From: | Shane Ambler <pgsql(at)Sheeky(dot)Biz> |
---|---|
To: | Gerry Reno <greno(at)verizon(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: join on three tables is slow |
Date: | 2007-12-10 04:45:44 |
Message-ID: | 475CC478.7010102@Sheeky.Biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Poovendran Moodley | 2007-12-10 06:36:44 | SQL INSERT/TRIGGER Help |
Previous Message | Gerry Reno | 2007-12-10 02:16:32 | Re: join on three tables is slow |