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.