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)
EXPLAIN ANALYZE
SELECT i.itemid, i.title, i.thumbnail, i.date
FROM pre."userToItems" uti, 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
uti.userid='9fd80e57-5af8-43a3-91e5-bcaad5338967' AND i.itemid!=uti.itemid AND
i.thumbnail != ''
ORDER BY p.score IS NULL, p.score DESC, i.date DESC
LIMIT 10;