;; This buffer is for notes you don't want to save, and for Lisp evaluation.
;; If you want to create a file, visit that file with C-x C-f,
;; then enter the text in that file's own buffer.

explain analyze SELECT
            m.content, m.message_id, m.created_on,
            u.nick, u.avatar, r.key,
            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 messages m, users u, rooms r
          WHERE m.user_id = u.user_id AND u.nick = 'jeanette'
            AND r.room_id = m.room_id AND r.admin_only = false AND m.is_image = true  ORDER BY message_id DESC
          LIMIT 40 OFFSET 0;

                                                                          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6799.90..6800.00 rows=40 width=189) (actual time=574.213..574.318 rows=40 loops=1)
   ->  Sort  (cost=6799.90..6800.10 rows=82 width=189) (actual time=574.207..574.244 rows=40 loops=1)
         Sort Key: m.message_id
         Sort Method:  top-N heapsort  Memory: 30kB
         ->  Hash Join  (cost=28.81..6797.31 rows=82 width=189) (actual time=39.615..549.090 rows=14514 loops=1)
               Hash Cond: (m.room_id = r.room_id)
               ->  Nested Loop  (cost=27.71..4742.93 rows=680 width=161) (actual time=39.113..209.852 rows=14520 loops=1)
                     ->  Index Scan using users_nick_key on users u  (cost=0.00..8.27 rows=1 width=85) (actual time=6.983..6.986 rows=1 loops=1)
                           Index Cond: (nick = 'jeanette'::text)
                     ->  Bitmap Heap Scan on messages m  (cost=27.71..4716.13 rows=1483 width=84) (actual time=31.609..180.079 rows=14520 loops=1)
                           Recheck Cond: (m.user_id = u.user_id)
                           Filter: m.is_image
                           ->  Bitmap Index Scan on messages_user_id_idx  (cost=0.00..27.54 rows=1483 width=0) (actual time=25.450..25.450 rows=54778 loops=1)
                                 Index Cond: (m.user_id = u.user_id)
               ->  Hash  (cost=1.06..1.06 rows=3 width=36) (actual time=0.144..0.144 rows=32 loops=1)
                     ->  Seq Scan on rooms r  (cost=0.00..1.06 rows=3 width=36) (actual time=0.050..0.100 rows=32 loops=1)
                           Filter: (NOT admin_only)
               SubPlan 1
                 ->  Nested Loop  (cost=0.00..25.00 rows=2 width=18) (actual time=0.015..0.018 rows=1 loops=14514)
                       ->  Index Scan using tags_message_id_idx on tags  (cost=0.00..8.43 rows=2 width=13) (actual
time=0.011..0.012 rows=1 loops=14514)
                             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=7278)
                             Index Cond: (users.user_id = tags.user_id)
 Total runtime: 576.155 ms
(24 rows)