pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: BUG #16968: Planner does not recognize optimization
8+ messages / 3 participants
[nested] [flat]
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-13 13:35 Eugen Konkov <kes-kes@yandex.ru>
0 siblings, 1 reply; 8+ messages in thread
From: Eugen Konkov @ 2021-05-13 13:35 UTC (permalink / raw)
To: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs@lists.postgresql.org; +Cc: pgsql-performance; ladayaroslav@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/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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-13 23:52 David Rowley <dgrowleyml@gmail.com>
parent: Eugen Konkov <kes-kes@yandex.ru>
0 siblings, 2 replies; 8+ messages in thread
From: David Rowley @ 2021-05-13 23:52 UTC (permalink / raw)
To: Eugen Konkov <kes-kes@yandex.ru>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-14 12:39 KES <kes-kes@yandex.ru>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: KES @ 2021-05-14 12:39 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru <ladayaroslav@yandex.ru>
<div>Thank you for detailed explanation. I glad to hear that I can use aliases and this will be recognized and optimization is applied.</div><div> </div><div>>We'd need some sort of ability to assign ressortgroupref to a particular column within a<div>whole-row var</div><div>Could it be possible to create hidden alias in same way as I did that manually?</div><div> </div><div>Algorithm seems not complex:</div><div>1. User refer column from composite type/whole-row: (o).agreement_id</div><div>2. Create hidden column at select: _o_agreement_id</div><div>3. Replace other references to (o).agreement_id by _o_agreement_id</div><div>4. Process query as usual after replacements</div><div> </div></div><div> </div><div>14.05.2021, 02:52, "David Rowley" <dgrowleyml@gmail.com>:</div><blockquote><p>On Fri, 14 May 2021 at 02:38, Eugen Konkov <<a href="mailto:kes-kes@yandex.ru" rel="noopener noreferrer">kes-kes@yandex.ru</a>> wrote:</p><blockquote> Now I create minimal reproducible test case.<br /> <a href="https://dbfiddle.uk/?rdbms=postgres_13&fiddle=761a00fb599789d3db31b120851d6341"; rel="noopener noreferrer">https://dbfiddle.uk/?rdbms=postgres_13&fiddle=761a00fb599789d3db31b120851d6341</a><... /><br /> Optimization is not applyed when I filter/partition by column using composite type name.</blockquote><p><br />You probably already know this part, but let me explain it just in<br />case it's not clear.<br /><br />The pushdown of the qual from the top-level query into the subquery,<br />or function, in this case, is only legal when the qual references a<br />column that's in the PARTITION BY clause of all window functions in<br />the subquery. The reason for this is, if we filter rows before<br />calling the window function, then it could affect which rows are in<br />see in the window's frame. If it did filter, that could cause<br />incorrect results. We can relax the restriction a bit if we can<br />eliminate entire partitions at once. The window function results are<br />independent between partitions, so we can allow qual pushdowns that<br />are in all PARTITION BY clauses.<br /><br />As for the reason you're having trouble getting this to work, it's<br />down to the way you're using whole-row vars in your targetlist.<br /><br />A slightly simplified case which shows this problem is:<br /><br />create table ab(a int, b int);<br />explain select * from (select ab as wholerowvar,row_number() over<br />(partition by a) from ab) ab where (ab.wholerowvar).a=1;<br /><br />The reason it does not work is down to how this is implemented<br />internally. The details are, transformGroupClause() not assigning a<br />ressortgroupref to the whole-row var. It's unable to because there is<br />no way to track which actual column within the whole row var is in the<br />partition by clause. When it comes to the code that tries to push the<br />qual down into the subquery, check_output_expressions() checks if the<br />column in the subquery is ok to accept push downs or not. One of the<br />checks is to see if the query has windowing functions and to ensure<br />that the column is in all the PARTITION BY clauses of each windowing<br />function. That check is done by checking if a ressortgroupref is<br />assigned and matches a tleSortGroupRef in the PARTITION BY clause. In<br />this case, it does not match. We didn't assign any ressortgroupref to<br />the whole-row var.<br /><br />Unfortunately, whole-row vars are a bit to 2nd class citizen when it<br />comes to the query planner. Also, it would be quite a bit of effort to<br />make the planner push down the qual in this case. We'd need some sort<br />of ability to assign ressortgroupref to a particular column within a<br />whole-row var and we'd need to adjust the code to check for that when<br />doing subquery pushdowns to allow it to mention which columns within<br />whole-row vars can legally accept pushdowns. I imagine that's<br />unlikely to be fixed any time soon. Whole-row vars just don't seem to<br />be used commonly enough to warrant going to the effort of making this<br />stuff work.<br /><br />To work around this, you should include a reference to the actual<br />column in the targetlist of the subquery, or your function, in this<br />case, and ensure you use that same column in the PARTITION BY clause.<br />You'll then need to write that column in your condition that you need<br />pushed into the subquery. I'm sorry if that messes up your design.<br />However, I imagine this is not the only optimisation that you'll miss<br />out on by doing things the way you are.<br /><br />David</p></blockquote>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-15 14:34 Eugen Konkov <kes-kes@yandex.ru>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Eugen Konkov @ 2021-05-15 14:34 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru
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
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": {
"Node Type": "WindowAgg",
"Parallel Aware": false,
"Startup Cost": 377.72,
"Total Cost": 418.86,
"Plan Rows": 5,
"Plan Width": 983,
"Actual Startup Time": 8.823,
"Actual Total Time": 8.846,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "(u.o).period_id", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "(u.c).id", "COALESCE((u.c).sort_order, pd.sort_order)", "COALESCE(st.display, (st.name)::text, (rt.display)::text, (rt.name)::text)", "COALESCE(u.item_qty, ((u.c).amount / (rt.unit)::double precision))", "COALESCE((u.p).label, (rt.label)::text)", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "((u.order_suma / '6'::numeric))::numeric(10,2)", "sum(u.conf_suma) FILTER (WHERE (u.nitem = 1)) OVER (?)", "sum(((u.order_suma / '6'::numeric))::numeric(10,2)) FILTER (WHERE ((u.nitem = 1) AND (u.nconf = 1))) OVER (?)", "pkg.id", "pkg.link_1c_id", "COALESCE(pkg.display, pkg.name)", "u.o", "u.c", "u.p", "u.ic"],
"Shared Hit Blocks": 118,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 377.72,
"Total Cost": 418.66,
"Plan Rows": 5,
"Plan Width": 863,
"Actual Startup Time": 8.157,
"Actual Total Time": 8.811,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "pd.sort_order", "st.display", "st.name", "rt.display", "rt.name", "u.item_qty", "rt.unit", "u.p", "rt.label", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name", "u.ic"],
"Inner Unique": true,
"Hash Cond": "((u.o).package_id = pkg.id)",
"Shared Hit Blocks": 118,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 362.31,
"Total Cost": 403.24,
"Plan Rows": 5,
"Plan Width": 709,
"Actual Startup Time": 7.898,
"Actual Total Time": 8.549,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "u.item_qty", "u.p", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "u.ic", "rt.display", "rt.name", "rt.unit", "rt.label", "st.display", "st.name", "pd.sort_order"],
"Inner Unique": true,
"Hash Cond": "((u.c).service_type_id = st.id)",
"Shared Hit Blocks": 110,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 355.35,
"Total Cost": 396.27,
"Plan Rows": 5,
"Plan Width": 624,
"Actual Startup Time": 7.763,
"Actual Total Time": 8.412,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "u.item_qty", "u.p", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "u.ic", "rt.display", "rt.name", "rt.unit", "rt.label", "pd.sort_order"],
"Inner Unique": false,
"Hash Cond": "((u.c).resource_type_id = rt.id)",
"Shared Hit Blocks": 107,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 347.90,
"Total Cost": 388.75,
"Plan Rows": 5,
"Plan Width": 552,
"Actual Startup Time": 7.599,
"Actual Total Time": 8.243,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "u.item_qty", "u.p", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "u.ic", "pd.sort_order"],
"Inner Unique": false,
"Hash Cond": "(pd.package_id = (u.o).package_id)",
"Join Filter": "((NOT (pd.resource_type_id IS DISTINCT FROM (u.c).resource_type_id)) AND (NOT (pd.service_type_id IS DISTINCT FROM (u.c).service_type_id)))",
"Rows Removed by Join Filter": 9,
"Shared Hit Blocks": 104,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "package_detail",
"Schema": "public",
"Alias": "pd",
"Startup Cost": 0.00,
"Total Cost": 33.20,
"Plan Rows": 1920,
"Plan Width": 16,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.274,
"Actual Rows": 1920,
"Actual Loops": 1,
"Output": ["pd.id", "pd.package_id", "pd.resource_type_id", "pd.service_type_id", "pd.quantity", "pd.parent_id", "pd.sort_order", "pd.hide"],
"Shared Hit Blocks": 14,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 347.84,
"Total Cost": 347.84,
"Plan Rows": 5,
"Plan Width": 548,
"Actual Startup Time": 7.498,
"Actual Total Time": 7.513,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "u.item_qty", "u.p", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "u.ic"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 10,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "u",
"Startup Cost": 220.34,
"Total Cost": 347.84,
"Plan Rows": 5,
"Plan Width": 548,
"Actual Startup Time": 7.481,
"Actual Total Time": 7.505,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["u.agreement_id", "u.o", "u.order_id", "u.consumed_period", "u.nconf", "u.nitem", "u.c", "u.item_qty", "u.p", "u.item_price", "u.item_cost", "u.item_suma", "u.conf_cost", "u.consumed", "u.conf_suma", "u.conf_nds", "u.order_suma", "u.ic"],
"Filter": "((u.o).period_id = 10)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 335.34,
"Plan Rows": 1000,
"Plan Width": 548,
"Actual Startup Time": 7.476,
"Actual Total Time": 7.498,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.item_qty", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.o", "gconf.c", "gconf.p", "gconf.ic", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "((gconf.conf_suma / '6'::numeric))::numeric(10,2)", "sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "gconf",
"Startup Cost": 220.34,
"Total Cost": 310.34,
"Plan Rows": 1000,
"Plan Width": 500,
"Actual Startup Time": 7.441,
"Actual Total Time": 7.473,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.item_qty", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.o", "gconf.c", "gconf.p", "gconf.ic", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 300.34,
"Plan Rows": 1000,
"Plan Width": 504,
"Actual Startup Time": 7.439,
"Actual Total Time": 7.469,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed_period", "dense_rank() OVER (?)", "(row_number() OVER (?))", "((sum(cis.item_cost) OVER (?)))::numeric(10,2)", "(max(ic.consumed) OVER (?))", "((sum(cis.item_suma) OVER (?)))::numeric(10,2)", "od.sort_order"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 270.34,
"Plan Rows": 1000,
"Plan Width": 488,
"Actual Startup Time": 7.407,
"Actual Total Time": 7.424,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed", "(row_number() OVER (?))", "sum(cis.item_cost) OVER (?)", "max(ic.consumed) OVER (?)", "sum(cis.item_suma) OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 245.34,
"Plan Rows": 1000,
"Plan Width": 464,
"Actual Startup Time": 7.389,
"Actual Total Time": 7.405,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed", "row_number() OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 222.84,
"Plan Rows": 1000,
"Plan Width": 456,
"Actual Startup Time": 7.377,
"Actual Total Time": 7.390,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed"],
"Sort Key": ["o.id", "ic.consumed_period", "od.sort_order"],
"Sort Method": "quicksort",
"Sort Space Used": 27,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 59.92,
"Total Cost": 170.51,
"Plan Rows": 1000,
"Plan Width": 456,
"Actual Startup Time": 2.936,
"Actual Total Time": 7.370,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed"],
"Inner Unique": false,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 59.67,
"Total Cost": 150.26,
"Plan Rows": 1,
"Plan Width": 424,
"Actual Startup Time": 2.858,
"Actual Total Time": 7.169,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "od.*", "od.sort_order", "sp.*", "ic.*", "ic.consumed_period", "ic.consumed"],
"Inner Unique": false,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 57.12,
"Total Cost": 139.12,
"Plan Rows": 1,
"Plan Width": 394,
"Actual Startup Time": 2.068,
"Actual Total Time": 5.638,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.app_period", "split_period.split_period", "od.*", "od.sort_order", "od.app_period", "sp.*"],
"Inner Unique": false,
"Join Filter": "(od.service_type_id = sp.service_type_id)",
"Rows Removed by Join Filter": 192,
"Shared Hit Blocks": 78,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 56.36,
"Total Cost": 125.80,
"Plan Rows": 1,
"Plan Width": 374,
"Actual Startup Time": 0.514,
"Actual Total Time": 1.448,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id", "split_period.split_period", "od.*", "od.sort_order", "od.app_period", "od.service_type_id"],
"Inner Unique": false,
"Join Filter": "((od.app_period * o.app_period) && split_period.split_period)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 63,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 52.00,
"Total Cost": 83.14,
"Plan Rows": 1,
"Plan Width": 235,
"Actual Startup Time": 0.465,
"Actual Total Time": 1.311,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id", "split_period.split_period"],
"Inner Unique": false,
"Shared Hit Blocks": 56,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "order_bt",
"Schema": "public",
"Alias": "o",
"Startup Cost": 4.35,
"Total Cost": 35.17,
"Plan Rows": 1,
"Plan Width": 203,
"Actual Startup Time": 0.276,
"Actual Total Time": 0.401,
"Actual Rows": 5,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id"],
"Recheck Cond": "(o.agreement_id = 161)",
"Rows Removed by Index Recheck": 0,
"Filter": "(o.sys_period @> sys_time())",
"Rows Removed by Filter": 18,
"Exact Heap Blocks": 18,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 20,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_idx_agreement_id",
"Startup Cost": 0.00,
"Total Cost": 4.35,
"Plan Rows": 8,
"Plan Width": 0,
"Actual Startup Time": 0.026,
"Actual Total Time": 0.026,
"Actual Rows": 23,
"Actual Loops": 1,
"Index Cond": "(o.agreement_id = 161)",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Alias": "split_period",
"Startup Cost": 47.65,
"Total Cost": 47.96,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.171,
"Actual Total Time": 0.179,
"Actual Rows": 0,
"Actual Loops": 5,
"Output": ["split_period.split_period"],
"Filter": "((o.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange) && split_period.split_period)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Alias": "ranges",
"Startup Cost": 47.65,
"Total Cost": 47.87,
"Plan Rows": 6,
"Plan Width": 32,
"Actual Startup Time": 0.168,
"Actual Total Time": 0.176,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["tstzrange(ranges.start, COALESCE(ranges.finish, '2020-08-01 00:00:00+03'::timestamp with time zone), CASE WHEN (ranges.finish IS NULL) THEN '[]'::text ELSE '[)'::text END)"],
"Filter": "(ranges.finish IS NOT NULL)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE periods",
"Parallel Aware": false,
"Startup Cost": 47.24,
"Total Cost": 47.27,
"Plan Rows": 3,
"Plan Width": 32,
"Actual Startup Time": 0.138,
"Actual Total Time": 0.139,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["((o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))"],
"Group Key": ["((o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 24,
"Disk Usage": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.53,
"Total Cost": 47.23,
"Plan Rows": 3,
"Plan Width": 32,
"Actual Startup Time": 0.068,
"Actual Total Time": 0.130,
"Actual Rows": 2,
"Actual Loops": 5,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Scan Direction": "NoMovement",
"Index Name": "order_id_sys_period_app_period_excl",
"Relation Name": "order_bt",
"Schema": "public",
"Alias": "o_1",
"Startup Cost": 0.53,
"Total Cost": 4.55,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.054,
"Actual Total Time": 0.054,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["(o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange)"],
"Index Cond": "((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 19,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Relation Name": "order_detail_bt",
"Schema": "public",
"Alias": "od_1",
"Startup Cost": 4.36,
"Total Cost": 42.64,
"Plan Rows": 2,
"Plan Width": 32,
"Actual Startup Time": 0.046,
"Actual Total Time": 0.055,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["(od_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange)"],
"Recheck Cond": "(od_1.order_id = $0)",
"Rows Removed by Index Recheck": 0,
"Filter": "((od_1.app_period && '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange) AND (od_1.sys_period @> sys_time()))",
"Rows Removed by Filter": 1,
"Exact Heap Blocks": 7,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 17,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_detail_idx_order_id",
"Startup Cost": 0.00,
"Total Cost": 4.36,
"Plan Rows": 10,
"Plan Width": 0,
"Actual Startup Time": 0.012,
"Actual Total Time": 0.012,
"Actual Rows": 3,
"Actual Loops": 5,
"Index Cond": "(od_1.order_id = $0)",
"Shared Hit Blocks": 10,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
},
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 0.38,
"Total Cost": 0.53,
"Plan Rows": 6,
"Plan Width": 16,
"Actual Startup Time": 0.165,
"Actual Total Time": 0.168,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))", "lead((lower(periods.app_period))) OVER (?)"],
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.38,
"Total Cost": 0.39,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.159,
"Actual Total Time": 0.159,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))"],
"Sort Key": ["(lower(periods.app_period))"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.24,
"Total Cost": 0.30,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.150,
"Actual Total Time": 0.151,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))"],
"Group Key": ["(lower(periods.app_period))"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 24,
"Disk Usage": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 0.23,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.143,
"Actual Total Time": 0.146,
"Actual Rows": 2,
"Actual Loops": 5,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"CTE Name": "periods",
"Alias": "periods",
"Startup Cost": 0.00,
"Total Cost": 0.07,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 0.142,
"Actual Total Time": 0.142,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["lower(periods.app_period)"],
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"CTE Name": "periods",
"Alias": "periods_1",
"Startup Cost": 0.00,
"Total Cost": 0.07,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["upper(periods_1.app_period)"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "order_detail_bt",
"Schema": "public",
"Alias": "od",
"Startup Cost": 4.36,
"Total Cost": 42.61,
"Plan Rows": 3,
"Plan Width": 143,
"Actual Startup Time": 0.040,
"Actual Total Time": 0.059,
"Actual Rows": 2,
"Actual Loops": 2,
"Output": ["od.*", "od.sort_order", "od.app_period", "od.order_id", "od.service_type_id"],
"Recheck Cond": "(od.order_id = o.id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(od.sys_period @> sys_time())",
"Rows Removed by Filter": 2,
"Exact Heap Blocks": 3,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 7,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_detail_idx_order_id",
"Startup Cost": 0.00,
"Total Cost": 4.36,
"Plan Rows": 10,
"Plan Width": 0,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 3,
"Actual Loops": 2,
"Index Cond": "(od.order_id = o.id)",
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Function Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Function Name": "service_level_price",
"Schema": "public",
"Alias": "sp",
"Startup Cost": 0.76,
"Total Cost": 13.26,
"Plan Rows": 5,
"Plan Width": 40,
"Actual Startup Time": 1.327,
"Actual Total Time": 1.381,
"Actual Rows": 65,
"Actual Loops": 3,
"Output": ["sp.*", "sp.service_type_id", "sp.period_id"],
"Function Call": "service_level_price(o.service_level_id, CASE WHEN (app_time() IS NOT NULL) THEN tstzrange(app_time(), NULL::timestamp with time zone) ELSE (NULLIF(current_setting('my.app_period'::text, true), ''::text))::tstzrange END)",
"Filter": "(o.period_id = sp.period_id)",
"Rows Removed by Filter": 46,
"Shared Hit Blocks": 15,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Alias": "ic",
"Startup Cost": 2.55,
"Total Cost": 11.12,
"Plan Rows": 1,
"Plan Width": 104,
"Actual Startup Time": 0.501,
"Actual Total Time": 0.506,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["ic.*", "ic.consumed_period", "ic.consumed"],
"Shared Hit Blocks": 12,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 2.55,
"Total Cost": 11.11,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 0.496,
"Actual Total Time": 0.500,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["ROW(cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.app_period)::currency_rate", "interval_length(((od.app_period * o.app_period) * split_period.split_period), $3, '2020-08-01 00:00:00+03'::timestamp with time zone, $4)", "COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))"],
"One-Time Filter": "(NOT isempty(((od.app_period * o.app_period) * split_period.split_period)))",
"Shared Hit Blocks": 12,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 2 (returns $3)",
"Parallel Aware": false,
"Relation Name": "period",
"Schema": "public",
"Alias": "period",
"Startup Cost": 0.00,
"Total Cost": 1.07,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.010,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["period.\"interval\""],
"Filter": "(period.id = ($2).period_id)",
"Rows Removed by Filter": 5,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 3 (returns $4)",
"Parallel Aware": false,
"Relation Name": "period",
"Schema": "public",
"Alias": "period_1",
"Startup Cost": 0.00,
"Total Cost": 1.07,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.005,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["period_1.trunc_interval"],
"Filter": "(period_1.id = ($2).period_id)",
"Rows Removed by Filter": 5,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 0.40,
"Total Cost": 8.70,
"Plan Rows": 1,
"Plan Width": 45,
"Actual Startup Time": 0.103,
"Actual Total Time": 0.106,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cr.id", "cr.rate", "cr.docdate", "cr.from_currency_id", "cr.to_currency_id", "cr.app_period"],
"Inner Unique": false,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 1,
"Actual Loops": 3,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "NoMovement",
"Index Name": "currency_rate_bt_id_sys_period_app_period_excl",
"Relation Name": "currency_rate_bt",
"Schema": "public",
"Alias": "cr",
"Startup Cost": 0.40,
"Total Cost": 8.67,
"Plan Rows": 1,
"Plan Width": 45,
"Actual Startup Time": 0.064,
"Actual Total Time": 0.066,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cr.id", "cr.rate", "cr.docdate", "cr.from_currency_id", "cr.to_currency_id", "cr.sid", "cr.app_period", "cr.sys_period"],
"Index Cond": "((cr.sys_period @> sys_time()) AND (cr.app_period @> lower(((od.app_period * o.app_period) * split_period.split_period))))",
"Rows Removed by Index Recheck": 0,
"Filter": "((cr.from_currency_id = (sp.*).currency_id) AND (cr.to_currency_id = app_currency()))",
"Rows Removed by Filter": 3,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Function Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Function Name": "calc_item_suma",
"Schema": "public",
"Alias": "cis",
"Startup Cost": 0.25,
"Total Cost": 10.25,
"Plan Rows": 1000,
"Plan Width": 32,
"Actual Startup Time": 0.061,
"Actual Total Time": 0.061,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma"],
"Function Call": "calc_item_suma(o.*, od.*, sp.*, ic.*)",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 4.98,
"Total Cost": 4.98,
"Plan Rows": 198,
"Plan Width": 76,
"Actual Startup Time": 0.152,
"Actual Total Time": 0.152,
"Actual Rows": 198,
"Actual Loops": 1,
"Output": ["rt.display", "rt.name", "rt.unit", "rt.label", "rt.id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 24,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "resource_type",
"Schema": "public",
"Alias": "rt",
"Startup Cost": 0.00,
"Total Cost": 4.98,
"Plan Rows": 198,
"Plan Width": 76,
"Actual Startup Time": 0.013,
"Actual Total Time": 0.082,
"Actual Rows": 198,
"Actual Loops": 1,
"Output": ["rt.display", "rt.name", "rt.unit", "rt.label", "rt.id"],
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 4.76,
"Total Cost": 4.76,
"Plan Rows": 176,
"Plan Width": 89,
"Actual Startup Time": 0.124,
"Actual Total Time": 0.124,
"Actual Rows": 176,
"Actual Loops": 1,
"Output": ["st.display", "st.name", "st.id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 30,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "service_type",
"Schema": "public",
"Alias": "st",
"Startup Cost": 0.00,
"Total Cost": 4.76,
"Plan Rows": 176,
"Plan Width": 89,
"Actual Startup Time": 0.012,
"Actual Total Time": 0.063,
"Actual Rows": 176,
"Actual Loops": 1,
"Output": ["st.display", "st.name", "st.id"],
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 11.29,
"Total Cost": 11.29,
"Plan Rows": 329,
"Plan Width": 154,
"Actual Startup Time": 0.246,
"Actual Total Time": 0.246,
"Actual Rows": 329,
"Actual Loops": 1,
"Output": ["pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 68,
"Shared Hit Blocks": 8,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "package",
"Schema": "public",
"Alias": "pkg",
"Startup Cost": 0.00,
"Total Cost": 11.29,
"Plan Rows": 329,
"Plan Width": 154,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.129,
"Actual Rows": 329,
"Actual Loops": 1,
"Output": ["pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name"],
"Shared Hit Blocks": 8,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
},
"Planning": {
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 5.412,
"Triggers": [
],
"Execution Time": 9.374
}
]
via function
[
{
"Plan": {
"Node Type": "Subquery Scan",
"Parallel Aware": false,
"Alias": "agreement_totals",
"Startup Cost": 307.35,
"Total Cost": 720.23,
"Plan Rows": 5,
"Plan Width": 776,
"Actual Startup Time": 9.102,
"Actual Total Time": 9.138,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["agreement_totals.agreement_id", "agreement_totals.order_id", "agreement_totals.consumed_period", "agreement_totals.nconf", "agreement_totals.nitem", "agreement_totals.item_id", "agreement_totals.item_order", "agreement_totals.item_name", "agreement_totals.item_qty", "agreement_totals.measure", "agreement_totals.item_price", "agreement_totals.item_cost", "agreement_totals.item_suma", "agreement_totals.conf_cost", "agreement_totals.consumed", "agreement_totals.conf_suma", "agreement_totals.conf_nds", "agreement_totals.order_suma", "agreement_totals.order_nds", "agreement_totals.total_suma", "agreement_totals.total_nds", "agreement_totals.package_id", "agreement_totals.package_1c_id", "agreement_totals.package", "agreement_totals.o", "agreement_totals.c", "agreement_totals.p", "agreement_totals.ic"],
"Filter": "((agreement_totals.o).period_id = 10)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 118,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 307.35,
"Total Cost": 707.73,
"Plan Rows": 1000,
"Plan Width": 760,
"Actual Startup Time": 9.099,
"Actual Total Time": 9.133,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "(gconf.c).id", "COALESCE((gconf.c).sort_order, pd.sort_order)", "COALESCE(st.display, (st.name)::text, (rt.display)::text, (rt.name)::text)", "COALESCE(gconf.item_qty, ((gconf.c).amount / (rt.unit)::double precision))", "COALESCE((gconf.p).label, (rt.label)::text)", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "(((gconf.conf_suma / '6'::numeric))::numeric(10,2))", "(sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?))", "(((sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?)) / '6'::numeric))::numeric(10,2)", "sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?)", "sum((((sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?)) / '6'::numeric))::numeric(10,2)) FILTER (WHERE ((gconf.nitem = 1) AND (gconf.nconf = 1))) OVER (?)", "pkg.id", "pkg.link_1c_id", "COALESCE(pkg.display, pkg.name)", "gconf.o", "gconf.c", "gconf.p", "gconf.ic"],
"Shared Hit Blocks": 118,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 307.35,
"Total Cost": 662.73,
"Plan Rows": 1000,
"Plan Width": 863,
"Actual Startup Time": 9.015,
"Actual Total Time": 9.088,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.c", "pd.sort_order", "st.display", "st.name", "rt.display", "rt.name", "gconf.item_qty", "rt.unit", "gconf.p", "rt.label", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "(((gconf.conf_suma / '6'::numeric))::numeric(10,2))", "(sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?))", "pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name", "gconf.o", "gconf.ic"],
"Inner Unique": false,
"Hash Cond": "((gconf.o).package_id = pd.package_id)",
"Join Filter": "((NOT (pd.resource_type_id IS DISTINCT FROM (gconf.c).resource_type_id)) AND (NOT (pd.service_type_id IS DISTINCT FROM (gconf.c).service_type_id)))",
"Rows Removed by Join Filter": 26,
"Shared Hit Blocks": 118,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 250.15,
"Total Cost": 394.14,
"Plan Rows": 1000,
"Plan Width": 859,
"Actual Startup Time": 7.914,
"Actual Total Time": 7.973,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.c", "gconf.item_qty", "gconf.p", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "(((gconf.conf_suma / '6'::numeric))::numeric(10,2))", "(sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?))", "gconf.o", "gconf.ic", "rt.display", "rt.name", "rt.unit", "rt.label", "st.display", "st.name", "pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name"],
"Inner Unique": true,
"Hash Cond": "((gconf.o).package_id = pkg.id)",
"Shared Hit Blocks": 104,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 234.75,
"Total Cost": 376.08,
"Plan Rows": 1000,
"Plan Width": 705,
"Actual Startup Time": 7.681,
"Actual Total Time": 7.734,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.c", "gconf.item_qty", "gconf.p", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "(((gconf.conf_suma / '6'::numeric))::numeric(10,2))", "(sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?))", "gconf.o", "gconf.ic", "rt.display", "rt.name", "rt.unit", "rt.label", "st.display", "st.name"],
"Inner Unique": true,
"Hash Cond": "((gconf.c).service_type_id = st.id)",
"Shared Hit Blocks": 96,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 227.79,
"Total Cost": 366.44,
"Plan Rows": 1000,
"Plan Width": 620,
"Actual Startup Time": 7.556,
"Actual Total Time": 7.601,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.c", "gconf.item_qty", "gconf.p", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "(((gconf.conf_suma / '6'::numeric))::numeric(10,2))", "(sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?))", "gconf.o", "gconf.ic", "rt.display", "rt.name", "rt.unit", "rt.label"],
"Inner Unique": false,
"Hash Cond": "((gconf.c).resource_type_id = rt.id)",
"Shared Hit Blocks": 93,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 335.34,
"Plan Rows": 1000,
"Plan Width": 548,
"Actual Startup Time": 7.411,
"Actual Total Time": 7.452,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.item_qty", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.o", "gconf.c", "gconf.p", "gconf.ic", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma", "((gconf.conf_suma / '6'::numeric))::numeric(10,2)", "sum(gconf.conf_suma) FILTER (WHERE (gconf.nitem = 1)) OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "gconf",
"Startup Cost": 220.34,
"Total Cost": 310.34,
"Plan Rows": 1000,
"Plan Width": 500,
"Actual Startup Time": 7.380,
"Actual Total Time": 7.418,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["gconf.agreement_id", "gconf.order_id", "gconf.item_qty", "gconf.item_price", "gconf.item_cost", "gconf.item_suma", "gconf.o", "gconf.c", "gconf.p", "gconf.ic", "gconf.consumed_period", "gconf.nconf", "gconf.nitem", "gconf.conf_cost", "gconf.consumed", "gconf.conf_suma"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 300.34,
"Plan Rows": 1000,
"Plan Width": 504,
"Actual Startup Time": 7.378,
"Actual Total Time": 7.414,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed_period", "dense_rank() OVER (?)", "(row_number() OVER (?))", "((sum(cis.item_cost) OVER (?)))::numeric(10,2)", "(max(ic.consumed) OVER (?))", "((sum(cis.item_suma) OVER (?)))::numeric(10,2)", "od.sort_order"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 270.34,
"Plan Rows": 1000,
"Plan Width": 488,
"Actual Startup Time": 7.347,
"Actual Total Time": 7.372,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed", "(row_number() OVER (?))", "sum(cis.item_cost) OVER (?)", "max(ic.consumed) OVER (?)", "sum(cis.item_suma) OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 245.34,
"Plan Rows": 1000,
"Plan Width": 464,
"Actual Startup Time": 7.330,
"Actual Total Time": 7.353,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed", "row_number() OVER (?)"],
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 220.34,
"Total Cost": 222.84,
"Plan Rows": 1000,
"Plan Width": 456,
"Actual Startup Time": 7.319,
"Actual Total Time": 7.338,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed"],
"Sort Key": ["o.id", "ic.consumed_period", "od.sort_order"],
"Sort Method": "quicksort",
"Sort Space Used": 27,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 59.92,
"Total Cost": 170.51,
"Plan Rows": 1000,
"Plan Width": 456,
"Actual Startup Time": 2.862,
"Actual Total Time": 7.318,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "ic.consumed_period", "od.sort_order", "cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma", "o.*", "od.*", "sp.*", "ic.*", "ic.consumed"],
"Inner Unique": false,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 59.67,
"Total Cost": 150.26,
"Plan Rows": 1,
"Plan Width": 424,
"Actual Startup Time": 2.771,
"Actual Total Time": 7.104,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "od.*", "od.sort_order", "sp.*", "ic.*", "ic.consumed_period", "ic.consumed"],
"Inner Unique": false,
"Shared Hit Blocks": 90,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 57.12,
"Total Cost": 139.12,
"Plan Rows": 1,
"Plan Width": 394,
"Actual Startup Time": 2.058,
"Actual Total Time": 5.716,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.app_period", "split_period.split_period", "od.*", "od.sort_order", "od.app_period", "sp.*"],
"Inner Unique": false,
"Join Filter": "(od.service_type_id = sp.service_type_id)",
"Rows Removed by Join Filter": 192,
"Shared Hit Blocks": 78,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 56.36,
"Total Cost": 125.80,
"Plan Rows": 1,
"Plan Width": 374,
"Actual Startup Time": 0.517,
"Actual Total Time": 1.463,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id", "split_period.split_period", "od.*", "od.sort_order", "od.app_period", "od.service_type_id"],
"Inner Unique": false,
"Join Filter": "((od.app_period * o.app_period) && split_period.split_period)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 63,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 52.00,
"Total Cost": 83.14,
"Plan Rows": 1,
"Plan Width": 235,
"Actual Startup Time": 0.462,
"Actual Total Time": 1.308,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id", "split_period.split_period"],
"Inner Unique": false,
"Shared Hit Blocks": 56,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "order_bt",
"Schema": "public",
"Alias": "o",
"Startup Cost": 4.35,
"Total Cost": 35.17,
"Plan Rows": 1,
"Plan Width": 203,
"Actual Startup Time": 0.263,
"Actual Total Time": 0.391,
"Actual Rows": 5,
"Actual Loops": 1,
"Output": ["o.agreement_id", "o.id", "o.*", "o.service_level_id", "o.app_period", "o.period_id"],
"Recheck Cond": "(o.agreement_id = 161)",
"Rows Removed by Index Recheck": 0,
"Filter": "(o.sys_period @> sys_time())",
"Rows Removed by Filter": 18,
"Exact Heap Blocks": 18,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 20,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_idx_agreement_id",
"Startup Cost": 0.00,
"Total Cost": 4.35,
"Plan Rows": 8,
"Plan Width": 0,
"Actual Startup Time": 0.022,
"Actual Total Time": 0.022,
"Actual Rows": 23,
"Actual Loops": 1,
"Index Cond": "(o.agreement_id = 161)",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Alias": "split_period",
"Startup Cost": 47.65,
"Total Cost": 47.96,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.176,
"Actual Total Time": 0.181,
"Actual Rows": 0,
"Actual Loops": 5,
"Output": ["split_period.split_period"],
"Filter": "((o.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange) && split_period.split_period)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Alias": "ranges",
"Startup Cost": 47.65,
"Total Cost": 47.87,
"Plan Rows": 6,
"Plan Width": 32,
"Actual Startup Time": 0.172,
"Actual Total Time": 0.177,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["tstzrange(ranges.start, COALESCE(ranges.finish, '2020-08-01 00:00:00+03'::timestamp with time zone), CASE WHEN (ranges.finish IS NULL) THEN '[]'::text ELSE '[)'::text END)"],
"Filter": "(ranges.finish IS NOT NULL)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE periods",
"Parallel Aware": false,
"Startup Cost": 47.24,
"Total Cost": 47.27,
"Plan Rows": 3,
"Plan Width": 32,
"Actual Startup Time": 0.139,
"Actual Total Time": 0.140,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["((o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))"],
"Group Key": ["((o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 24,
"Disk Usage": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.53,
"Total Cost": 47.23,
"Plan Rows": 3,
"Plan Width": 32,
"Actual Startup Time": 0.063,
"Actual Total Time": 0.131,
"Actual Rows": 2,
"Actual Loops": 5,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Scan Direction": "NoMovement",
"Index Name": "order_id_sys_period_app_period_excl",
"Relation Name": "order_bt",
"Schema": "public",
"Alias": "o_1",
"Startup Cost": 0.53,
"Total Cost": 4.55,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.047,
"Actual Total Time": 0.048,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["(o_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange)"],
"Index Cond": "((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 19,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Relation Name": "order_detail_bt",
"Schema": "public",
"Alias": "od_1",
"Startup Cost": 4.36,
"Total Cost": 42.64,
"Plan Rows": 2,
"Plan Width": 32,
"Actual Startup Time": 0.050,
"Actual Total Time": 0.061,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["(od_1.app_period * '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange)"],
"Recheck Cond": "(od_1.order_id = $0)",
"Rows Removed by Index Recheck": 0,
"Filter": "((od_1.app_period && '[\"2020-07-01 00:00:00+03\",\"2020-08-01 00:00:00+03\")'::tstzrange) AND (od_1.sys_period @> sys_time()))",
"Rows Removed by Filter": 1,
"Exact Heap Blocks": 7,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 17,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_detail_idx_order_id",
"Startup Cost": 0.00,
"Total Cost": 4.36,
"Plan Rows": 10,
"Plan Width": 0,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.011,
"Actual Rows": 3,
"Actual Loops": 5,
"Index Cond": "(od_1.order_id = $0)",
"Shared Hit Blocks": 10,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
},
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 0.38,
"Total Cost": 0.53,
"Plan Rows": 6,
"Plan Width": 16,
"Actual Startup Time": 0.169,
"Actual Total Time": 0.173,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))", "lead((lower(periods.app_period))) OVER (?)"],
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.38,
"Total Cost": 0.39,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.159,
"Actual Total Time": 0.160,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))"],
"Sort Key": ["(lower(periods.app_period))"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.24,
"Total Cost": 0.30,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.151,
"Actual Total Time": 0.152,
"Actual Rows": 2,
"Actual Loops": 5,
"Output": ["(lower(periods.app_period))"],
"Group Key": ["(lower(periods.app_period))"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 24,
"Disk Usage": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 0.23,
"Plan Rows": 6,
"Plan Width": 8,
"Actual Startup Time": 0.145,
"Actual Total Time": 0.148,
"Actual Rows": 2,
"Actual Loops": 5,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"CTE Name": "periods",
"Alias": "periods",
"Startup Cost": 0.00,
"Total Cost": 0.07,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 0.143,
"Actual Total Time": 0.144,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["lower(periods.app_period)"],
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"CTE Name": "periods",
"Alias": "periods_1",
"Startup Cost": 0.00,
"Total Cost": 0.07,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 5,
"Output": ["upper(periods_1.app_period)"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "order_detail_bt",
"Schema": "public",
"Alias": "od",
"Startup Cost": 4.36,
"Total Cost": 42.61,
"Plan Rows": 3,
"Plan Width": 143,
"Actual Startup Time": 0.049,
"Actual Total Time": 0.066,
"Actual Rows": 2,
"Actual Loops": 2,
"Output": ["od.*", "od.sort_order", "od.app_period", "od.order_id", "od.service_type_id"],
"Recheck Cond": "(od.order_id = o.id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(od.sys_period @> sys_time())",
"Rows Removed by Filter": 2,
"Exact Heap Blocks": 3,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 7,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "order_detail_idx_order_id",
"Startup Cost": 0.00,
"Total Cost": 4.36,
"Plan Rows": 10,
"Plan Width": 0,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.009,
"Actual Rows": 3,
"Actual Loops": 2,
"Index Cond": "(od.order_id = o.id)",
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Function Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Function Name": "service_level_price",
"Schema": "public",
"Alias": "sp",
"Startup Cost": 0.76,
"Total Cost": 13.26,
"Plan Rows": 5,
"Plan Width": 40,
"Actual Startup Time": 1.354,
"Actual Total Time": 1.404,
"Actual Rows": 65,
"Actual Loops": 3,
"Output": ["sp.*", "sp.service_type_id", "sp.period_id"],
"Function Call": "service_level_price(o.service_level_id, CASE WHEN (app_time() IS NOT NULL) THEN tstzrange(app_time(), NULL::timestamp with time zone) ELSE (NULLIF(current_setting('my.app_period'::text, true), ''::text))::tstzrange END)",
"Filter": "(o.period_id = sp.period_id)",
"Rows Removed by Filter": 46,
"Shared Hit Blocks": 15,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Alias": "ic",
"Startup Cost": 2.55,
"Total Cost": 11.12,
"Plan Rows": 1,
"Plan Width": 104,
"Actual Startup Time": 0.453,
"Actual Total Time": 0.458,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["ic.*", "ic.consumed_period", "ic.consumed"],
"Shared Hit Blocks": 12,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 2.55,
"Total Cost": 11.11,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 0.448,
"Actual Total Time": 0.452,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["ROW(cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.app_period)::currency_rate", "interval_length(((od.app_period * o.app_period) * split_period.split_period), $3, '2020-08-01 00:00:00+03'::timestamp with time zone, $4)", "COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))"],
"One-Time Filter": "(NOT isempty(((od.app_period * o.app_period) * split_period.split_period)))",
"Shared Hit Blocks": 12,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 2 (returns $3)",
"Parallel Aware": false,
"Relation Name": "period",
"Schema": "public",
"Alias": "period",
"Startup Cost": 0.00,
"Total Cost": 1.07,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.009,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["period.\"interval\""],
"Filter": "(period.id = ($2).period_id)",
"Rows Removed by Filter": 5,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 3 (returns $4)",
"Parallel Aware": false,
"Relation Name": "period",
"Schema": "public",
"Alias": "period_1",
"Startup Cost": 0.00,
"Total Cost": 1.07,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.005,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["period_1.trunc_interval"],
"Filter": "(period_1.id = ($2).period_id)",
"Rows Removed by Filter": 5,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 0.40,
"Total Cost": 8.70,
"Plan Rows": 1,
"Plan Width": 45,
"Actual Startup Time": 0.089,
"Actual Total Time": 0.092,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cr.id", "cr.rate", "cr.docdate", "cr.from_currency_id", "cr.to_currency_id", "cr.app_period"],
"Inner Unique": false,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 1,
"Actual Loops": 3,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "NoMovement",
"Index Name": "currency_rate_bt_id_sys_period_app_period_excl",
"Relation Name": "currency_rate_bt",
"Schema": "public",
"Alias": "cr",
"Startup Cost": 0.40,
"Total Cost": 8.67,
"Plan Rows": 1,
"Plan Width": 45,
"Actual Startup Time": 0.056,
"Actual Total Time": 0.058,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cr.id", "cr.rate", "cr.docdate", "cr.from_currency_id", "cr.to_currency_id", "cr.sid", "cr.app_period", "cr.sys_period"],
"Index Cond": "((cr.sys_period @> sys_time()) AND (cr.app_period @> lower(((od.app_period * o.app_period) * split_period.split_period))))",
"Rows Removed by Index Recheck": 0,
"Filter": "((cr.from_currency_id = (sp.*).currency_id) AND (cr.to_currency_id = app_currency()))",
"Rows Removed by Filter": 3,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Function Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Function Name": "calc_item_suma",
"Schema": "public",
"Alias": "cis",
"Startup Cost": 0.25,
"Total Cost": 10.25,
"Plan Rows": 1000,
"Plan Width": 32,
"Actual Startup Time": 0.065,
"Actual Total Time": 0.066,
"Actual Rows": 1,
"Actual Loops": 3,
"Output": ["cis.item_qty", "cis.item_price", "cis.item_cost", "cis.item_suma"],
"Function Call": "calc_item_suma(o.*, od.*, sp.*, ic.*)",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 4.98,
"Total Cost": 4.98,
"Plan Rows": 198,
"Plan Width": 76,
"Actual Startup Time": 0.130,
"Actual Total Time": 0.131,
"Actual Rows": 198,
"Actual Loops": 1,
"Output": ["rt.display", "rt.name", "rt.unit", "rt.label", "rt.id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 24,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "resource_type",
"Schema": "public",
"Alias": "rt",
"Startup Cost": 0.00,
"Total Cost": 4.98,
"Plan Rows": 198,
"Plan Width": 76,
"Actual Startup Time": 0.014,
"Actual Total Time": 0.072,
"Actual Rows": 198,
"Actual Loops": 1,
"Output": ["rt.display", "rt.name", "rt.unit", "rt.label", "rt.id"],
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 4.76,
"Total Cost": 4.76,
"Plan Rows": 176,
"Plan Width": 89,
"Actual Startup Time": 0.114,
"Actual Total Time": 0.115,
"Actual Rows": 176,
"Actual Loops": 1,
"Output": ["st.display", "st.name", "st.id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 30,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "service_type",
"Schema": "public",
"Alias": "st",
"Startup Cost": 0.00,
"Total Cost": 4.76,
"Plan Rows": 176,
"Plan Width": 89,
"Actual Startup Time": 0.013,
"Actual Total Time": 0.059,
"Actual Rows": 176,
"Actual Loops": 1,
"Output": ["st.display", "st.name", "st.id"],
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 11.29,
"Total Cost": 11.29,
"Plan Rows": 329,
"Plan Width": 154,
"Actual Startup Time": 0.221,
"Actual Total Time": 0.221,
"Actual Rows": 329,
"Actual Loops": 1,
"Output": ["pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 68,
"Shared Hit Blocks": 8,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "package",
"Schema": "public",
"Alias": "pkg",
"Startup Cost": 0.00,
"Total Cost": 11.29,
"Plan Rows": 329,
"Plan Width": 154,
"Actual Startup Time": 0.012,
"Actual Total Time": 0.117,
"Actual Rows": 329,
"Actual Loops": 1,
"Output": ["pkg.id", "pkg.link_1c_id", "pkg.display", "pkg.name"],
"Shared Hit Blocks": 8,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 33.20,
"Total Cost": 33.20,
"Plan Rows": 1920,
"Plan Width": 16,
"Actual Startup Time": 1.081,
"Actual Total Time": 1.082,
"Actual Rows": 1920,
"Actual Loops": 1,
"Output": ["pd.sort_order", "pd.package_id", "pd.resource_type_id", "pd.service_type_id"],
"Hash Buckets": 2048,
"Original Hash Buckets": 2048,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 101,
"Shared Hit Blocks": 14,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "package_detail",
"Schema": "public",
"Alias": "pd",
"Startup Cost": 0.00,
"Total Cost": 33.20,
"Plan Rows": 1920,
"Plan Width": 16,
"Actual Startup Time": 0.010,
"Actual Total Time": 0.605,
"Actual Rows": 1920,
"Actual Loops": 1,
"Output": ["pd.sort_order", "pd.package_id", "pd.resource_type_id", "pd.service_type_id"],
"Shared Hit Blocks": 14,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
},
"Planning": {
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 6.516,
"Triggers": [
],
"Execution Time": 9.690
}
]
Attachments:
[text/plain] full.txt (3.8K, ../../1035195294.20210515173416@yandex.ru/2-full.txt)
download | inline:
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 )
[text/plain] direct-plan.txt (124.4K, ../../1035195294.20210515173416@yandex.ru/3-direct-plan.txt)
download
[text/plain] function-plan.txt (122.5K, ../../1035195294.20210515173416@yandex.ru/4-function-plan.txt)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-15 14:52 David Rowley <dgrowleyml@gmail.com>
parent: Eugen Konkov <kes-kes@yandex.ru>
0 siblings, 1 reply; 8+ messages in thread
From: David Rowley @ 2021-05-15 14:52 UTC (permalink / raw)
To: Eugen Konkov <kes-kes@yandex.ru>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru
On Sun, 16 May 2021 at 02:34, Eugen Konkov <kes-kes@yandex.ru> wrote:
> 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.
This is unrelated to the optimisation that you were asking about before.
All that's going on here is that WHERE is evaluated before SELECT.
This means that your filtering is done before the window functions are
executed. This is noted in the docs in [1]:
> The rows considered by a window function are those of the “virtual table” produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.
If you want to filter rows after the window functions are evaluated
then you'll likely want to use a subquery.
David
[1] https://www.postgresql.org/docs/13/tutorial-window.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-15 14:59 David Rowley <dgrowleyml@gmail.com>
parent: KES <kes-kes@yandex.ru>
0 siblings, 1 reply; 8+ messages in thread
From: David Rowley @ 2021-05-15 14:59 UTC (permalink / raw)
To: KES <kes-kes@yandex.ru>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru <ladayaroslav@yandex.ru>
On Sat, 15 May 2021 at 00:39, KES <kes-kes@yandex.ru> wrote:
>
> Thank you for detailed explanation. I glad to hear that I can use aliases and this will be recognized and optimization is applied.
>
> >We'd need some sort of ability to assign ressortgroupref to a particular column within a
> whole-row var
> Could it be possible to create hidden alias in same way as I did that manually?
>
> Algorithm seems not complex:
> 1. User refer column from composite type/whole-row: (o).agreement_id
> 2. Create hidden column at select: _o_agreement_id
> 3. Replace other references to (o).agreement_id by _o_agreement_id
> 4. Process query as usual after replacements
Internally Postgresql does use a hidden column for columns that are
required for calculations which are not in the SELECT list. e.g ones
that are in the GROUP BY / ORDER BY, or in your case a window
function's PARTITION BY. We call these "resjunk" columns. The problem
is you can't reference those from the parent query. If you explicitly
had listed that column in the SELECT clause, it won't cost you
anything more since the planner will add it regardless and just hide
it from you. When you add it yourself you'll be able to use it in the
subquery and you'll be able to filter out the partitions that you
don't want.
I really think you're driving yourself down a difficult path by
expecting queries with whole-row vars to be optimised just as well as
using select * or explicitly listing the columns.
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-15 16:10 Eugen Konkov <kes-kes@yandex.ru>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Eugen Konkov @ 2021-05-15 16:10 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru
Hello David,
Saturday, May 15, 2021, 5:52:47 PM, you wrote:
> On Sun, 16 May 2021 at 02:34, Eugen Konkov <kes-kes@yandex.ru> wrote:
>> 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.
> This is unrelated to the optimisation that you were asking about before.
> All that's going on here is that WHERE is evaluated before SELECT.
> This means that your filtering is done before the window functions are
> executed. This is noted in the docs in [1]:
>> The rows considered by a window function are those of the “virtual table” produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.
> If you want to filter rows after the window functions are evaluated
> then you'll likely want to use a subquery.
> David
> [1] https://www.postgresql.org/docs/13/tutorial-window.html
Sorry, I miss that WHERE works first and after it
window function.
>This is unrelated to the optimisation that you were asking about before.
So, yes, unrelated.
Thank you for your answers.
--
Best regards,
Eugen Konkov
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #16968: Planner does not recognize optimization
@ 2021-05-15 16:10 Eugen Konkov <kes-kes@yandex.ru>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Eugen Konkov @ 2021-05-15 16:10 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; pgsql-performance; ladayaroslav@yandex.ru <ladayaroslav@yandex.ru>
Hello David,
> I really think you're driving yourself down a difficult path by
> expecting queries with whole-row vars to be optimised just as well as
> using select * or explicitly listing the columns.
Yes, I was expect that. I use whole-row because do not want repeat all
10+ columns at select. I do not use (row1).*, (row2).*, because rows
could have same columns. eg: row1.name, row2.name both will be
named as 'name' and then I can not distinguish them.
So I select whole-row and put myself into problems ((
It would be nice if (row1).** will be expanded to: row1_id, row1_name
etc. But this is other question which I already ask at different
thread.
Saturday, May 15, 2021, 5:59:41 PM, you wrote:
> On Sat, 15 May 2021 at 00:39, KES <kes-kes@yandex.ru> wrote:
>>
>> Thank you for detailed explanation. I glad to hear that I can use aliases and this will be recognized and optimization is applied.
>>
>> >We'd need some sort of ability to assign ressortgroupref to a particular column within a
>> whole-row var
>> Could it be possible to create hidden alias in same way as I did that manually?
>>
>> Algorithm seems not complex:
>> 1. User refer column from composite type/whole-row: (o).agreement_id
>> 2. Create hidden column at select: _o_agreement_id
>> 3. Replace other references to (o).agreement_id by _o_agreement_id
>> 4. Process query as usual after replacements
> Internally Postgresql does use a hidden column for columns that are
> required for calculations which are not in the SELECT list. e.g ones
> that are in the GROUP BY / ORDER BY, or in your case a window
> function's PARTITION BY. We call these "resjunk" columns. The problem
> is you can't reference those from the parent query. If you explicitly
> had listed that column in the SELECT clause, it won't cost you
> anything more since the planner will add it regardless and just hide
> it from you. When you add it yourself you'll be able to use it in the
> subquery and you'll be able to filter out the partitions that you
> don't want.
> I really think you're driving yourself down a difficult path by
> expecting queries with whole-row vars to be optimised just as well as
> using select * or explicitly listing the columns.
> David
--
Best regards,
Eugen Konkov
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-05-15 16:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-13 13:35 Re: BUG #16968: Planner does not recognize optimization Eugen Konkov <kes-kes@yandex.ru>
2021-05-13 23:52 ` David Rowley <dgrowleyml@gmail.com>
2021-05-14 12:39 ` KES <kes-kes@yandex.ru>
2021-05-15 14:59 ` David Rowley <dgrowleyml@gmail.com>
2021-05-15 16:10 ` Eugen Konkov <kes-kes@yandex.ru>
2021-05-15 14:34 ` Eugen Konkov <kes-kes@yandex.ru>
2021-05-15 14:52 ` David Rowley <dgrowleyml@gmail.com>
2021-05-15 16:10 ` Eugen Konkov <kes-kes@yandex.ru>
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