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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
