Skip site navigation (1) Skip section navigation (2)

Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: "Mouhamadou Dia" <MDia(at)accovia(dot)com>, <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: RE : RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
Date: 2007-08-07 01:13:18
Message-ID: 87zm14kuz5.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-bugs
"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:

> 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.

I think this is a case where Postgres just doesn't know it can re-order near
an outer join. Outer joins often can't be re-ordered and Postgres isn't a
general theorem prover, it can't always figure out whether it's safe to
re-order them.

The structure of your query is a whole series of left outer joins, the result
of which is then (inner) joined with one more table. The outer joins return a
whole lot of records but the inner join is only going to match a few of them.

The only hope you have of a reasonable plan here is if Postgres can figure out
that it can do the inner join first so that it only has to perform the outer
join on the resulting records.

I think it could actually re-order the inner query to happen first in this
case. But I'm not certain, it's tricky to tell. But the fact that Oracle finds
a way to execute it quickly gives me some confidence that it ought to be
possible since I think Oracle does get join orderings reasonably right. I'm
not so sure about Informix.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-08-07 01:23:18
Subject: Re: BUG #3504: Some listening sessions never return from writing, problems ensue
Previous:From: Peter KoczanDate: 2007-08-07 01:05:37
Subject: Re: BUG #3504: Some listening sessions never return from writing, problems ensue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group