QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=226302.01..226302.03 rows=10 width=124) (actual time=2361.944..2361.944 rows=0 loops=1) -> Sort (cost=226302.01..226478.00 rows=70395 width=124) (actual time=2361.940..2361.940 rows=0 loops=1) Sort Key: (p.score IS NULL), p.score, i.date -> Nested Loop (cost=212396.44..215599.09 rows=70395 width=124) (actual time=2361.927..2361.927 rows=0 loops=1) Join Filter: ("inner".itemid <> "outer".itemid) -> Bitmap Heap Scan on "userToItems" pre (cost=5.36..40.24 rows=390 width=16) (actual time=0.231..1.134 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.196..0.196 rows=388 loops=1) Index Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier) -> Materialize (cost=212391.08..212394.69 rows=361 width=124) (actual time=6.082..6.082 rows=0 loops=388) -> Nested Loop IN Join (cost=22.68..212390.71 rows=361 width=124) (actual time=2359.376..2359.376 rows=0 loops=1) Join Filter: ("outer".resourceid = "inner".resourceid) -> Nested Loop Left Join (cost=0.00..208183.57 rows=26568 width=140) (actual time=0.122..2255.569 rows=26339 loops=1) -> Seq Scan on tbxmlcontentitems i (cost=0.00..48389.94 rows=26568 width=136) (actual time=0.066..1922.873 rows=26339 loops=1) Filter: (((now() - '7 days'::interval) < date) AND (thumbnail <> ''::text)) -> Index Scan using items_idx on items p (cost=0.00..6.00 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=26339) Index Cond: (p.itemid = "outer".itemid) -> Materialize (cost=22.68..22.75 rows=7 width=16) (actual time=0.001..0.001 rows=0 loops=26339) -> Nested Loop (cost=18.39..22.67 rows=7 width=16) (actual time=0.110..0.110 rows=0 loops=1) -> HashAggregate (cost=11.58..11.59 rows=1 width=4) (actual time=0.108..0.108 rows=0 loops=1) -> Nested Loop (cost=0.00..11.58 rows=1 width=4) (actual time=0.104..0.104 rows=0 loops=1) -> Index Scan using tbxmlcontentitems_pkey on tbxmlcontentitems i (cost=0.00..6.01 rows=1 width=16) (actual time=0.046..0.049 rows=1 loops=1) Index Cond: (itemid = '318e4942-4590-48d1-87fe-a2b092aaa3f2'::uniqueidentifier) -> Index Scan using feedtopic_idx on feedtopic f (cost=0.00..5.56 rows=1 width=20) (actual time=0.050..0.050 rows=0 loops=1) Index Cond: (f.resourceid = "outer".resourceid) -> Bitmap Heap Scan on feedtopic (cost=6.81..10.99 rows=7 width=20) (never executed) Recheck Cond: (feedtopic.topicid = "outer".topicid) -> Bitmap Index Scan on feedtopic_idx (cost=0.00..6.81 rows=7 width=0) (never executed) Index Cond: (feedtopic.topicid = "outer".topicid) Total runtime: 2362.136 ms (30 rows) streamy=# EXPLAIN ANALYZE streamy-# SELECT i.itemid, i.title, i.thumbnail, i.date streamy-# FROM index.feedtopic f, pre."userToItems" uti, play.tbxmlcontentitems i LEFT OUTER JOIN pre.items p streamy-# ON (p.itemid=i.itemid) streamy-# WHERE streamy-# (now()-'7 day'::interval) Sort (cost=226230.23..226406.22 rows=70395 width=124) (actual time=1824.333..1824.333 rows=0 loops=1) Sort Key: (p.score IS NULL), p.score, i.date -> Nested Loop (cost=212324.66..215527.31 rows=70395 width=124) (actual time=1824.315..1824.315 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.243..1.138 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.208..0.208 rows=388 loops=1) Index Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier) -> Materialize (cost=212319.30..212322.91 rows=361 width=124) (actual time=4.697..4.697 rows=0 loops=388) -> Nested Loop (cost=22.68..212318.93 rows=361 width=124) (actual time=1821.770..1821.770 rows=0 loops=1) Join Filter: ("outer".resourceid = "inner".resourceid) -> Nested Loop Left Join (cost=0.00..208114.16 rows=26553 width=140) (actual time=0.121..1717.512 rows=26326 loops=1) -> Seq Scan on tbxmlcontentitems i (cost=0.00..48410.74 rows=26553 width=136) (actual time=0.065..1385.990 rows=26326 loops=1) Filter: (((now() - '7 days'::interval) < date) AND (thumbnail <> ''::text)) -> Index Scan using items_idx on items p (cost=0.00..6.00 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=26326) Index Cond: (p.itemid = "outer".itemid) -> Materialize (cost=22.68..22.75 rows=7 width=16) (actual time=0.001..0.001 rows=0 loops=26326) -> Nested Loop (cost=18.39..22.67 rows=7 width=16) (actual time=0.132..0.132 rows=0 loops=1) -> HashAggregate (cost=11.58..11.59 rows=1 width=4) (actual time=0.129..0.129 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 i (cost=0.00..6.01 rows=1 width=16) (actual time=0.052..0.055 rows=1 loops=1) Index Cond: (itemid = '318e4942-4590-48d1-87fe-a2b092aaa3f2'::uniqueidentifier) -> Index Scan using feedtopic_idx on feedtopic f (cost=0.00..5.56 rows=1 width=20) (actual time=0.065..0.065 rows=0 loops=1) Index Cond: (f.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: 1824.537 ms (30 rows)