Re: How to optimize this query ?

On Wed, 13 Aug 2003, ProgHome wrote:

> 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

As I replied to Franco for his query below, I believe this query is not
equivalent to your original query for a few cases, but those might not
come up.

If you had a row in lead like
id = 2, affiliate_id = 2
And rows in affiliate_lockout like:
affiliate_locked_id=2, member_id=21101
affiliate_locked_id=2, member_id=31101
should this row in lead be shown or not?

In the original query I think it would not (because lead.affiliate_id was
IN the affiliate_lockout table where member_id=21101). In the above query
I think it will, because one of the joined tables will have the lead
information and a member_id that is not equal to 21101.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
> Sent: Wednesday, August 13, 2003 1:10 PM
> To: Franco Bruno Borghesi
> Cc: proghome(at)silesky(dot)com; pgsql-sql(at)postgresql(dot)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)
>
>
>
>
>