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 1000;

 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)
----------------------------------------

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;

 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)


QUERY 3 (ROOM JOIN)
----------------

2010-08-23 02:27:30 EDT LOG:  duration: 10817.570 ms  execute <unnamed>:

explain analyze SELECT
            m.content, m.message_id, m.created_on,
            u.nick, u.avatar,
            array_to_string(ARRAY(SELECT nick || ' ' || tag
              FROM tags
              WHERE message_id = m.message_id AND tags.user_id = u.user_id), ' ') as tags
          FROM users u, messages m
          WHERE room_id = 2
            AND m.user_id = u.user_id  ORDER BY created_on DESC
          LIMIT 200 OFFSET 0;

select 

PLAN
----

 Limit  (cost=115535.24..115535.74 rows=200 width=157) (actual time=7835.228..7835.592 rows=200 loops=1)
   ->  Sort  (cost=115535.24..115546.44 rows=4477 width=157) (actual time=7835.224..7835.356 rows=200 loops=1)
         Sort Key: m.created_on
         Sort Method:  top-N heapsort  Memory: 58kB
         ->  Hash Join  (cost=354.59..115341.75 rows=4477 width=157) (actual time=256.324..7807.477 rows=13581 loops=1)
               Hash Cond: (m.user_id = u.user_id)
               ->  Index Scan using messages_room_id_idx on messages m  (cost=0.00..2970.23 rows=4477 width=80) (actual time=7.260..5647.945 rows=13581 loops=1)
                     Index Cond: (room_id = 2)
               ->  Hash  (cost=262.04..262.04 rows=7404 width=85) (actual time=237.419..237.419 rows=5675 loops=1)
                     ->  Seq Scan on users u  (cost=0.00..262.04 rows=7404 width=85) (actual time=0.208..227.609 rows=5675 loops=1)
               SubPlan 1
                 ->  Nested Loop  (cost=0.00..25.00 rows=2 width=18) (actual time=0.126..0.135 rows=0 loops=13581)
                       ->  Index Scan using tags_message_id_idx on tags  (cost=0.00..8.43 rows=2 width=13) (actual time=0.122..0.131 rows=0 loops=13581)
                             Index Cond: (message_id = $0)
                       ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=13) (actual time=0.010..0.011 rows=1 loops=243)
                             Index Cond: (users.user_id = tags.user_id)
 Total runtime: 7837.031 ms
(17 rows)


QUERY 4 (FEED_IMAGES)
------------------------

explain analyze SELECT * FROM feed_images
WHERE room = 'boys' AND message_id IS NULL
ORDER BY queued_on ASC LIMIT 1;

 Limit  (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.090..8779.093 rows=1 loops=1)
   ->  Sort  (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.084..8779.084 rows=1 loops=1)
         Sort Key: queued_on
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Bitmap Heap Scan on feed_images  (cost=921.32..3966.32 rows=1 width=231) (actual time=8772.479..8772.497 rows=7 loops=1)
               Recheck Cond: (room = 'boys'::text)
               Filter: (message_id IS NULL)
               ->  Bitmap Index Scan on feed_images_pkey  (cost=0.00..921.32 rows=12400 width=0) (actual time=5878.657..5878.657 rows=13408 loops=1)
                     Index Cond: (room = 'boys'::text)
 Total runtime: 8779.219 ms
(10 rows)