Re: random_page_cost vs seq_page_cost

On 07/01/12 23:01, Peter Eisentraut wrote:
> On tor, 2012-01-05 at 10:04 +0000, Benedikt Grundmann wrote:
> > We have recently upgrade two of our biggest postgres databases
> > to new hardware and minor version number bump (8.4.5 -> 8.4.9).
> >
> > We are experiencing a big performance regression in some queries.
> > In those cases the planner seems to choose a nested loop index
> > scan instead of hashing the index once and then joining.
>
> There was a planner regression introduced in version 8.4.8, which was
> thought to be fixed in 8.4.9. Maybe you got caught by that. See
>
> Message-Id: <760C0206-B5F4-4DC6-9296-B7A730B7F403(at)silentmedia(dot)com>
>
> for some information. Check if your queries match that pattern.

Good idea. But that is not it. We checked by using 8.4.5 on
the new hardware (and the new database) which produced the same
(bad) plans as 8.4.10 (with both the old and the new postgres config).

We are again speculating that it might be:
> > For some of those tables we have also have recently (as part
> > of the move) clustered for the first time in ages and it was
> > speculated that that might have changed statistics (such
> > as correlation) and increased the attractiveness of the
> > index scan to the planner.

Is that possible? If so what is the best way to prove / disprove
this theory? And ideally if true what knobs are available to tune
this?

Thanks,

Bene