From: proghome@silesky.com
To: pgsql-sql@postgresql.org
Subject: How to optimize this query ?
Date: 12 Aug 2003 11:21:23 -0700
Message-ID: <85898f7e.0308121021.4c66aae5@posting.google.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: proghome@silesky.com
Subject: Re: How to optimize this query ?
In-Reply-To: <85898f7e.0308121021.4c66aae5@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox