Re: join on three tables is slow

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