Re: Wrong plan for simple join with index on FK

On Tue, May 16, 2006 at 12:54:58PM +0200, Pavel Stehule wrote:
> >
> >Can we seen an EXPLAIN ANALYZE output to see where the miscalculation
> >lies. Is it underestimating the cost of the index scan, or
> >overestimating the cost of the hash join.

> postgres=> explain analyze select count(*) from f1 join f2 on pk=fk;

First query (merge join):

Apart from the apparent overestimation of the cost of a full index scan
over xxx by about 30%, there seems to be a significant underestimation
of the cost of the merge join.

> postgres=> explain analyze select count(*) from f1 join f2 on pk=fk;

Second query (hash join):

Here the estimates seem to be fine, except for an apparent
underestimation of the cost of the aggregate.

These are all minor abberations though, on the whole the estimates are
pretty good. Perhaps you need to tweak the values of random_page_cost
and similar variables.

Have a ncie day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.