Message-ID: <475CC478.7010102@Sheeky.Biz>
Date: Mon, 10 Dec 2007 15:15:44 +1030
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Gerry Reno <greno@verizon.net>
CC: pgsql-sql@postgresql.org
Subject: Re: join on three tables is slow
References: <475C96EA.6090002@verizon.net>
In-Reply-To: <475C96EA.6090002@verizon.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Gerry Reno wrote:
> I have a join that uses three tables but it runs rather slow.  For 
> example, the following command takes about 10 min. to run.  It gets the 
> correct result but what should I do to increase the performance of this 
> query?  This query is the end result of some python code hence the big 
> id list.
> 
> myfile has 600 records, res_partner has 600 records, res_partner_address 
> has 1000 records
> 
> select p.addr, p.name, p.name2 from myfile as p join res_partner as e on 
> e.id in ( 3214, 3213, 3212, 3211, 3210, 3209, 3208, 3207, 3206, 3205, 

snip

> 3179, 3178, 3177, 50, 49, 48, 47, 22, 25 ) join res_partner_address as a 
> on a.partner_id = e.id and (a.type = 'default' or a.type IS NULL) and 
> (p.name != a.name or p.name2 != a.name2) and p.addr = e.addr where 
> e.active = '1' and p.date = e.date and e.date = (select max(date) from 
> res_partner as msd where msd.addr = p.addr)
> 

To start with -

You have join res_partner as e on e.id in (... big list...)
That list should be the contents of a where clause not a join. You want 
that first part to be join res_partner as e on e.id=p.something

So as a first step that join will link all 523 res_partner rows listed 
with every myfile row - that means you will get 313,800 rows from this 
join with your other joins and where clause then trim that down to the 
final result.


I would also say that the rest of your joins don't appear to be what you 
really want. (but part of them may belong in the where clause)


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz