Re: How to optimize this query ?

Sorry, I posted the following message on the newsgroups, but it seems
that you didn't see it ... 


I tried with some LEFT JOINS, which give me the possibility to keep
the information of the right table.

I have now the following query, which is 10 times faster !!! (from 16s
to 1.6s)
But I'd like to remove the last subquery, to see if it faster ;)


-------------------------

SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) 
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) 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
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id



-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] 
Sent: Wednesday, August 13, 2003 1:10 PM
To: Franco Bruno Borghesi
Cc: proghome@silesky.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to optimize this query ?

On 13 Aug 2003, Franco Bruno Borghesi wrote:

> Maybe its better now. Anyway, what I think is that joining will
perform
> better than using IN. Am I wrong?

Generally that's true (for 7.3 and earlier).  For 7.4 IN has gotten much
better, and you probably want to retry with IN.  However, it's possible
that NOT EXISTS will work better than left joins even in 7.3 and
earlier,
I'm not sure, I think it's probably situational.

I think that you're still going to have a problem in the below if there
are purchase rows with member_id 21101 and some other value that both
match. I think you need to do something like the subselect on
affiliate_lockout in the from on purchase as well.

> SELECT
>    L.*
> FROM
>       lead L
>       LEFT JOIN purchase P ON (L.id=P.lead_id)
>       LEFT JOIN member_exclusion M ON
> (P.member_id=M.member_id_to_exclude)
>       LEFT JOIN (
>        SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout
WHERE
> member_id=21101
>     ) A ON (L.affiliate_id=A.affiliated_locled_id)
> WHERE
>         L.exclusive IS NULL OR
>         (
>                 L.exclusive=0 AND
>                 L.nb_purchases<3
>         ) AND
>         (P.lead_id IS NULL OR P.lead_id<>21101) AND

[I think this was meant to be member_id from the original query]

>   (M.member_id IS NULL) AND
>         (A.member_id IS NULL)