Re: performance penalty between Postgresql 8.3.8 and 8.4.1

"Schmitz, David" <david.schmitz@harman.com> wrote:

> It is carried out with poor performance on postgresql 8.4.1
> However postgresql 8.3.8 performs just fine.
> If you take a closer look at the query with EXPLAIN, it becomes
> obvious, that postgresql 8.4 does not consider the primary key at
> level 3 and instead generates a hash join:

> Postgresql 8.4.1:
>
> Sort  (cost=129346.71..129498.64 rows=60772 width=61)

> Postgresql 8.3.8:
>
> Sort  (cost=3792.75..3792.95 rows=81 width=61)

It determines the plan based on available statistics, which in this
case seem to indicate rather different data.  Do the two databases
have identical data?  Have they both been recently analyzed?  What
is the default_statistics_target on each?  Do any columns in these
tables have overrides?

-Kevin