Re: How do query optimizers affect window functions

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"? You mean we have to specify the order
when using the window functions? Could you explain more?

Thanks!

> Cheers,
>
> Jeff
>

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/