Re: Windowing Function Patch Review -> Performance Comparison. - Mailing list pgsql-hackers

Here is another way to solve "big marathon" without window functions (and many other kinds of "windowing" queries,
especiallythose that do not specify "rows preceeding" etc.).<br /><br />It could be considered as a very dirty hack,
howeverit could give you an insight on the performance of the "windowed" query with indexscan instead of seqscan.<br
/><br/><pre class="src"><font color="blue">create</font> <font color="blue">function</font> var_set<span
style="background-color:rgb(255, 249, 140);"></span> (<font color="blue">text</font>,text) <font
color="blue">returns</font><font color="blue">text</font><br />
 
<font color="blue">as</font><br /><font color="red">'<br />  select set_config ('</font><font
color="red">'public.'</font><fontcolor="red">'||$2||pg_backend_pid(), $1, false);<br />'</font> LANGUAGE <font
color="red">'sql'</font>;<br/>
 
<br /><font color="blue">create</font> <font color="blue">function</font> var_get (<font color="blue">text</font>)
<fontcolor="blue">returns</font> <font color="blue">text</font><br /><font color="blue">as</font><br /><font
color="red">'<br/> select current_setting('</font><font color="red">'public.'</font><font
color="red">'||$1||pg_backend_pid());<br/>'</font> LANGUAGE <font color="red">'sql'</font>;<br /><br /><font
color="blue">create</font>operator >>> (<font color="blue">procedure</font> = var_set, leftarg = <font
color="blue">text</font>,rightarg = <font color="blue">text</font>);<br />
 
<font color="blue">create</font> operator <<< (<font color="blue">procedure</font> = var_get, rightarg = <font
color="blue">text</font>);<br/></pre><br /><pre class="src"><font color="teal">-- init values</font><br />
 
<font color="blue">select</font> <font color="red">''</font>>>><font color="red">'prev_time'</font>, <font
color="red">'0'</font>>>><fontcolor="red">'dense_rank'</font>;<br />
 
<br /><font color="teal">-- marathon query</font><br /><font color="blue">select</font> *<br />  <font
color="blue">from</font>(<br />     <font color="blue">select</font> (((<font color="magenta">case</font> <font
color="blue">when</font><font color="blue">time</font>::<font color="blue">text</font> = <<<<font
color="red">'prev_time'</font><font color="blue">then</font> <font color="darkblue"><b>0</b></font> <font
color="blue">else</font><font color="darkblue"><b>1</b></font> <font color="blue">end</font>)+(<<<<font
color="red">'dense_rank'</font>)::int4)::<fontcolor="blue">text</font>>>><font
color="red">'dense_rank'</font>)::int4<font color="blue">as</font> position, runnerid, <font
color="blue">time</font><br/>      <font color="blue">from</font> big_marathon<br />      <font
color="blue">order</font><font color="blue">by</font> <font color="blue">time</font><br />  ) results<br /> <font
color="blue">where</font>position=<font color="darkblue"><b>2</b></font><br />
 
</pre>Best regards,<br />Vladimir Sitnikov<br />