Re: BUG #16968: Planner does not recognize optimization

Поиск
Список
Период
Сортировка
От Eugen Konkov
Тема Re: BUG #16968: Planner does not recognize optimization
Дата
Msg-id 226821618.20210513163513@yandex.ru
обсуждение исходный текст
Ответ на Re: BUG #16968: Planner does not recognize optimization  (Eugen Konkov <kes-kes@yandex.ru>)
Ответы Re: BUG #16968: Planner does not recognize optimization  (David Rowley <dgrowleyml@gmail.com>)
Re: BUG #16968: Planner does not recognize optimization  (David Rowley <dgrowleyml@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Now I create minimal reproducible test case.
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=761a00fb599789d3db31b120851d6341

Optimization is not applyed when I filter/partition by column using composite type name.

Looking at this comparison table, we can see that optimization work only when I refer to column using alias:

    (t.ag).ag_id as agreement_id  -- making an alias

PARTITION | FILTER | IS USED?
------------------------------
ALIAS     | ORIG   | NO
ALIAS     | ALIAS  | YES
ORIG      | ALIAS  | NO
ORIG      | ORIG   | NO

link to original problem with EXPLAIN ANALYZE: https://stackoverflow.com/q/67492673/4632019
Links to similar problems:
https://stackoverflow.com/a/26237464/4632019
https://stackoverflow.com/q/65780112/4632019


Friday, April 16, 2021, 10:27:35 PM, you wrote:


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@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.




--
Best regards,
Eugen Konkov



--
Best regards,
Eugen Konkov

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

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #17006: Process watcher window doesnt appear
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #17007: server process (PID XXXX) was terminated by signal 11: Segmentation fault