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

8.4 semi-join slows down query performance (EXISTS)

From: vacuum(at)quantentunnel(dot)de
To: pgsql-hackers(at)postgresql(dot)org
Subject: 8.4 semi-join slows down query performance (EXISTS)
Date: 2009-04-21 09:38:06
Message-ID: 20090421093806.260250@gmx.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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).

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.

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 the end 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: pg83_84_query.zip
Description: application/zip (5.7 KB)

Responses

pgsql-hackers by date

Next:From: Albe LaurenzDate: 2009-04-21 10:36:44
Subject: Re: trouble with to_char('L')
Previous:From: Pavel StehuleDate: 2009-04-21 09:29:36
Subject: Re: Extend PL/pgSQL

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