Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-24 Thread Bruce Momjian
On Wed, May 16, 2012 at 09:25:13AM -0400, Tom Lane wrote: > Volker Grabsch writes: > > I propose the following general optimization: If all window > > functions are partitioned by the same first field (here: id), > > then any filter on that field should be executed before > > WindowAgg. > > I'm n

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-22 Thread Hitoshi Harada
On Thu, May 17, 2012 at 7:26 AM, Volker Grabsch wrote: > Hitoshi Harada schrieb: >> On Wed, May 16, 2012 at 12:50 AM, Volker Grabsch >> wrote: >> > I propose the following general optimization: If all window >> > functions are partitioned by the same first field (here: id), >> > then any filter

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-17 Thread Nicolas Barbier
2012/5/17 Volker Grabsch : > Also, is there any chance to include a (simple) attempt of > such an optimiztation into PostgreSQL-9.2 beta, or is this > only a possible topic for 9.3 and later? For 9.2, you’re about 4 months late :-). The last commitfest was in Januari: https://commitfest.postgres

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-17 Thread Volker Grabsch
Hitoshi Harada schrieb: > On Wed, May 16, 2012 at 12:50 AM, Volker Grabsch > wrote: > > I propose the following general optimization: If all window > > functions are partitioned by the same first field (here: id), > > then any filter on that field should be executed before > > WindowAgg. So a que

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-16 Thread Hitoshi Harada
On Wed, May 16, 2012 at 12:50 AM, Volker Grabsch wrote: > I propose the following general optimization: If all window > functions are partitioned by the same first field (here: id), > then any filter on that field should be executed before > WindowAgg. So a query like this: I think that's possib

Re: [HACKERS] Missing optimization when filters are applied after window functions

2012-05-16 Thread Tom Lane
Volker Grabsch writes: > I propose the following general optimization: If all window > functions are partitioned by the same first field (here: id), > then any filter on that field should be executed before > WindowAgg. I'm not sure if that rule is correct in detail, but in any case the short ans

[HACKERS] Missing optimization when filters are applied after window functions

2012-05-16 Thread Volker Grabsch
Dear PostgreSQL hackers, [ please CC to me as I'm not subscribed to the list ] I think there is a missing optimization when a filter is applied after a window function, where the filtered field is also used for partitioning. Here a simplified example: Suppose we have a table that stores 100.000