rendered paste body 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)<i.date AND
streamy-# f.topicid IN (SELECT f.topicid FROM index.feedtopic f,play.tbxmlcontentitems i
streamy(# WHERE i.itemid='318e4942-4590-48d1-87fe-a2b092aaa3f2' AND f.resourceid=i.resourceid) AND
streamy-# i.resourceid=f.resourceid AND
streamy-# uti.userid='9fd80e57-5af8-43a3-91e5-bcaad5338967' AND i.itemid!=uti.itemid AND
streamy-# i.thumbnail != ''
streamy-# ORDER BY p.score IS NULL, p.score DESC, i.date DESC
streamy-# LIMIT 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=226230.23..226230.25 rows=10 width=124) (actual time=1824.336..1824.336 rows=0 loops=1)
-> 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)