From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: proghome@silesky.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Tue, 12 Aug 2003 22:04:32 -0300 (ART)
Message-ID: <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar> (raw)
In-Reply-To: <85898f7e.0308121021.4c66aae5@posting.google.com>
References: <85898f7e.0308121021.4c66aae5@posting.google.com>
I *guess* this query does the same as yours (please verify).
SELECT
L.*
FROM
lead L
INNER JOIN purchase P ON (L.id=P.lead_id)
INNER JOIN affiliate_lockout A ON
(L.affiliate_id=A.affiliate_locked_id)
INNER JOIN member_exclusion M ON
(P.member_id=M.member_id_to_exclude)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
P.lead_id<>21101 AND
A.member_id<>21011
Hope it performs better.
> 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
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: franco@akyasociados.com.ar, proghome@silesky.com
Subject: Re: How to optimize this query ?
In-Reply-To: <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar>
* 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