From: Eugen Konkov <kes-kes@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: PG Bug reporting form <noreply@postgresql.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
Date: Sat, 15 May 2021 17:34:16 +0300
Message-ID: <1035195294.20210515173416@yandex.ru> (raw)
In-Reply-To: <CAApHDvrayMkhak9eYbhHs67XvkZSOiscxJEER2yECN-k4L91oQ@mail.gmail.com>
References: <16968-9c8a1708c1c04355@postgresql.org>
<846547866.20210416222735@yandex.ru>
<226821618.20210513163513@yandex.ru>
<CAApHDvrayMkhak9eYbhHs67XvkZSOiscxJEER2yECN-k4L91oQ@mail.gmail.com>
Hello David,
I found a case when `not assigning a ressortgroupref to the whole-row var` cause
wrong window function calculations.
I use same query. The difference come when I wrap my query into
function. (see full queries in attachment)
1.
SELECT *
FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) )
WHERE agreement_id = 161::int AND (o).period_id = 10::int
2.
SELECT *
sum( .... ) over wagreement
FROM ....
WHERE agreement_id = 161::int AND (o).period_id = 10::int
WINDOW wagreement AS ( PARTITION BY agreement_id )
For first query window function calculates SUM over all agreements,
then some are filtered out by (o).period_id condition.
But for second query agreements with "wrong" (o).period_id are filtered out,
then SUM is calculated.
I suppose here is problem with `not assigning a ressortgroupref to the whole-row var`
which cause different calculation when I try to filter: (o).period_id
I will also attach plans for both queries.
Friday, May 14, 2021, 2:52:33 AM, you wrote:
> On Fri, 14 May 2021 at 02:38, Eugen Konkov <kes-kes@yandex.ru> wrote:
>> 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.
> You probably already know this part, but let me explain it just in
> case it's not clear.
> The pushdown of the qual from the top-level query into the subquery,
> or function, in this case, is only legal when the qual references a
> column that's in the PARTITION BY clause of all window functions in
> the subquery. The reason for this is, if we filter rows before
> calling the window function, then it could affect which rows are in
> see in the window's frame. If it did filter, that could cause
> incorrect results. We can relax the restriction a bit if we can
> eliminate entire partitions at once. The window function results are
> independent between partitions, so we can allow qual pushdowns that
> are in all PARTITION BY clauses.
> As for the reason you're having trouble getting this to work, it's
> down to the way you're using whole-row vars in your targetlist.
> A slightly simplified case which shows this problem is:
> create table ab(a int, b int);
> explain select * from (select ab as wholerowvar,row_number() over
> (partition by a) from ab) ab where (ab.wholerowvar).a=1;
> The reason it does not work is down to how this is implemented
> internally. The details are, transformGroupClause() not assigning a
> ressortgroupref to the whole-row var. It's unable to because there is
> no way to track which actual column within the whole row var is in the
> partition by clause. When it comes to the code that tries to push the
> qual down into the subquery, check_output_expressions() checks if the
> column in the subquery is ok to accept push downs or not. One of the
> checks is to see if the query has windowing functions and to ensure
> that the column is in all the PARTITION BY clauses of each windowing
> function. That check is done by checking if a ressortgroupref is
> assigned and matches a tleSortGroupRef in the PARTITION BY clause. In
> this case, it does not match. We didn't assign any ressortgroupref to
> the whole-row var.
> Unfortunately, whole-row vars are a bit to 2nd class citizen when it
> comes to the query planner. Also, it would be quite a bit of effort to
> make the planner push down the qual in this case. We'd need some sort
> of ability to assign ressortgroupref to a particular column within a
> whole-row var and we'd need to adjust the code to check for that when
> doing subquery pushdowns to allow it to mention which columns within
> whole-row vars can legally accept pushdowns. I imagine that's
> unlikely to be fixed any time soon. Whole-row vars just don't seem to
> be used commonly enough to warrant going to the effort of making this
> stuff work.
> To work around this, you should include a reference to the actual
> column in the targetlist of the subquery, or your function, in this
> case, and ensure you use that same column in the PARTITION BY clause.
> You'll then need to write that column in your condition that you need
> pushed into the subquery. I'm sorry if that messes up your design.
> However, I imagine this is not the only optimisation that you'll miss
> out on by doing things the way you are.
> David
--
Best regards,
Eugen Konkov
Attachments:full.txt (text/plain, 3.8K) download
SELECT *
FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) )
-- PAY ATTENTION TO THIS
WHERE agreement_id = 161::int AND (o).period_id = 10::int
WITH gconf AS
-- https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-SELECT
-- NOT MATERIALIZED -- force it to be merged into the parent query
-- it gives a net savings because each usage of the WITH query needs only a small part of the WITH query''s full output.
( SELECT
ocd.*,
(ic).consumed_period AS consumed_period,
dense_rank() OVER ( PARTITION BY agreement_id, order_id ORDER BY (ic).consumed_period ) AS nconf,
row_number() OVER ( wconf ORDER BY (c).sort_order NULLS LAST ) AS nitem,
-- Summarize each item of configuration and get configuration cost
(sum( ocd.item_cost ) OVER wconf)::numeric( 10, 2 ) AS conf_cost,
-- For resource `consumed` is not calculated, because there is not period_id for it
-- For ServiceType there is Price which has period_id, but it depends on Order.period_id
-- TODO: Calculate consumption depending on Order.period_id
(max( (ocd.ic).consumed ) OVER wconf) AS consumed,
-- NOTICE: Here we MUST select cost because invoice does not know how much
-- service will be consumed. If we are regenerate past Invoice it MUST NOT
-- take into account changes, like it is just builded
-- Summarize each item of configuration to get configuration suma
CASE WHEN false
THEN (sum( ocd.item_cost ) OVER wconf)::numeric( 10, 2 )
ELSE (sum( ocd.item_suma ) OVER wconf)::numeric( 10, 2 )
END AS conf_suma
FROM order_cost_details( tstzrange( '2020-07-01', '2020-08-01' ) ) ocd
-- Each agreement could have few different orders
-- Each order could have changes within consumed_period
-- So we partition by these three factors:
WINDOW wconf AS ( PARTITION BY agreement_id, order_id, (ic).consumed_period )
),
gorder AS (
SELECT *,
(conf_suma/6)::numeric( 10, 2 ) AS conf_nds,
-- Summarize configuration suma for each consumed_period to get order suma
sum( conf_suma ) FILTER (WHERE nitem = 1) OVER worder AS order_suma
FROM gconf
WINDOW worder AS ( PARTITION BY agreement_id, order_id )
)
SELECT
agreement_id, (o).period_id, order_id, consumed_period, nconf, nitem,
(c).id AS item_id,
COALESCE( (c).sort_order, pd.sort_order ) AS item_order,
COALESCE( st.display, st.name, rt.display, rt.name ) AS item_name,
COALESCE( item_qty, (c).amount/rt.unit ) AS item_qty,
COALESCE( (p).label, rt.label ) AS measure,
item_price, item_cost, item_suma,
conf_cost, consumed, conf_suma, conf_nds, order_suma,
(order_suma/6)::numeric( 10, 2 ) AS order_nds,
-- Summarize configuration suma for each consumed_period and order to get agreement suma
sum( conf_suma ) FILTER (WHERE nitem = 1 ) OVER wagreement AS total_suma,
sum( (order_suma/6)::numeric( 10, 2 ) ) FILTER (WHERE nitem = 1 AND nconf = 1) OVER wagreement AS total_nds,
pkg.id AS package_id,
pkg.link_1c_id AS package_1c_id,
COALESCE( pkg.display, pkg.name ) AS package,
o, c, p, ic
FROM gorder u
LEFT JOIN resource_type rt ON rt.id = (c).resource_type_id
LEFT JOIN service_type st ON st.id = (c).service_type_id
LEFT JOIN package pkg ON pkg.id = (o).package_id
LEFT JOIN package_detail pd ON pd.package_id = (o).package_id
AND pd.resource_type_id IS NOT DISTINCT FROM (c).resource_type_id
AND pd.service_type_id IS NOT DISTINCT FROM (c).service_type_id
WHERE agreement_id = 161 AND (o).period_id = 10 -- PAY ATTENTION TO THIS
WINDOW wagreement AS ( PARTITION BY agreement_id )
direct-plan.txt (text/plain, 124.4K) download
function-plan.txt (text/plain, 122.5K) download
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-bugs@postgresql.org
Cc: kes-kes@yandex.ru, dgrowleyml@gmail.com, noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pgsql-performance@postgresql.org, ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
In-Reply-To: <1035195294.20210515173416@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