Re: How do query optimizers affect window functions

On Wed, Nov 14, 2012 at 2:16 PM, Tianyin Xu <tixu(at)cs(dot)ucsd(dot)edu> wrote:
> Thanks a lot, Jeff!
>
>
> On Wed, Nov 14, 2012 at 11:59 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>
>> On Wed, Nov 14, 2012 at 12:12 AM, Tianyin Xu <tixu(at)cs(dot)ucsd(dot)edu> wrote:
>> > Hi, Postgresql,
>> >
>> > I want to understand how the query optimizers affect the output of the
>> > window functions.
>>
>> Use "EXPLAIN".
>>
>> One is an index scan, one is a bitmap scan. They return rows in a
>> different order.
>>
>> ..
>>
>> > I don't understand why the results are different. Intuitively, the
>> > queries
>> > show return the same results no matter what plan the optimizer choose.
>>
>> My intuition is that the query should refuse to run at all, because
>> the results are order dependent and you haven't specified an ordering.
>>
>
> What do you mean by "refused to run"?

I mean that it could throw an error. Kind of like the way this
currently throws an error:

select b, sum(b) from foo;
ERROR: column "foo.b" must appear in the GROUP BY clause or be used
in an aggregate function.

To be clear, I am not saying that it does do this (clearly it does
not), just that my intuition is that it should do this.

> You mean we have to specify the order
> when using the window functions? Could you explain more?

Not all uses of window functions have results that depend on the
order. If you only use "partition by", there would be no reason to
force an ordering, for example.

Cheers,

Jeff