Hi,
I have this query
SELECT
*
FROM
v_material
WHERE
show_in_recent AND section_id IN (
SELECT
s.id
FROM
section AS s, section AS s2
WHERE
s2.id = 842
AND
s.breadcrumb <@ s2.breadcrumb
)
ORDER BY
published_on DESC
LIMIT 3;
This query is slow (2 sec)
if i just remove the sub-select in IN clause, and replace it with the value of the sub-select ,the query is very fast.
The sub-select itself is very fast.
Is there a way to optimize it ?
Here are the 2 execution plans
Slow :
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=82.13..92.65 rows=3 width=1109) (actual time=2626.954..2626.954 rows=0 loops=1)
-> Nested Loop Left Join (cost=82.13..633409.54 rows=180475 width=1109) (actual time=2626.952..2626.952 rows=0 loops=1)
-> Nested Loop Semi Join (cost=82.13..568691.75 rows=180475 width=1105) (actual time=2626.951..2626.951 rows=0 loops=1)
Join Filter: (s.id = s.id)
-> Nested Loop (cost=0.00..564548.94 rows=180475 width=1109) (actual time=0.096..2536.080 rows=179994 loops=1)
-> Nested Loop (cost=0.00..511665.26 rows=180475 width=1106) (actual time=0.092..2236.543 rows=179994 loops=1)
-> Nested Loop Left Join (cost=0.00..460819.28 rows=180475 width=1001) (actual time=0.084..1789.881 rows=179994 loops=1)
-> Nested Loop (cost=0.00..403069.44 rows=180475 width=997) (actual time=0.067..1442.077 rows=179994 loops=1)
-> Nested Loop (cost=0.00..352510.00 rows=180475 width=989) (actual time=0.061..1161.405 rows=179994 loops=1)
-> Nested Loop Left Join (cost=0.00..301950.56 rows=180475 width=975) (actual time=0.056..873.697 rows=179994 loops=1)
-> Nested Loop (cost=0.00..237987.06 rows=180475 width=971) (actual time=0.046..707.396 rows=179994 loops=1)
-> Index Scan Backward using material_published_on on material m (cost=0.00..187427.62 rows=180475 width=956) (actual time=0.037..392.091 rows=179994 loops=1)
Filter: (active AND show_in_recent)
-> Index Scan using staff_pkey on staff s (cost=0.00..0.27 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=179994)
Index Cond: (s.id = m.created_by)
-> Index Scan using rating_pkey on rating r (cost=0.00..0.34 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=179994)
Index Cond: (m.rating_id = r.id)
-> Index Scan using lookup_material_type_pkey on lookup_material_type mt (cost=0.00..0.27 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=179994)
Index Cond: (mt.id = m.type_id)
-> Index Scan using lookup_material_importance_pkey on lookup_material_importance lmi (cost=0.00..0.27 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=179994)
Index Cond: (lmi.id = m.importance_id)
-> Index Scan using material_images_pkey on material_images mi (cost=0.00..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=179994)
Index Cond: (m.id = mi.material_id)
Filter: (mi."position" = 1)
-> Index Scan using section_pkey on section s (cost=0.00..0.27 rows=1 width=105) (actual time=0.001..0.002 rows=1 loops=179994)
Index Cond: (s.id = m.section_id)
-> Index Scan using lookup_section_type_pkey on lookup_section_type lst (cost=0.00..0.28 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=179994)
Index Cond: (lst.id = s.type_id)
-> Materialize (cost=82.13..82.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=179994)
-> Nested Loop (cost=0.00..82.13 rows=1 width=4) (actual time=0.535..0.730 rows=1 loops=1)
Join Filter: (s.breadcrumb <@ s2.breadcrumb)
-> Index Scan using section_pkey on section s2 (cost=0.00..8.27 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (id = 842)
-> Seq Scan on section s (cost=0.00..62.27 rows=927 width=38) (actual time=0.003..0.248 rows=927 loops=1)
-> Index Scan using forum_pkey on forum f (cost=0.00..0.34 rows=1 width=8) (never executed)
Index Cond: (m.forum_id = f.id)
Total runtime: 2627.290 ms
===============================================
Also what is the meaning of MATERIALIZE in explain analyze ?
Fast ;
SELECT
*
FROM
v_material
WHERE
show_in_recent AND section_id IN (
842
)
ORDER BY
published_on DESC
LIMIT 3;
Limit (cost=3458.91..3458.92 rows=3 width=1109) (actual time=0.189..0.189 rows=0 loops=1)
-> Sort (cost=3458.91..3459.24 rows=133 width=1109) (actual time=0.189..0.189 rows=0 loops=1)
Sort Key: m.published_on
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=18.02..3457.19 rows=133 width=1109) (actual time=0.184..0.184 rows=0 loops=1)
-> Nested Loop Left Join (cost=18.02..2419.92 rows=133 width=1105) (actual time=0.184..0.184 rows=0 loops=1)
-> Hash Join (cost=18.02..1590.87 rows=111 width=1101) (actual time=0.184..0.184 rows=0 loops=1)
Hash Cond: (m.importance_id = lmi.id)
-> Hash Join (cost=16.93..1588.25 rows=111 width=1093) (actual time=0.173..0.173 rows=0 loops=1)
Hash Cond: (m.type_id = mt.id)
-> Hash Join (cost=15.66..1585.46 rows=111 width=1079) (actual time=0.163..0.163 rows=0 loops=1)
Hash Cond: (m.created_by = s.id)
-> Nested Loop (cost=13.74..1582.01 rows=111 width=1064) (actual time=0.137..0.137 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.43 rows=1 width=108) (actual time=0.011..0.013 rows=1 loops=1)
Join Filter: (s.type_id = lst.id)
-> Index Scan using section_pkey on section s (cost=0.00..8.27 rows=1 width=105) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (id = 842)
-> Seq Scan on lookup_section_type lst (cost=0.00..1.07 rows=7 width=11) (actual time=0.000..0.001 rows=7 loops=1)
-> Nested Loop Left Join (cost=13.74..1571.47 rows=111 width=960) (actual time=0.122..0.122 rows=0 loops=1)
-> Bitmap Heap Scan on material m (cost=13.74..693.56 rows=111 width=956) (actual time=0.122..0.122 rows=0 loops=1)
Recheck Cond: (section_id = 842)
Filter: (active AND show_in_recent)
-> Bitmap Index Scan on section_total_visits (cost=0.00..13.71 rows=177 width=0) (actual time=0.034..0.034 rows=170 loops=1)
Index Cond: (section_id = 842)
-> Index Scan using rating_pkey on rating r (cost=0.00..7.90 rows=1 width=8) (never executed)
Index Cond: (m.rating_id = r.id)
-> Hash (cost=1.41..1.41 rows=41 width=19) (actual time=0.022..0.022 rows=41 loops=1)
-> Seq Scan on staff s (cost=0.00..1.41 rows=41 width=19) (actual time=0.003..0.012 rows=41 loops=1)
-> Hash (cost=1.12..1.12 rows=12 width=18) (actual time=0.007..0.007 rows=12 loops=1)
-> Seq Scan on lookup_material_type mt (cost=0.00..1.12 rows=12 width=18) (actual time=0.001..0.004 rows=12 loops=1)
-> Hash (cost=1.04..1.04 rows=4 width=12) (actual time=0.007..0.007 rows=4 loops=1)
-> Seq Scan on lookup_material_importance lmi (cost=0.00..1.04 rows=4 width=12) (actual time=0.003..0.003 rows=4 loops=1)
-> Index Scan using material_images_pkey on material_images mi (cost=0.00..7.46 rows=1 width=8) (never executed)
Index Cond: (m.id = mi.material_id)
Filter: (mi."position" = 1)
-> Index Scan using forum_pkey on forum f (cost=0.00..7.78 rows=1 width=8) (never executed)
Index Cond: (m.forum_id = f.id)
Total runtime: 0.402 ms
