Hmm. I don't see anything terribly wrong in the planner's estimates. The
only estimate that's off is the # of rows in pror_org matching the qual
orgt_cd = 'CHAIN', 27 estimated vs 1 actual. You could try increasing
the statistics target for that column to get that estimate right. That
might tip the planner to choose a plan with nested loop joins instead of
hash joins.
Have you played with enable_seqscan=off or enable_hashjoin=off? That's
not a good long term solution, but it would be interesting to see what
happens.
Mouhamadou Dia wrote:
> Sorry,
> This output is coming from PG 8.1.19
> I'm attaching the one that is coming from 8.2.4
> Thanks and sorry for the confusion
>
>
> -----Message d'origine-----
> De : Heikki Linnakangas [mailto:hlinnaka@gmail.com] De la part de Heikki Linnakangas
> Envoyé : 6 août 2007 15:32
> À : Mouhamadou Dia
> Cc : pgsql-bugs@postgresql.org
> Objet : Re: RE : [BUGS] BUG #3519: Postgres takes the wrong query plan resulting in performance issues
>
> Mouhamadou Dia wrote:
>> I'm sending in attachment the output of the explain analyze command and the create table statements of tables
involvedin the query.
>
> Wait, you said that the query takes 20 seconds on 8.2, but the explain
> analyze output says that it actually took 50 seconds. Is this the output
> from 8.2.4?
>
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com