From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | kes-kes(at)yandex(dot)ru |
Subject: | BUG #16968: Planner does not recognize optimization |
Date: | 2021-04-16 19:18:45 |
Message-ID: | 16968-9c8a1708c1c04355@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Eugen Konkov | 2021-04-16 19:27:35 | Re: BUG #16968: Planner does not recognize optimization |
Previous Message | PG Bug reporting form | 2021-04-16 16:52:15 | BUG #16967: Extremely slow update statement in trigger |
From | Date | Subject | |
---|---|---|---|
Next Message | Eugen Konkov | 2021-04-16 19:27:35 | Re: BUG #16968: Planner does not recognize optimization |
Previous Message | Tomas Vondra | 2021-04-16 17:59:04 | Re: Disabling options lowers the estimated cost of a query |