From: proghome@silesky.com (krystoffff)
Subject: Re: How to optimize this query ?
Date: 13 Aug 2003 11:54:16 -0700
Organization: http://groups.google.com/
Lines: 35
Message-ID: <85898f7e.0308131054.1b1801d2@posting.google.com>
References: 
 <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar>
	<20030812180544.L27665-100000@megazone.bigpanda.com>
	<85898f7e.0308130520.5af10fd0@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

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 !