From: Eugen Konkov <kes-kes@yandex.ru>
To: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pgsql-performance@postgresql.org
Cc: ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
Date: Thu, 13 May 2021 16:35:13 +0300
Message-ID: <226821618.20210513163513@yandex.ru> (raw)
In-Reply-To: <846547866.20210416222735@yandex.ru>
References: <16968-9c8a1708c1c04355@postgresql.org>
<846547866.20210416222735@yandex.ru>
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/4632019https://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kes-kes@yandex.ru, noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
In-Reply-To: <226821618.20210513163513@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox