Re: How to optimize this query ?

From: proghome(at)silesky(dot)com (krystoffff)
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: How to optimize this query ?
Date: 2003-08-13 18:54:16
Message-ID: 85898f7e.0308131054.1b1801d2@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

OK, here is the final query without any subquery ...

--------------------------
SELECT L. *
FROM lead L
LEFT JOIN purchase P ON ( L.id = P.lead_id )
LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude
)
LEFT JOIN (

SELECT DISTINCT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AS A ON ( L.affiliate_id = A.affiliate_locked_id )
WHERE L.exclusive IS NULL OR (
L.exclusive = 0 AND L.nb_purchases < 3
) AND (
P.lead_id IS NULL OR P.lead_id <> 21101
) AND (
M.member_id IS NULL
)
GROUP BY L.id
-----------------------

I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...

Thanks, Franco !

PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-08-13 19:35:23 Re: Porting from PL/SQL to PLPGSQL
Previous Message Stephan Szabo 2003-08-13 18:39:39 Re: How to optimize this query ?