[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Should the optimiser convert a CASE into a WHERE if it
From:       <tivv00 () gmail ! com>
Date:       2010-01-27 17:09:59
Message-ID: 331e40661001270909q3f6cdb5et17c9819b29d26b54 () mail ! gmail ! com
[Download RAW message or body]

27 =D3=A6=DE=CE=D1 2010 =D2. 19:01 Matthew Wakeling <matthew@flymine.org> =
=CE=C1=D0=C9=D3=C1=D7:

> On Wed, 27 Jan 2010, =F7=A6=D4=C1=CC=A6=CA =F4=C9=CD=DE=C9=DB=C9=CE wrote=
:
>
>> How  about SELECT SUM (case when id > 1200000 and id < 1210000 then 1 en=
d)
>> from tbl_tracker;
>>
>
> That is very interesting.
>
>
>  * All the functions should be noop for null input
>>
>
> Alas, not true for COUNT(*), AVG(), etc.
>
> select avg(b), count(b), count(*) from (values (2),(null))a(b)
gives  (2.0, 1, 2) for me, so AVG is in game. Sure, it won't work for
count(*), but optimizer already knows which aggregates are strict and which
are not, so no new information is needed.

Best regards, Vitalii Tymchyshyn

[Attachment #3 (text/html)]

<br><br><div class="gmail_quote">27 Ó¦ÞÎÑ 2010 Ò. 19:01 Matthew Wakeling <span \
dir="ltr">&lt;<a href="mailto:matthew@flymine.org">matthew@flymine.org</a>&gt;</span> \
ÎÁÐÉÓÁ×:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex;"> <div class="im">On Wed, 27 Jan 2010, ÷¦ÔÁÌ¦Ê ôÉÍÞÉÛÉÎ \
wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"> How šabout SELECT SUM (case when id &gt; 1200000 and id \
&lt; 1210000 then 1 end)<br> from tbl_tracker;<br>
</blockquote>
<br></div>
That is very interesting.<div class="im"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
                solid;padding-left:1ex">
* All the functions should be noop for null input<br>
</blockquote>
<br></div>
Alas, not true for COUNT(*), AVG(), etc.<br><br></blockquote><div>select avg(b), \
count(b), count(*) from (values (2),(null))a(b)</div><div>gives š(2.0, 1, 2) for me, \
so AVG is in game. Sure, it won&#39;t work for count(*), but optimizer already knows \
which aggregates are strict and which are not, so no new information is needed.</div> \
<div><br></div><div>Best regards, Vitalii Tymchyshyn</div></div>



[prev in list] [next in list] [prev in thread] [next in thread]