Re: Suboptimal plan choice problem with 8.3RC2

On Jan 22, 2008 8:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The only way the merge join could have an estimated cost that's barely
> 1% of the estimate for one of its inputs is if the planner thinks the
> merge will stop after reading only 1% of that input, ie, the largest
> a.numasso value is only about 1% of the way through the range of
> el.numasso. If the a.numasso distribution has a long tail, you might
> need to raise the statistics target to fix this estimate.

The statistics target was fine (I set it to 30 by default). But...

> I'd expect 8.1 to make about the same estimate given the same stats,
> so I think it's not looking at the same stats.

Yep, the statistics were the problem, sorry for the noise. The query
performs in 50ms after an ANALYZE so far better than with 8.1.

The 8.3RC2 box is using the default configuration of autovacuum
though. Shouldn't it take care of keeping the statistics up to date?
That's what I thought from what I've read on autovacuum so far (it's
the first time I use it in "production" though, it was a manual
process until now) - and that's why I didn't check it. Or should we
still run the first ANALYZE manually?

Andrew from Supernews also pointed the lack of an index on
evelieu(numasso). It's even better with it (less than a ms).

--
Guillaume