Re: How to optimize this query ?

From: "Franco Bruno Borghesi" <franco(at)akyasociados(dot)com(dot)ar>
To: <proghome(at)silesky(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: How to optimize this query ?
Date: 2003-08-13 01:04:32
Message-ID: 4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2003-08-13 01:29:14 Re: How to optimize this query ?
Previous Message Stephan Szabo 2003-08-13 00:52:47 Re: How to optimize this query ?