Re: BUG #16968: Planner does not recognize optimization

Now I attarch plans for both queries.

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(at)yandex(dot)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`

> but and for `WHERE (ots.o).agreement_id = XXX`

> Thank you.

--
Best regards,
Eugen Konkov