rendered paste body QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=246220.82..246220.85 rows=10 width=125) (actual time=22925.798..22925.798 rows=0 loops=1)
-> Sort (cost=246220.82..246221.22 rows=160 width=125) (actual time=22925.795..22925.795 rows=0 loops=1)
Sort Key: (p.score IS NULL), p.score, i.date
-> Nested Loop IN Join (cost=377.44..246214.97 rows=160 width=125) (actual time=22925.652..22925.652 rows=0 loops=1)
Join Filter: ("outer".resourceid = "inner".resourceid)
-> Nested Loop Left Join (cost=354.76..244334.26 rows=11797 width=141) (actual time=33.478..22842.713 rows=26358 loops=1)
-> Bitmap Heap Scan on tbxmlcontentitems i (cost=354.76..173400.22 rows=11797 width=137) (actual time=33.390..22584.871 rows=26358 loops=1)
Recheck Cond: ((now() - '7 days'::interval) < date)
Filter: ((thumbnail <> ''::text) AND (NOT (subplan)))
-> Bitmap Index Scan on tbxmlcontentitems_dateidx (cost=0.00..313.86 rows=49673 width=0) (actual time=22.743..22.743 rows=55457 loops=1)
Index Cond: ((now() - '7 days'::interval) < date)
SubPlan
-> Materialize (cost=40.90..44.96 rows=406 width=16) (actual time=0.001..0.401 rows=388 loops=26361)
-> Bitmap Heap Scan on "userToItems" (cost=5.42..40.50 rows=406 width=16) (actual time=0.340..1.478 rows=388 loops=1)
Recheck Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
-> Bitmap Index Scan on "userToItems_idx1" (cost=0.00..5.42 rows=406 width=0) (actual time=0.300..0.300 rows=388 loops=1)
Index Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
-> Index Scan using items_idx on items p (cost=0.00..6.00 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=26358)
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=26358)
-> Nested Loop (cost=18.39..22.67 rows=7 width=16) (actual time=0.221..0.221 rows=0 loops=1)
-> HashAggregate (cost=11.58..11.59 rows=1 width=4) (actual time=0.218..0.218 rows=0 loops=1)
-> Nested Loop (cost=0.00..11.58 rows=1 width=4) (actual time=0.214..0.214 rows=0 loops=1)
-> Index Scan using tbxmlcontentitems_pkey on tbxmlcontentitems i (cost=0.00..6.01 rows=1 width=16) (actual time=0.131..0.134 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.074..0.074 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: 22926.252 ms
(32 rows)
EXPLAIN ANALYZE SELECT i.itemid,i.title,i.thumbnail,i.date FROM play.tbxmlcontentitems i LEFT OUTER JOIN pre.items p ON (p.itemid=i.itemid) WHERE (now()-'7 day'::interval)<i.date AND i.resourceid IN (SELECT resourceid FROM index.feedtopic WHERE topicid IN (SELECT f.topicid FROM index.feedtopic f,play.tbxmlcontentitems i WHERE i.itemid='318e4942-4590-48d1-87fe-a2b092aaa3f2' AND f.resourceid=i.resourceid)) AND i.itemid NOT IN (SELECT itemid FROM pre."userToItems" WHERE userid='9fd80e57-5af8-43a3-91e5-bcaad5338967') AND i.thumbnail != '' ORDER BY p.score IS NULL, p.score DESC, i.date DESC LIMIT 10;