From: proghome@silesky.com (krystoffff)
Subject: How to optimize this query ?
Date: 12 Aug 2003 11:21:23 -0700
Organization: http://groups.google.com/
Lines: 51
Message-ID: <85898f7e.0308121021.4c66aae5@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

Hi all

I have to optmize this query, because it takes a while to run (about
30s)

Here are the tables (with the keys):
 affiliate_lockout  (6 rows)  (member_id, affiliate_id)
 lead   (4490 rows)           (id, ...)
 member  (6 rows)             (id, ...)
 member_exclusion (3 rows)    (member_id, member_id_to_exclude)
 purchase (10000 rows)        (lead_id, member_id, ...)


Here is the query:
SELECT *
FROM lead
WHERE 
(exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND 

id NOT IN (

SELECT lead_id
FROM purchase
WHERE member_id = 21101
) AND affiliate_id NOT 
IN (

SELECT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) 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
)


I wonder the problem is with the subqueries (which are apparently very
slow to run, according to what I read), but I can't figure how to
rewrite this query without any subquery ...

Maybe the problem comes from the index ... How would you create your
indexes to optimize this query ?

Could somebody help me ?
Thanks
krystoffff