Planner choices

Hi,

Could anybody explain why the planner in the first query chooses to use a hash
join on the cbs table, while in the second query (different only in that
another table is inner joined) a nested loop is used (slooooow...)

All fields compared in the joins are of the same type AND indexed.

# explain analyse select distinct on (a.id) a.id, a.descr
from article a
left outer join article_creditor_price acp on a.id = acp.article_id,
cbs c where a.dflt_cbs_id = c.id;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Unique (cost=863.25..888.13 rows=498 width=38) (actual time=2148.57..2199.76
rows=4976 loops=1)
-> Sort (cost=863.25..875.69 rows=4976 width=38) (actual
time=2148.55..2161.54 rows=4976 loops=1)
Sort Key: a.id
-> Hash Join (cost=64.71..557.71 rows=4976 width=38) (actual
time=616.54..2083.86 rows=4976 loops=1)
Hash Cond: ("outer".dflt_cbs_id = "inner".id)
-> Hash Join (cost=63.27..469.19 rows=4976 width=34) (actual
time=597.69..1969.24 rows=4976 loops=1)
Hash Cond: ("outer".id = "inner".article_id)
-> Seq Scan on article a (cost=0.00..352.76 rows=4976
width=30) (actual time=3.53..1274.34 rows=4976 loops=1)
-> Hash (cost=57.62..57.62 rows=2262 width=4) (actual
time=593.70..593.70 rows=0 loops=1)
-> Seq Scan on article_creditor_price acp
(cost=0.00..57.62 rows=2262 width=4) (actual time=0.34..579.15 rows=2262
loops=1)
-> Hash (cost=1.35..1.35 rows=35 width=4) (actual
time=18.42..18.42 rows=0 loops=1)
-> Seq Scan on cbs c (cost=0.00..1.35 rows=35 width=4)
(actual time=17.58..18.19 rows=35 loops=1)
Total runtime: 2229.14 msec
(13 rows)

# explain analyse select distinct on (a.id) a.id, a.descr
from article a
left outer join article_creditor_price acp on a.id = acp.article_id,
cbs c,
article_material am
where a.dflt_cbs_id = c.id and a.main_material_id = am.id;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=369.84..394.72 rows=498 width=50) (actual time=5051.83..5102.39
rows=4976 loops=1)
-> Sort (cost=369.84..382.28 rows=4976 width=50) (actual
time=5051.81..5064.56 rows=4976 loops=1)
Sort Key: a.id
-> Merge Join (cost=1.95..64.29 rows=4976 width=50) (actual
time=46.06..4839.02 rows=4976 loops=1)
Merge Cond: ("outer".main_material_id = "inner".id)
-> Nested Loop (cost=0.00..27550.88 rows=4976 width=44)
(actual time=44.88..4680.38 rows=4976 loops=1)
Join Filter: ("outer".dflt_cbs_id = "inner".id)
-> Nested Loop (cost=0.00..18656.28 rows=4976 width=40)
(actual time=44.62..1408.64 rows=4976 loops=1)
-> Index Scan using article_idx7 on article a
(cost=0.00..463.74 rows=4976 width=36) (actual time=23.11..762.05 rows=4976
loops=1)
-> Index Scan using article_creditor_price_idx1 on
article_creditor_price acp (cost=0.00..3.64 rows=1 width=4) (actual
time=0.09..0.09 rows=0 loops=4976)
Index Cond: ("outer".id = acp.article_id)
-> Seq Scan on cbs c (cost=0.00..1.35 rows=35 width=4)
(actual time=0.02..0.39 rows=35 loops=4976)
-> Sort (cost=1.95..2.02 rows=28 width=6) (actual
time=0.85..18.20 rows=28 loops=1)
Sort Key: am.id
-> Seq Scan on article_material am (cost=0.00..1.28
rows=28 width=6) (actual time=0.04..0.32 rows=28 loops=1)
Total runtime: 5117.80 msec
(16 rows)

TIA!

Regards,

Frank.