From: Shane Ambler <pgsql@Sheeky.Biz>
To: Gerry Reno <greno@verizon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: join on three tables is slow
Date: Mon, 10 Dec 2007 15:15:44 +1030
Message-ID: <475CC478.7010102@Sheeky.Biz> (raw)
In-Reply-To: <475C96EA.6090002@verizon.net>
References: <475C96EA.6090002@verizon.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@Sheeky.Biz, greno@verizon.net
Subject: Re: join on three tables is slow
In-Reply-To: <475CC478.7010102@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox