Thread: How to optimize this query ?

How to optimize this query ?

From
proghome@silesky.com (krystoffff)
Date:
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
(10000rows)        (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


Re: How to optimize this query ?

From
Stephan Szabo
Date:
On 12 Aug 2003, krystoffff wrote:

> 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, ...)
>

I think you can possibly get better results in 7.3 and earlier
by using NOT EXISTS.

> Here is the query:
> SELECT *
> FROM lead
> WHERE
> (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
>

NOT EXISTS (select lead_id from purchase where lead_id=lead.id and member_id=21101)

> id NOT IN (
> SELECT lead_id
> FROM purchase
> WHERE member_id = 21101

AND NOT EXISTS (select * from affiliate_lockoutWHERE member_id=21101 and affiliate_locked_id=lead.affiliate_id)

> ) 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

Doesn't this condition end up giving you a subset of the rows in the first
one?




Re: How to optimize this query ?

From
"Franco Bruno Borghesi"
Date:
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





Re: How to optimize this query ?

From
Stephan Szabo
Date:
On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote:

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

It does not in general unfortunately. :( I see two possible
problems.

The first is that by using an inner join you're going to lose
any rows where there is no match on the right hand table which
the original doesn't.

The second is that if there were two rows in affiliate_lockout
with the different member_ids but the same affiliate_locked_id
say, (21101, 10) and (21201, 10) and you were matching a lead row
with a affiliate_id of 10, the second row would get past the
condition since it has a member_id!=21101, but the original
would drop the row because there existed some matching
affiliate_lockout row where the member_id was 21101.

> 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
>
> > 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>








Re: How to optimize this query ?

From
Franco Bruno Borghesi
Date:
Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong?<br /><br />
SELECT<br/>    L.*<br /> FROM<br />       lead L<br />       LEFT JOIN purchase P ON (L.id=P.lead_id)<br />       LEFT
JOINmember_exclusion M ON (P.member_id=M.member_id_to_exclude)<br />       LEFT JOIN (<br />        SELECT DISTINCT
affiliate_locked_idFROM affiliate_lockout WHERE member_id=21101<br />     ) A ON
(L.affiliate_id=A.affiliated_locled_id)<br/> WHERE<br />         L.exclusive IS NULL OR<br />         (<br />
               L.exclusive=0 AND<br />                 L.nb_purchases<3<br />         ) AND<br />         (P.lead_id
ISNULL OR P.lead_id<>21101) AND<br />   (M.member_id IS NULL) AND<br />         (A.member_id IS NULL)<br /><br
/><br/> On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote: <blockquote type="CITE"><pre><font color="#737373"><i>On Tue,
12Aug 2003, Franco Bruno Borghesi wrote: 

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

It does not in general unfortunately. :( I see two possible
problems.

The first is that by using an inner join you're going to lose
any rows where there is no match on the right hand table which
the original doesn't.

The second is that if there were two rows in affiliate_lockout
with the different member_ids but the same affiliate_locked_id
say, (21101, 10) and (21201, 10) and you were matching a lead row
with a affiliate_id of 10, the second row would get past the
condition since it has a member_id!=21101, but the original
would drop the row because there existed some matching
affiliate_lockout row where the member_id was 21101.

> 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
>
> > 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?
> >
> >               </i></font><a
href="http://www.postgresql.org/docs/faqs/FAQ.html"><u>http://www.postgresql.org/docs/faqs/FAQ.html</u></a>
<font color="#737373">>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>




</font>
</pre></blockquote>

Re: How to optimize this query ?

From
Stephan Szabo
Date:
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)






Re: How to optimize this query ?

From
Stephan Szabo
Date:
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 likeid = 2, affiliate_id = 2
And rows in affiliate_lockout like:affiliate_locked_id=2, member_id=21101affiliate_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@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)
>
>
>
>
>



Re: How to optimize this query ?

From
Stephan Szabo
Date:
On Wed, 13 Aug 2003, ProgHome wrote:

> select member.id, automated.delivery, member.email
>
> from (automated INNER JOIN member ON member.id = automated.member_id)
>
> where activated=1
>   and website='$SITE_NAME'
>   and (select count(*) from trans_member where
> (unix_timestamp(now())-unix_timestamp(date)) <
> (unix_timestamp(now())-'$today_midnight') and type='purchase' and
> comment LIKE '%automated%'
>   and member_id=member.id and comment LIKE '%$type%') < max_$field
>   and balance_in_points > $price
>   and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%'
>   and states LIKE '%$lead[prop_state]%'
>   and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) /
> cast($lead[current_value] as unsigned))
>   and amount_t$n < $lead[loan_amount]
>
>   AND $id NOT IN (select lead_id from purchase where
> member_id=member.id)
>   AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout
> where member_id=member.id)
>   AND $id NOT IN (select lead_id from purchase where member_id IN
> (select member_id_to_exclude from member_exclusion where
> member_id=member.id))

In these cases I'd suggest trying a NOT EXISTS, maybe something like
(for the first one):
AND NOT EXISTS (select 1 from purchase where member_id=member.idAND lead_id=$id)

With a two column index on purchase(member_id,lead_id) [or lead_id,
member_id maybe] you might be able to do okay.




Re: How to optimize this query ?

From
proghome@silesky.com (krystoffff)
Date:
OK, here is the final query without any subquery ...


--------------------------
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
) AS A ON ( L.affiliate_id = A.affiliate_locked_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 (
M.member_id IS NULL 
)
GROUP BY L.id
-----------------------

I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...

Thanks, Franco !

PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !


Re: How to optimize this query ?

From
"ProgHome"
Date:
<p align="LEFT"><span lang="en-us"><font face="Courier New" size="2">Actually,</font></span><span lang="en-us"> <font
face="CourierNew" size="2">I have got another query where I need to remove the subqueries ...</font></span><span
lang="en-us"><fontface="Courier New" size="2"> It is almost the same query but this time, I don't search in the table
LEA</font></span><spanlang="en-us"><font face="Courier New" size="2">D</font></span><span lang="en-us"><font
face="CourierNew" size="2"> but in the table</font></span><span lang="en-us"> <font face="Courier New"
size="2">MEMBER</font></span><spanlang="en-us"> <font face="Courier New" size="2">…</font></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2">HERE are the
tables:</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"></font></span><span
lang="en-us"> <fontface="Courier New" size="2">affiliate_lockout  (6 rows)  (member_id,
affiliate_id)</font></span><spanlang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Courier New"
size="2"> automated     (4 rows)        (member_id,</font></span><span lang="en-us"> <font face="Courier New"
size="2">…</font></span><spanlang="en-us"><font face="Courier New" size="2">)</font></span><span lang="en-us"></span><p
align="LEFT"><spanlang="en-us"><font face="Courier New" size="2"> lead   (4490 rows)           (id,
...)</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member  (6 rows)            
(id,...)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member_exclusion (3
rows)   (member_id, member_id_to_exclude)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New"
size="2"></font> <fontface="Courier New" size="2">purchase (10000 rows)        (lead_id, member_id, ...</font><font
face="CourierNew" size="2">)</font></span><br /><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier
New"size="1">----------------------------</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">select member.id, automated.delivery, member.email </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">from (automated INNER JOIN member ON</font> <font color="#000000"
face="CourierNew" size="1">member.id = automated.member_id)</font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">where activated=1</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">  and website='$SITE_NAME'</font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">  and (select count(*) from trans_member where
(unix_timestamp(now())-unix_timestamp(date))<</font></span><span lang="en-us"></span><span lang="en-us"><font
color="#000000"face="Courier New" size="1"></font></span><span lang="en-us"></span><span lang="en-us"> <font
color="#000000"face="Courier New" size="1">(unix_timestamp(now())-'$today_midnight') and type='purchase' and comment
LIKE</font><fontcolor="#000000" face="Courier New" size="1"> '%automated%' </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  and member_id=member.id and comment LIKE '%$type%')
<max_$field </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1">  and
balance_in_points> $price </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New"
size="1"> and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  and states LIKE '%$lead[prop_state]%' </font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1">  and ltv_t$n/100 >=
(cast(</font><fontcolor="#000000" face="Courier New" size="1">$lead[loan_amount] as unsigned) /
cast($lead[current_value]as unsigned)) </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">  and amount_t$n < $lead[loan_amount] </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">  AND $id NOT IN (select lead_id from purchase where member_id=member.id)
</font></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1">  AND $aff_id NOT IN
(selectaffiliate_locked_id from affiliate_lockout where member_id=member.id) </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  AND $id NOT IN (select lead_id from purchase where
member_idIN (select member_id_to_exclude from member_exclusion where member_id=member.id))</font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">ORDER</font> <font color="#000000" face="Courier New"
size="1">BYbalance_in_points DESC</font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier
New"size="2">--------------------</font></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="2"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="2">For this one, I really don</font><font color="#000000" face="Courier New"
size="2">’</font><fontcolor="#000000" face="Courier New" size="2">t know how to remove the 3 subqueries at the end
becausethe $id and $aff_id are values external to the query, and there is no table to join …</font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="2"> </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="2">I tried to remove the subqueries and to rewrite
them</font><fontcolor="#000000" face="Courier New" size="2"> to 3 small external queries that I run for each result
givenby this first query, but I guess this will be much longer if the tables are big …</font></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="2">What do you
thinkabout ?</font></span><span lang="en-us"></span> 

Re: How to optimize this query ?

From
"ProgHome"
Date:
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)







Re: How to optimize this query ?

From
proghome@silesky.com (krystoffff)
Date:
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's like to remove the last subquery, to see if it faster ;)
Can somebody help me ?


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

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


Re: How to optimize this query ?

From
proghome@silesky.com (krystoffff)
Date:
For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to reuse the alias "nb_bogus_leads", for instance). Do you have a way
to avoid this ? Because If I do so, the same query is calculated twice
...

Second problem, the most important :
The A.id should be for each result returned in A.*, and there should
be a join to calculate the query "nb_bogus_leads" (for instance) about
the A.id currently processed by the query.
But it seems that this join doesn't work, because I have the same
"nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned
(they should be different !)

How can you make this query work ?
Thanks


SELECT A. * , (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) AS nb_bogus_leads, (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) AS nb_leads_submitted, (

CASE WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) <> 0
THEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) / ( 
SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id ) * 100
WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) = 0
THEN 0 
END 
) AS percentage_bogus_leads
FROM affiliate A
WHERE website = 'dev'


Re: How to optimize this query ?

From
"ProgHome"
Date:
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">As I am using <span class="SpellE">mysql</span> 4.0 right now (we’ve got a stupid
problemwith the 4.1 with the <span class="SpellE">authentification</span> protocol we can’t figure out) and the last
<spanclass="SpellE">subquery</span> (the one in the last LEFT JOIN) MUST be removed …</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">So I tried the following query:</span></font><p class="MsoNormal"><span
class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font></span><pclass="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt">SELECT<br />    <span class="GramE">L.*</span><span
class="syntaxalphasyntaxalphareservedword"></span></span></font><pclass="MsoNormal"><span
class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">FROM</span></font></span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">lead</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">L</span><br/><span class="syntaxalphasyntaxalphareservedword">LEFT</span><span
class="syntax"></span><span class="syntaxalphasyntaxalphareservedword">JOIN</span><span class="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">purchase</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">P1</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span class="GramE"><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">id</span></span></span><spanclass="syntax"> </span><span
class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P1</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id</span><span
class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><br
/><spanclass="syntaxalphasyntaxalphareservedword">LEFT</span><span class="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">JOIN</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">affiliate_lockout</span></span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">A</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">affiliate_id</span></span><spanclass="syntax"> </span><span
class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">A</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">affiliate_locked_id</span></span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><br /><span
class="syntaxalphasyntaxalphareservedword">LEFT</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">JOIN</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span
class="GramE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt">purchase</span></font></span></span><span
class="syntaxalphasyntaxalphaidentifier">P2</span><br/><span class="syntaxalphasyntaxalphareservedword">INNER</span>
<spanclass="syntaxalphasyntaxalphareservedword">JOIN</span> <span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">member_exclusion</span></span><span
class="syntaxalphasyntaxalphaidentifier">M</span><span class="syntaxalphasyntaxalphareservedword">ON</span> <span
class="GramE"><spanclass="syntaxpunctsyntaxpunctbracketopenround">(</span> <span
class="syntaxalphasyntaxalphaidentifier">P2</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">member</span></span><spanclass="syntaxalphasyntaxalphaidentifier">_id</span>
<spanclass="syntaxpunct">=</span> <span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">M</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">member_id_to_exclude</span></span><span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span><pclass="MsoNormal"><span
class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">)</span></font></span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">id</span></span><spanclass="syntax"> </span><span
class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P2</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id</span><span
class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><br
/><spanclass="syntaxalphasyntaxalphareservedword">WHERE</span><span class="syntax"> </span><span
class="syntaxalphasyntaxalphafunctionname">UNIX_TIMESTAMP</span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphafunctionname">now</span><spanclass="syntaxpunctsyntaxpunctbracketopenround">(</span><span
class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><span
class="syntaxpunct">-</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphafunctionname">UNIX_TIMESTAMP</span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">date_creation</span></span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><span
class="syntaxpunct"><=</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span
class="syntaxdigitsyntaxdigitinteger">6</span><spanclass="syntax"> </span><span class="syntaxpunct">*</span><span
class="syntax"></span><span class="syntaxdigitsyntaxdigitinteger">24</span><span class="syntax"> </span><span
class="syntaxpunct">*</span><spanclass="syntax"> </span><span class="syntaxdigitsyntaxdigitinteger">3600</span><span
class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">AND</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span
class="GramE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt">exclusive</span></font></span></span><span
class="syntaxalphasyntaxalphareservedword">IS</span><span class="syntaxalphasyntaxalphareservedword">NULL</span> <span
class="syntaxalphasyntaxalphareservedword">OR</span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><p
class="MsoNormal"><spanclass="GramE"><span class="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt">exclusive</span></font></span></span> <span class="syntaxpunct">=</span> <span
class="syntaxdigitsyntaxdigitinteger">0</span><span class="syntaxalphasyntaxalphareservedword">AND</span> <span
class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier">nb_purchases</span></span> <span
class="syntaxpunct"><</span><span class="syntaxdigitsyntaxdigitinteger">3</span><p class="MsoNormal"><span
class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">)</span></font></span><pclass="MsoNormal"><span
class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">)</span></font></span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphareservedword">AND</span><spanclass="syntax"> </span><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span
class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt">A.member_id</span></font></span></span><span class="syntaxpunct"><></span> <span
class="syntaxdigitsyntaxdigitinteger">21101</span><span class="syntaxalphasyntaxalphareservedword">OR</span> <span
class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier">A</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">member_id</span></span>
<spanclass="syntaxalphasyntaxalphareservedword">IS</span> <span class="GramE"><span
class="syntaxalphasyntaxalphareservedword">NULL</span><span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span></span><spanclass="syntax"> </span><p class="MsoNormal"><span
class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">AND</span></font></span><spanclass="syntax"> </span><span class="GramE"><span
class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span
class="syntaxalphasyntaxalphaidentifier">P1</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">member</span></span><spanclass="syntaxalphasyntaxalphaidentifier">_id</span>
<spanclass="syntaxpunct"><></span> <span class="syntaxdigitsyntaxdigitinteger">21101</span> <span
class="syntaxalphasyntaxalphareservedword">OR</span><span class="syntaxalphasyntaxalphaidentifier">P1</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">member_id</span> <span
class="syntaxalphasyntaxalphareservedword">IS</span><span class="syntaxalphasyntaxalphareservedword">NULL</span> <span
class="syntaxpunctsyntaxpunctbracketcloseround">)</span><pclass="MsoNormal"><span
class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font></span><pclass="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt">But it seems that the LEFT JOIN doesn’t work anymore and are replaced by OUTER JOIN because the result of the
queryis (number of rows in Lead * number of rows in PURCHASE * number of rows in …)</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 
12.0pt">And it seems that the condition <span class="SpellE"><span
class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span
class="syntaxalphasyntaxalphaidentifier">id</span></span><spanclass="syntax"> </span><span
class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P2</span><span
class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id doesn’t work
either…</span></span></font><p class="MsoNormal"><span class="syntaxalphasyntaxalphaidentifier"><font face="Times New
Roman"size="3"><span style="font-size:12.0pt"> </span></font></span><p class="MsoNormal"><span
class="syntaxalphasyntaxalphaidentifier"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">Could you
tellme what the problem <span class="GramE">is ?</span></span></font></span><p class="MsoNormal"><span
class="syntaxalphasyntaxalphaidentifier"><fontface="Times New Roman" size="3"><span
style="font-size:12.0pt">Thanks</span></font></span><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal" style="margin-left:.5in"><font face="Tahoma"
size="2"><spanstyle="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<br /><b><span
style="font-weight:bold">From:</span></b>Franco Bruno Borghesi [mailto:franco@akyasociados.com.ar] <br /><b><span
style="font-weight:bold">Sent:</span></b></span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">Wednesday,August 13, 2003</span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"></span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">12:18PM</span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"><br/><b><span style="font-weight:bold">To:</span></b> proghome@silesky.com;
pgsql-sql@postgresql.org<br/><b><span style="font-weight:bold">Subject:</span></b> Re: [SQL] How to optimize this query
?</span></font><pclass="MsoNormal" style="margin-left:.5in"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font><prestyle="margin-left:.5in"><font face="Courier New" size="2"><span
style="font-size:10.0pt">Maybeits better now. Anyway, what I think is that joining will perform better than using IN.
AmI wrong?<br /> 
<br />
SELECT<br />
   <span class="GramE">L.</span>*<br />
FROM<br />
      lead L<br />
      LEFT JOIN purchase P ON (L.id=P.lead_id)<br />
      LEFT JOIN member_exclusion M ON (P.member_id=M.member_id_to_exclude)<br />
      LEFT JOIN (<br />
       SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id=21101<br />
    ) A ON (L.affiliate_id=A.affiliated_locled_id)<br />
WHERE<br />
        L.exclusive IS NULL OR<br />
        (<br />
                L.exclusive=0 AND<br />
                L.nb_purchases<3<br />
        ) AND<br />
        (P.lead_id IS NULL OR P.lead_id<>21101) AND<br />
  (M.member_id IS NULL) AND<br />
        (A.member_id IS NULL)<br />
<i><font color="#737373"><span style="color:#737373;font-style:italic"><span
style="mso-spacerun:yes"> </span></span></font></i></span></font></pre><prestyle="margin-left:.5in"><font face="Courier
New"size="2"><span style="font-size:10.0pt"> </span></font></pre></div> 

Re: How to optimize this query ?

From
Stephan Szabo
Date:
On Wed, 27 Aug 2003, ProgHome wrote:

> You were right, Stephan !
> The query below is still not correct ... because the second line
> shouldn't be shown !
> Now I really don't know how I could rewrite this without a subquery
> because it doesn't seem to be possible with some LEFT or INNER joins !
> Do you have an idea ?

The only ways I can think of are through a subquery (*) or possibly there
might be a way to do it with EXCEPT, but on first blush that seems like it
might be difficult and probably not any better speed wise.

(*) - You were using IN (subquery) which is known to be poorly optimized
for 7.3 and earlier.  You might want to see how it performs on your data
under 7.4beta for forward looking, and/or consider converting into a form
using EXISTS rather than IN.





Re: How to optimize this query ?

From
"ProgHome"
Date:
You were right, Stephan !
The query below is still not correct ... because the second line
shouldn't be shown !
Now I really don't know how I could rewrite this without a subquery
because it doesn't seem to be possible with some LEFT or INNER joins !
Do you have an idea ?

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] 
Sent: Wednesday, August 13, 2003 2:29 PM
To: ProgHome
Cc: 'Franco Bruno Borghesi'; pgsql-sql@postgresql.org
Subject: RE: [SQL] 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 likeid = 2, affiliate_id = 2
And rows in affiliate_lockout like:affiliate_locked_id=2, member_id=21101affiliate_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.