From: | proghome(at)silesky(dot)com (krystoffff) |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | How to optimize this query ? |
Date: | 2003-08-12 18:21:23 |
Message-ID: | 85898f7e.0308121021.4c66aae5@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vernon Smith | 2003-08-12 23:06:33 | Re: INSERT INTO ... SELECT |
Previous Message | Jonathan Gardner | 2003-08-12 16:46:54 | Re: How to check: is some key referenced from sometable |