Query 1 (USER_ID):

explain analyze SELECT
m.content, m.message_id, m.created_on, u.nick, u.avatar,
array_to_string(ARRAY(SELECT nick || ' ' || tag
              FROM tags, users
              WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags
from users u
left join messages m on u.user_id = m.user_id
where 
--u.nick = 'jeanette'
 u.user_id = 6
and m.is_image = true and m.room_id = 1
order by m.created_on desc
LIMIT 40 offset 0;

Query 2 (NICK):

explain analyze SELECT
m.content, m.message_id, m.created_on, u.nick, u.avatar,
array_to_string(ARRAY(SELECT nick || ' ' || tag
              FROM tags, users
              WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags
from users u
left join messages m on u.user_id = m.user_id
where 
u.nick = 'jeanette'
-- u.user_id = 6
and m.is_image = true and m.room_id = 1
order by m.created_on desc
LIMIT 40 offset 0;


QUERY 1 (USER_ID)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1526.37 rows=40 width=157) (actual time=0.121..1.696 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..880298.45 rows=23069 width=157) (actual time=0.118..1.621 rows=40 loops=1)
         ->  Index Scan using messages_user_created_on_idx on messages m  (cost=0.00..112630.78 rows=23069 width=80) (actual time=0.056..0.234 rows=40 loops=1)
               Index Cond: (user_id = 6)
               Filter: (is_image AND (room_id = 1))
         ->  Index Scan using users_pkey on users u  (cost=0.00..8.27 rows=1 width=85) (actual time=0.004..0.006 rows=1 loops=40)
               Index Cond: (u.user_id = 6)
         SubPlan 1
           ->  Nested Loop  (cost=0.00..25.00 rows=2 width=18) (actual time=0.012..0.019 rows=1 loops=40)
                 ->  Index Scan using tags_message_id_idx on tags  (cost=0.00..8.43 rows=2 width=13) (actual time=0.006..0.007 rows=1 loops=40)
                       Index Cond: (message_id = $0)
                 ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=37)
                       Index Cond: (users.user_id = tags.user_id)
 Total runtime: 1.829 ms
(14 rows)


QUERY 2 (NICK)
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=20371.96..20372.06 rows=40 width=157) (actual time=532.538..532.611 rows=40 loops=1)
   ->  Sort  (cost=20371.96..20373.52 rows=624 width=157) (actual time=532.535..532.560 rows=40 loops=1)
         Sort Key: m.created_on
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Nested Loop  (cost=27.71..20352.24 rows=624 width=157) (actual time=83.105..502.069 rows=14354 loops=1)
               ->  Index Scan using users_nick_key on users u  (cost=0.00..8.27 rows=1 width=85) (actual time=0.047..0.048 rows=1 loops=1)
                     Index Cond: (nick = 'jeanette'::text)
               ->  Bitmap Heap Scan on messages m  (cost=27.71..4726.08 rows=1485 width=80) (actual time=83.025..180.705 rows=14354 loops=1)
                     Recheck Cond: (m.user_id = u.user_id)
                     Filter: (m.is_image AND (m.room_id = 1))
                     ->  Bitmap Index Scan on messages_user_id_idx  (cost=0.00..27.55 rows=1485 width=0) (actual time=81.113..81.113 rows=54778 loops=1)
                           Index Cond: (m.user_id = u.user_id)
               SubPlan 1
                 ->  Nested Loop  (cost=0.00..25.00 rows=2 width=18) (actual time=0.013..0.017 rows=1 loops=14354)
                       ->  Index Scan using tags_message_id_idx on tags  (cost=0.00..8.43 rows=2 width=13) (actual time=0.009..0.010 rows=1 loops=14354)
                             Index Cond: (message_id = $0)
                       ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=13) (actual time=0.004..0.006 rows=1 loops=7190)
                             Index Cond: (users.user_id = tags.user_id)
 Total runtime: 532.757 ms
(19 rows)