BUG #16968: Planner does not recognize optimization

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #16968: Planner does not recognize optimization
Дата
Msg-id 16968-9c8a1708c1c04355@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #16968: Planner does not recognize optimization  (Eugen Konkov <kes-kes@yandex.ru>)
Список pgsql-bugs
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`

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

Thank you.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #16967: Extremely slow update statement in trigger
Следующее
От: Eugen Konkov
Дата:
Сообщение: Re: BUG #16968: Planner does not recognize optimization