Message-ID: <42EA4E78.90305@archonet.com>
Date: Fri, 29 Jul 2005 16:42:48 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why are these queries so different in time?
References: <op.suofx0h694ope3@olivier.amaze.ulb.ac.be>
In-Reply-To: <op.suofx0h694ope3@olivier.amaze.ulb.ac.be>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Olivier Hubaut wrote:
> Hi, I have a question about performance querying a 7.4 database. The  
> orginal generated query was
> 
> SELECT DISTINCT _compound0.object_id AS "ObjectId"
>   FROM
(4 LEFT JOINS then a couple of WHERE conditions on 2 tables)

> This on take a huge time to perform, which may come to a timeout on the  
> front-end application that uses the database.


> So, I decided to modify manually the query like this:
> 
> SELECT DISTINCT _compound0.object_id AS "ObjectId"
>   FROM
(Two lots of 2 x Left-joins, unioned together)

> This should give the same result set, but it's really faster than the  
> previous one, more than one thousand time faster.
> Is there a reason for this huge difference of performance?

You're probably processing 1000 more rows in the first example. It's 
probably running the LEFT JOIN across all the tables then restricting 
the results in the WHERE. As it happens you're throwing away duplicates 
with DISTINCT and/or UNION anyway, so you never get to see the results.

Try an EXPLAIN ANALYSE of the first example and see if the rows= parts 
indicate very large numbers of rows being processed.

To make it faster I'd remove the LEFT JOINs, since your WHERE conditions 
seem to rule out the NULL cases anyway.

--
   Richard Huxton
   Archonet Ltd