Hello David, Saturday, May 15, 2021, 5:52:47 PM, you wrote:
> On Sun, 16 May 2021 at 02:34, Eugen Konkov <kes-...@yandex.ru> wrote: >> I found a case when `not assigning a ressortgroupref to the whole-row var` >> cause >> wrong window function calculations. >> >> I use same query. The difference come when I wrap my query into >> function. (see full queries in attachment) >> >> 1. >> SELECT * >> FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) ) >> WHERE agreement_id = 161::int AND (o).period_id = 10::int >> >> 2. >> SELECT * >> sum( .... ) over wagreement >> FROM .... >> WHERE agreement_id = 161::int AND (o).period_id = 10::int >> WINDOW wagreement AS ( PARTITION BY agreement_id ) >> >> For first query window function calculates SUM over all agreements, >> then some are filtered out by (o).period_id condition. > This is unrelated to the optimisation that you were asking about before. > All that's going on here is that WHERE is evaluated before SELECT. > This means that your filtering is done before the window functions are > executed. This is noted in the docs in [1]: >> The rows considered by a window function are those of the “virtual table” >> produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and >> HAVING clauses if any. For example, a row removed because it does not meet >> the WHERE condition is not seen by any window function. A query can contain >> multiple window functions that slice up the data in different ways using >> different OVER clauses, but they all act on the same collection of rows >> defined by this virtual table. > If you want to filter rows after the window functions are evaluated > then you'll likely want to use a subquery. > David > [1] https://www.postgresql.org/docs/13/tutorial-window.html Sorry, I miss that WHERE works first and after it window function. >This is unrelated to the optimisation that you were asking about before. So, yes, unrelated. Thank you for your answers. -- Best regards, Eugen Konkov
