Problem with subquery containg GROUP BY - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’m using <span class="SpellE">postgres</span> 7.4.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have two queries:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(1)</span></font><p class="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">SELECT<span style="mso-spacerun:yes"> </span><span
class="SpellE">a,b</span></span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">FROM table1</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">WHERE a=99</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">(2)</span></font><p class="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">SELECT<span style="mso-spacerun:yes"> </span><span
class="SpellE">a,b,sum</span></span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">(o) <span style="mso-spacerun:yes"> </span>as <span
class="SpellE">sum_o</span></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">FROM table2</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">GROUP BY <span class="SpellE">a<span class="GramE">,b</span></span></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Both Runs very fast.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">But when I try to make (2) as a <span class="SpellE">subquery</span> of (1):</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">SELECT<span style="mso-spacerun:yes"> </span><span
class="SpellE">a,b,sum</span></span></font></span><spanclass="SpellE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:
Arial">_o</span></font></span><font face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">FROM table1</span></font><p class="MsoNormal" style="text-indent:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">LEFT <span class="GramE">JOIN <span
style="mso-spacerun:yes"> </span>(</span></span></font><pclass="MsoNormal"
style="margin-left:.5in;text-indent:.5in"><spanclass="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">SELECT<span style="mso-spacerun:yes"> </span><span
class="SpellE">a,b,sum</span></span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">(o) <span style="mso-spacerun:yes"> </span>as <span
class="SpellE">sum_o</span></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-tab-count:1">            </span><span style="mso-tab-count:1">           
</span>FROMtable2</span></font><p class="MsoNormal" style="margin-left:.5in;text-indent:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">GROUP BY <span class="SpellE">a<span
class="GramE">,b</span></span></span></font><pclass="MsoNormal" style="text-indent:.5in"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">) <span class="SpellE">sub_query</span></span></font><p
class="MsoNormal"style="text-indent:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">USING(<span class="SpellE">a<span
class="GramE">,b</span></span>)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">WHERE a=99</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt">It runs 100 times slower.</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt">I guess it has to do with the <span class="SpellE">postgres</span> unable to pass the a=99 inside the <span
class="SpellE">subquery</span></span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt">Any <span class="GramE">ideas ?</span></span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><span class="SpellE"><span class="GramE"><font face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt">ishay</span></font></span></span></div>