Re: How to optimize this query ?

I *guess* this query does the same as yours (please verify).

SELECTL.*
FROMlead LINNER 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)
WHEREL.exclusive IS NULL OR(    L.exclusive=0 AND    L.nb_purchases<3) ANDP.lead_id<>21101 ANDA.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