Re: BUG #13827: planner chooses more expensive plan than it should

michal(dot)schwarz(at)gmail(dot)com writes:
> => explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
> n.smlouva_id=s.smlouva_id AND s.osoba_id='900316';
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..18415.76 rows=88 width=0)
> -> Index Scan using smlouvy_osoba_id on smlouvy s (cost=0.00..678.38
> rows=170 width=4)
> Index Cond: (osoba_id = 900316)
> -> Index Only Scan using nakupy_prodeje_smlouva_id on nakupy_prodeje n
> (cost=0.00..100.99 rows=335 width=4)
> Index Cond: (smlouva_id = s.smlouva_id)
> (5 rows)

> Total expected cost is 18415 and 88 rows. OK.

> Problematic behaviour is when I just add "AND n.datum_realizace is null" to
> original WHERE condition. This query should be at least as fast as previous
> query, because everything is absolutely the same, and only an ADDITIONAL
> condition "AND n.datum_realizace is null" was used.

But datum_realizace is not in the nakupy_prodeje_smlouva_id index. So it
could not have done this "exactly the same"; it would have had to have
used a regular index scan, which is a whole lot more expensive than the
index-only scan because it involves fetching heap tuples too. Evidently
the planner thinks the hash join is a better option than that.

If you try turning off enable_hashjoin and enable_mergejoin, you'll
probably get the nestloop/indexscan plan, and you'll be able to see
what cost the planner is assigning to it; but it will certainly be
higher than for the plan that got selected.

regards, tom lane