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

I tried with some LEFT JOINS, which give me the possibility to keep
the information of the right table.

I have now the following query, which is 10 times faster !!! (from 16s
to 1.6s)
But I's like to remove the last subquery, to see if it faster ;)
Can somebody help me ?


-------------------------

SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) 
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND id NOT 
IN (

SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id