Stuff

streamy=# EXPLAIN ANALYZE
streamy-# SELECT i.itemid, i.title, i.thumbnail, i.date
streamy-# FROM index.feedtopic f, index.feedtopic f2, pre."userToItems" uti, play.tbxmlcontentitems i2, play.tbxmlcontentitems i LEFT OUTER JOIN pre.items p
streamy-#    ON (p.itemid=i.itemid)
streamy-# WHERE
streamy-# i2.itemid='318e4942-4590-48d1-87fe-a2b092aaa3f2' AND
streamy-# f2.resourceid=i2.resourceid AND
streamy-# f.topicid=f2.topicid AND
streamy-# i.resourceid=f.resourceid AND
streamy-# uti.userid='9fd80e57-5af8-43a3-91e5-bcaad5338967' AND i.itemid!=uti.itemid AND
streamy-# i.thumbnail != '' AND
streamy-# (now()-'7 day'::interval)<i.date
streamy-# ORDER BY p.score IS NULL, p.score DESC, i.date DESC
streamy-# LIMIT 10;
                                                                                            QUERY PLAN                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=215340.04..215340.06 rows=10 width=126) (actual time=689.197..689.197 rows=0 loops=1)
   ->  Sort  (cost=215340.04..215700.53 rows=144195 width=126) (actual time=689.192..689.192 rows=0 loops=1)
         Sort Key: (p.score IS NULL), p.score, i.date
         ->  Nested Loop  (cost=172004.61..178524.21 rows=144195 width=126) (actual time=689.179..689.179 rows=0 loops=1)
               Join Filter: ("inner".itemid <> "outer".itemid)
               ->  Bitmap Heap Scan on "userToItems" uti  (cost=5.36..40.24 rows=390 width=16) (actual time=0.226..1.119 rows=388 loops=1)
                     Recheck Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
                     ->  Bitmap Index Scan on "userToItems_idx1"  (cost=0.00..5.36 rows=390 width=0) (actual time=0.210..0.210 rows=388 loops=1)
                           Index Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
               ->  Materialize  (cost=171999.25..172006.64 rows=739 width=126) (actual time=1.771..1.771 rows=0 loops=388)
                     ->  Nested Loop  (cost=22.68..171998.51 rows=739 width=126) (actual time=686.627..686.627 rows=0 loops=1)
                           Join Filter: ("outer".resourceid = "inner".resourceid)
                           ->  Nested Loop Left Join  (cost=0.00..163452.94 rows=25253 width=142) (actual time=0.125..583.608 rows=26281 loops=1)
                                 ->  Index Scan using tbxmlcontentitems_dateidx on tbxmlcontentitems i  (cost=0.00..11568.40 rows=25253 width=138) (actual time=0.109..267.953 rows=26281 loops=1)
                                       Index Cond: ((now() - '7 days'::interval) < date)
                                       Filter: (thumbnail <> ''::text)
                                 ->  Index Scan using items_idx on items p  (cost=0.00..6.00 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=26281)
                                       Index Cond: (p.itemid = "outer".itemid)
                           ->  Materialize  (cost=22.68..22.83 rows=15 width=16) (actual time=0.001..0.001 rows=0 loops=26281)
                                 ->  Nested Loop  (cost=6.81..22.66 rows=15 width=16) (actual time=0.128..0.128 rows=0 loops=1)
                                       ->  Nested Loop  (cost=0.00..11.58 rows=1 width=4) (actual time=0.126..0.126 rows=0 loops=1)
                                             ->  Index Scan using tbxmlcontentitems_pkey on tbxmlcontentitems i2  (cost=0.00..6.01 rows=1 width=16) (actual time=0.072..0.074 rows=1 loops=1)
                                                   Index Cond: (itemid = '318e4942-4590-48d1-87fe-a2b092aaa3f2'::uniqueidentifier)
                                             ->  Index Scan using feedtopic_idx on feedtopic f2  (cost=0.00..5.56 rows=1 width=20) (actual time=0.046..0.046 rows=0 loops=1)
                                                   Index Cond: (f2.resourceid = "outer".resourceid)
                                       ->  Bitmap Heap Scan on feedtopic f  (cost=6.81..10.99 rows=7 width=20) (never executed)
                                             Recheck Cond: (f.topicid = "outer".topicid)
                                             ->  Bitmap Index Scan on feedtopic_idx  (cost=0.00..6.81 rows=7 width=0) (never executed)
                                                   Index Cond: (f.topicid = "outer".topicid)
 Total runtime: 689.380 ms
(30 rows)