Re: Recursive optimization of IN subqueries

<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">My mistake then. Better to check than let a logical hole in… Thanks for letting me
know,Simon</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><div style="border:none;border-left:solid blue 1.5pt;padding:0cm
0cm0cm 4.0pt"><p class="MsoNormal"><font color="black" face="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma;color:windowtext">-----OriginalMessage-----<br /><b><span
style="font-weight:bold">From:</span></b>pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org]
<b><spanstyle="font-weight:bold">On Behalf Of </span></b>Dennis Haney<br /><b><span
style="font-weight:bold">Sent:</span></b>Tuesday, January 27, 2004 14:33<br /><b><span
style="font-weight:bold">To:</span></b>simon@2ndquadrant.com<br /><b><span style="font-weight:bold">Cc:</span></b> 'Tom
Lane';pgsql-hackers@postgresql.org<br /><b><span style="font-weight:bold">Subject:</span></b> Re: [HACKERS] Recursive
optimizationof IN subqueries</span></font><p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt">Simon Riggs wrote: </span></font><blockquote
style="margin-top:5.0pt;margin-bottom:5.0pt"type="cite"><pre wrap=""><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt">Tom Lane writes</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt">Inthe second place, what the code is doing is dependent on an</span></font></pre><pre><font
color="black"face="Courier New" size="2"><span style="font-size:10.0pt">understanding</span></font></pre><pre><font
color="black"face="Courier New" size="2"><span style="font-size:10.0pt">of the semantics of IN; I'm not sure it's
applicableto, say,</span></font></pre><pre><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt">WHERE outervar > ANY (SELECT innervar FROM ...)</span></font></pre><pre><font color="black"
face="CourierNew" size="2"><span style="font-size:10.0pt">and it's definitely not applicable
to</span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span style="font-size:10.0pt"> WHERE
outervar> ALL (SELECT innervar FROM ...)</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt">In particular, the optimization paths that involve unique-ifying
the</span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span style="font-size:10.0pt">subselect
outputand then using it as the outer side of a join would</span></font></pre><pre><font color="black" face="Courier
New"size="2"><span style="font-size:10.0pt">definitely not work for these sorts of
things.</span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span style="font-size:10.0pt">   
</span></font></pre></blockquote><prewrap=""><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt"> </span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span
style="font-size:10.0pt">I'mnot sure if I've understood you correctly in the section above.
Are</span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span style="font-size:10.0pt">you saying
thatthese types of queries don't have a meaningful or</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt">defined response? Or just that they wouldn't be very well optimized as
a</span></font></pre><pre><fontcolor="black" face="Courier New" size="2"><span style="font-size:10.0pt">result of the
unique-ifyingcode changes? Or have I just mis-read the</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt">thread...</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt">  </span></font></pre><p class="MsoNormal"><font color="black" face="Times New
Roman"size="3"><span style="font-size:12.0pt">I think Tom is refering to the context of the specific optimization.<br
/>The optimization we are discussing does nothing to correlated subqueries, and a uncorrolated subquery with >
ALL/ANYis actually a computed constant and not a join.<br /><br /><br /></span></font><pre><font color="black"
face="CourierNew" size="2"><span style="font-size:10.0pt">-- </span></font></pre><pre><font color="black" face="Courier
New"size="2"><span style="font-size:10.0pt">Dennis</span></font></pre></div></div>