tucha=> \out f2 tucha=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) select * from order_total_suma() ots where agreement_id = 3943; tucha=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) select * from order_total_suma() ots where (ots.o).agreement_id = 3943; tucha=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from order_total_suma() ots where agreement_id = 3943; tucha=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from order_total_suma() ots where (ots.o).agreement_id = 3943;
Friday, April 16, 2021, 10:18:45 PM, you wrote:
> The following bug has been logged on the website:
> Bug reference: 16968 > Logged by: Eugen Konkov > Email address: kes-kes@yandex.ru > PostgreSQL version: 13.1 > Operating system: Linux Mint 19.3 > Description:
> TLDR; > If I refer to same column by different ways planner may or may not recognize > optimization
> select * from order_total_suma() ots where agreement_id = 3943; > -- fast > select * from order_total_suma() ots where (ots.o).agreement_id = 3943; -- > slow
> Where `order_total_suma` is sql function:
> SELECT > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id > ) AS agreement_suma, > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id, > (ocd.o).id ) AS order_suma, > sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).agreement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS group_cost, > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS group_suma, > max( (ocd.ic).consumed ) OVER( PARTITION BY (ocd.o).agreement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS consumed, > ocd.item_qty, ocd.item_price, ocd.item_cost, ocd.item_suma, > ocd.o, ocd.c, ocd.p, ocd.ic, > (ocd.o).id as order_id, > (ocd.o).agreement_id as agreement_id > FROM order_cost_details( _target_range ) ocd
> Problem is window function, because ID can not go through. But this occur > not always. > When I filter by field I partition result by then optimization occur > BUT only when I create an alias for this field and do filtering via this > alias.
> Expected: apply optimization not only when I do `WHERE agreement_id = XXX`
