Message-ID: 
 <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar>
Date: Tue, 12 Aug 2003 22:04:32 -0300 (ART)
Subject: Re: How to optimize this query ?
From: "Franco Bruno Borghesi" <franco@akyasociados.com.ar>
To: <proghome@silesky.com>
In-Reply-To: <85898f7e.0308121021.4c66aae5@posting.google.com>
References: <85898f7e.0308121021.4c66aae5@posting.google.com>
Importance: Normal
Cc: <pgsql-sql@postgresql.org>
Reply-To: franco@akyasociados.com.ar
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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