Re: How to optimize this query ? - Mailing list pgsql-sql

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