How to optimize this query ?

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

Responses

Browse pgsql-sql by date

  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