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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
