Thread: 8.4 semi-join slows down query performance (EXISTS)

8.4 semi-join slows down query performance (EXISTS)

From
vacuum@quantentunnel.de
Date:
Hello folk,

I migrate a pg 8.3 database to a pg 8.4 backend for testing. All works fine except changes or new features of the
planner.

There are two tables. The first (A) stores "data" - second table (B) holds (forinstance) "structure" information and
referencesto A by defining foreign-key constraint(s). 

I've queries returning tupels of A, with a (sub-)selected constant-expression that indicates whether a referenced tupel
existsin B or not. For this issue the EXISTS clause is used. 

In past (8.3) the planner resolves this into index-scans using existing foreign-key indices -> fast query (1.5 seconds
forcomparison). Now (in 8.4) the planner wants "semi-joins". Index-scans are not longer used and my query needs 600
secondsto return. 

I attached two plans of the identical query - executed in 8.3 and 8.4 as well as the query itself.

You will see some more differences between planning in 8.3 and 8.4. The differences relating this mail you can find at
theend of the plans. 

thanks

--
Neu: GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate + Telefonanschluss für nur 17,95 Euro/mtl.!*
http://dslspecial.gmx.de/freedsl-surfflat/?ac=OM.AD.PD003K11308T4569a

Attachment

Re: 8.4 semi-join slows down query performance (EXISTS)

From
Guillaume Smet
Date:
On Tue, Apr 21, 2009 at 11:38 AM,  <vacuum@quantentunnel.de> wrote:
> I've queries returning tupels of A, with a (sub-)selected constant-expression that indicates whether a referenced
tupelexists in B or not. For this issue the EXISTS clause is used.
 
>
> In past (8.3) the planner resolves this into index-scans using existing foreign-key indices -> fast query (1.5
secondsfor comparison). Now (in 8.4) the planner wants "semi-joins". Index-scans are not longer used and my query needs
600seconds to return.
 

That worries me a bit for one of our applications too. We use EXISTS
in several places to trick the planner when the statistics are way off
(cross columns/cross tables) and I'm not sure making EXISTS more
clever will help us.

-- 
Guillaume


Re: 8.4 semi-join slows down query performance (EXISTS)

From
"steven king"
Date:
I hope this will treat as a "planner-bug". I think the problem is tricky but not hard to solve.

-------- Original-Nachricht --------
> Datum: Tue, 21 Apr 2009 13:53:01 +0200
> Von: Guillaume Smet <guillaume.smet@gmail.com>
> An: vacuum@quantentunnel.de
> CC: pgsql-hackers@postgresql.org
> Betreff: Re: [HACKERS] 8.4 semi-join slows down query performance (EXISTS)

> On Tue, Apr 21, 2009 at 11:38 AM,  <vacuum@quantentunnel.de> wrote:
> > I've queries returning tupels of A, with a (sub-)selected
> constant-expression that indicates whether a referenced tupel exists in B or not. For
> this issue the EXISTS clause is used.
> >
> > In past (8.3) the planner resolves this into index-scans using existing
> foreign-key indices -> fast query (1.5 seconds for comparison). Now (in
> 8.4) the planner wants "semi-joins". Index-scans are not longer used and my
> query needs 600 seconds to return.
> 
> That worries me a bit for one of our applications too. We use EXISTS
> in several places to trick the planner when the statistics are way off
> (cross columns/cross tables) and I'm not sure making EXISTS more
> clever will help us.
> 
> -- 
> Guillaume
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
Neu: GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate + Telefonanschluss für nur 17,95 Euro/mtl.!*
http://dslspecial.gmx.de/freedsl-surfflat/?ac=OM.AD.PD003K11308T4569a


Re: 8.4 semi-join slows down query performance (EXISTS)

From
Alvaro Herrera
Date:
vacuum@quantentunnel.de wrote:
> Hello folk,
> 
> I migrate a pg 8.3 database to a pg 8.4 backend for testing. All works
> fine except changes or new features of the planner.
> 
> There are two tables. The first (A) stores "data" - second table (B)
> holds (forinstance) "structure" information and references to A by
> defining foreign-key constraint(s).

Can you please post the table definitions?  "pg_dump -t" output would be
best.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.