SELECT i.itemid,i.title,i.thumbnail,i.date
FROM index.feedtopic f, index.feedtopic f2, pre."userToItems" uti, play.tbxmlcontentitems i2,
play.tbxmlcontentitems i LEFT OUTER JOIN pre.items p ON (p.itemid=i.itemid)
WHERE (now()-'7 day'::interval)<i.date
AND i.thumbnail IS NOT NULL
AND i2.itemid='6b603c5f-6bcc-43a8-a55f-c9907ee59c91'
AND f2.resourceid=i2.resourceid
AND f.topicid=f2.topicid
AND i.resourceid=f.resourceid
AND uti.userid='9fd80e57-5af8-43a3-91e5-bcaad5338967'
AND i.itemid != uti.itemid
ORDER BY p.score IS NULL, p.score DESC, i.date DESC LIMIT 10;
Notes: The part that is really slowing this down is the LEFT OUTER JOIN. It does a sequential scan on every single item in 'play.tbxmlcontentitems' that does not get filtered out using the date and thumbnail conditions (it is not the checking of those conditions that takes the time, removing them only adds more items to sequentially scan and triples the runtime).
Question: Is there a different (faster or more efficient) method of doing this? play.tbxmlcontentitems contains a very large number of rows (~300k now, will grow into millions) and the pre.items table contains far fewer entries (10k now, hundreds of thousands later). Basically I want to get a value from pre.items if one exists, but i still want ones for which a value does not exist in pre.items.
Below is the query plan...
Limit (cost=243909.19..243909.21 rows=10 width=184) (actual time=1912.282..1912.282 rows=0 loops=1)
-> Sort (cost=243909.19..244239.48 rows=132119 width=184) (actual time=1912.278..1912.278 rows=0 loops=1)
Sort Key: (p.score IS NULL), p.score, i.date
-> Nested Loop (cost=195487.46..201471.49 rows=132119 width=184) (actual time=1912.262..1912.262 rows=0 loops=1)
Join Filter: ("inner".itemid <> "outer".itemid)
-> Bitmap Heap Scan on "userToItems" uti (cost=5.49..46.82 rows=426 width=16) (actual time=0.271..1.334 rows=426 loops=1)
Recheck Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
-> Bitmap Index Scan on "userToItems_idx1" (cost=0.00..5.49 rows=426 width=0) (actual time=0.231..0.231 rows=432 loops=1)
Index Cond: (userid = '9fd80e57-5af8-43a3-91e5-bcaad5338967'::uniqueidentifier)
-> Materialize (cost=195481.97..195488.17 rows=620 width=184) (actual time=4.483..4.483 rows=0 loops=426)
-> Nested Loop (cost=22.68..195481.35 rows=620 width=184) (actual time=1909.342..1909.342 rows=0 loops=1)
Join Filter: ("outer".resourceid = "inner".resourceid)
-> Nested Loop Left Join (cost=0.00..187885.51 rows=22439 width=200) (actual time=0.165..1813.911 rows=24300 loops=1)
-> Seq Scan on tbxmlcontentitems i (cost=0.00..52884.17 rows=22439 width=196) (actual time=0.105..1496.627 rows=24300 loops=1)
Filter: (((now() - '7 days'::interval) < date) AND (thumbnail IS NOT NULL))
-> 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=24300)
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=24300)
-> Nested Loop (cost=6.81..22.66 rows=15 width=16) (actual time=0.103..0.103 rows=0 loops=1)
-> Nested Loop (cost=0.00..11.58 rows=1 width=4) (actual time=0.101..0.101 rows=0 loops=1)
-> Index Scan using tbxmlcontentitems_pkey on tbxmlcontentitems i2 (cost=0.00..6.01 rows=1 width=16) (actual time=0.048..0.051 rows=1 loops=1)
Index Cond: (itemid = '6b603c5f-6bcc-43a8-a55f-c9907ee59c91'::uniqueidentifier)
-> Index Scan using feedtopic_idx on feedtopic f2 (cost=0.00..5.56 rows=1 width=20) (actual time=0.044..0.044 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: 1912.472 ms
(29 rows)