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@xxxxxxxxx> 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
}
]
