optimizing a query with sub select

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