1.15.0
Execution time:
2m 6s
Planning time:
14ms
Triggers: N/A
| #1 | Subquery Scan | |
| #2 | â”” Simple GroupAggregate | |
| #3 |  └ Sort | |
| #4 |   └ Nested Loop | |
| #5 |    ├ Nested Loop | |
| #6 |    │├ Nested Loop | |
| #7 |    ││├ Nested Loop | |
| #8 |    │││├ Nested Loop | |
| #9 |    ││││├ Nested Loop | |
| #10 |    │││││├ Nested Loop | |
| #11 |    ││││││├ WindowAgg | |
| #12 |    │││││││└ Sort | |
| #13 |    │││││││ └ Seq Scan | |
| #14 |    ││││││└ Materialize | |
| #15 |    ││││││ └ Seq Scan | |
| #16 |    │││││└ Materialize | |
| #17 |    │││││ └ Hash Join | |
| #18 |    │││││  ├ Seq Scan | |
| #19 |    │││││  └ Hash | |
| #20 |    │││││   └ Subquery Scan | |
| #21 |    │││││    └ WindowAgg | |
| #22 |    │││││     └ Sort | |
| #23 |    │││││      └ Seq Scan | |
| #24 |    ││││└ Materialize | |
| #25 |    ││││ └ Seq Scan | |
| #26 |    │││└ Materialize | |
| #27 |    │││ └ Seq Scan | |
| #28 |    ││└ Seq Scan | |
| #29 |    │└ Index Scan | |
| #30 |    └ Simple HashAggregate | |
| #31 |     └ Append | |
| #32 |      ├ Seq Scan | |
| #33 |      └ Seq Scan |
[
{
"Plan": {
"Node Type": "Subquery Scan",
"Parallel Aware": false,
"Alias": "a",
"Startup Cost": 20163.33,
"Total Cost": 20163.39,
"Plan Rows": 2,
"Plan Width": 4,
"Actual Startup Time": 126443.066,
"Actual Total Time": 126612.673,
"Actual Rows": 178,
"Actual Loops": 1,
"Output": ["a.id"],
"Shared Hit Blocks": 1032131,
"Shared Read Blocks": 25545,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 1388,
"Temp Written Blocks": 1387,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 20163.33,
"Total Cost": 20163.37,
"Plan Rows": 2,
"Plan Width": 168,
"Actual Startup Time": 126443.064,
"Actual Total Time": 126612.632,
"Actual Rows": 178,
"Actual Loops": 1,
"Output": ["o.category", "NULL::integer", "NULL::timestamp with time zone", "NULL::integer", "NULL::timestamp without time zone", "b.company", "NULL::numeric", "NULL::numeric", "NULL::numeric", "NULL::numeric", "g.digits", "NULL::integer"],
"Group Key": ["b.company", "g.digits", "o.category"],
"Shared Hit Blocks": 1032131,
"Shared Read Blocks": 25545,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 1388,
"Temp Written Blocks": 1387,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 20163.33,
"Total Cost": 20163.33,
"Plan Rows": 2,
"Plan Width": 12,
"Actual Startup Time": 126442.236,
"Actual Total Time": 126547.906,
"Actual Rows": 333459,
"Actual Loops": 1,
"Output": ["o.category", "b.company", "g.digits"],
"Sort Key": ["g.digits", "o.category"],
"Sort Method": "external merge",
"Sort Space Used": 7144,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 1032131,
"Shared Read Blocks": 25545,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 1388,
"Temp Written Blocks": 1387,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 370.65,
"Total Cost": 20163.32,
"Plan Rows": 2,
"Plan Width": 12,
"Actual Startup Time": 350.090,
"Actual Total Time": 126031.555,
"Actual Rows": 333459,
"Actual Loops": 1,
"Output": ["o.category", "b.company", "g.digits"],
"Join Filter": "(m.template = o.template)",
"Rows Removed by Join Filter": 321311541,
"Shared Hit Blocks": 1032128,
"Shared Read Blocks": 25545,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.61,
"Total Cost": 19716.33,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 343.203,
"Actual Total Time": 7679.951,
"Actual Rows": 104600,
"Actual Loops": 1,
"Output": ["b.company", "g.digits", "m.template"],
"Shared Hit Blocks": 1032126,
"Shared Read Blocks": 25468,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.33,
"Total Cost": 19716.02,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 343.162,
"Actual Total Time": 6831.024,
"Actual Rows": 104600,
"Actual Loops": 1,
"Output": ["b.company", "b.product", "g.digits"],
"Join Filter": "(f.currency = g.id)",
"Rows Removed by Join Filter": 17677400,
"Shared Hit Blocks": 732205,
"Shared Read Blocks": 11399,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.33,
"Total Cost": 19705.19,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 343.097,
"Actual Total Time": 1191.239,
"Actual Rows": 104600,
"Actual Loops": 1,
"Output": ["b.company", "b.product", "f.currency", "i.currency"],
"Join Filter": "((b.to_location = l.id) AND ((((k.type)::text = ANY ('{storage,drop}'::text[])) AND ((l.type)::text = 'customer'::text)) OR (((k.type)::text = 'customer'::text) AND ((l.type)::text = ANY ('{storage,drop}'::text[])))))",
"Rows Removed by Join Filter": 775349,
"Shared Hit Blocks": 12,
"Shared Read Blocks": 11392,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.33,
"Total Cost": 19703.70,
"Plan Rows": 2,
"Plan Width": 29,
"Actual Startup Time": 343.082,
"Actual Total Time": 807.809,
"Actual Rows": 125707,
"Actual Loops": 1,
"Output": ["b.company", "b.to_location", "b.product", "f.currency", "i.currency", "k.type"],
"Join Filter": "(b.from_location = k.id)",
"Rows Removed by Join Filter": 808968,
"Shared Hit Blocks": 11,
"Shared Read Blocks": 11392,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.33,
"Total Cost": 19702.14,
"Plan Rows": 4,
"Plan Width": 24,
"Actual Startup Time": 343.051,
"Actual Total Time": 486.939,
"Actual Rows": 133525,
"Actual Loops": 1,
"Output": ["b.company", "b.from_location", "b.to_location", "b.product", "f.currency", "i.currency"],
"Join Filter": "((i.date <= b.effective_date) AND (((nth_value(i.date, 2) OVER (?)) IS NULL) OR ((nth_value(i.date, 2) OVER (?)) >= b.effective_date)))",
"Rows Removed by Join Filter": 133525,
"Shared Hit Blocks": 10,
"Shared Read Blocks": 11392,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.08,
"Total Cost": 2.28,
"Plan Rows": 2,
"Plan Width": 20,
"Actual Startup Time": 0.120,
"Actual Total Time": 0.202,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": ["f.id", "f.currency", "i.currency", "i.date", "(nth_value(i.date, 2) OVER (?))"],
"Join Filter": "(f.currency = i.currency)",
"Rows Removed by Join Filter": 4,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 2,
"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": 1.08,
"Total Cost": 1.16,
"Plan Rows": 4,
"Plan Width": 44,
"Actual Startup Time": 0.081,
"Actual Total Time": 0.139,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["i.currency", "NULL::numeric", "i.date", "nth_value(i.date, 2) OVER (?)"],
"Shared Hit Blocks": 3,
"Shared Read Blocks": 1,
"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": 1.08,
"Total Cost": 1.09,
"Plan Rows": 4,
"Plan Width": 8,
"Actual Startup Time": 0.069,
"Actual Total Time": 0.082,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["i.currency", "i.date"],
"Sort Key": ["i.currency", "i.date"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 3,
"Shared Read Blocks": 1,
"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": "currency_currency_rate",
"Schema": "public",
"Alias": "i",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 8,
"Actual Startup Time": 0.042,
"Actual Total Time": 0.044,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["i.currency", "i.date"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1,
"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": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.006,
"Actual Rows": 1,
"Actual Loops": 6,
"Output": ["f.id", "f.currency"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1,
"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": "company_company",
"Schema": "public",
"Alias": "f",
"Startup Cost": 0.00,
"Total Cost": 1.01,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.021,
"Actual Total Time": 0.022,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["f.id", "f.currency"],
"Filter": "(f.id = 1)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1,
"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": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 1.25,
"Total Cost": 19699.25,
"Plan Rows": 19,
"Plan Width": 20,
"Actual Startup Time": 0.083,
"Actual Total Time": 188.260,
"Actual Rows": 133525,
"Actual Loops": 2,
"Output": ["b.company", "b.effective_date", "b.from_location", "b.to_location", "b.product"],
"Shared Hit Blocks": 7,
"Shared Read Blocks": 11390,
"Shared Dirtied Blocks": 5,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 490,
"Temp Written Blocks": 489,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.25,
"Total Cost": 19699.15,
"Plan Rows": 19,
"Plan Width": 20,
"Actual Startup Time": 0.128,
"Actual Total Time": 265.739,
"Actual Rows": 133525,
"Actual Loops": 1,
"Output": ["b.company", "b.effective_date", "b.from_location", "b.to_location", "b.product"],
"Hash Cond": "(b.currency = c.currency)",
"Join Filter": "((c.start_date <= b.effective_date) AND ((c.end_date IS NULL) OR (c.end_date >= b.effective_date)))",
"Rows Removed by Join Filter": 133525,
"Shared Hit Blocks": 7,
"Shared Read Blocks": 11390,
"Shared Dirtied Blocks": 5,
"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": "stock_move",
"Schema": "public",
"Alias": "b",
"Startup Cost": 0.00,
"Total Cost": 19098.30,
"Plan Rows": 119420,
"Plan Width": 24,
"Actual Startup Time": 0.047,
"Actual Total Time": 174.953,
"Actual Rows": 133525,
"Actual Loops": 1,
"Output": ["b.id", "b.origin", "b.create_date", "b.planned_date", "b.write_uid", "b.currency", "b.create_uid", "b.unit_price", "b.state", "b.shipment", "b.effective_date", "b.uom", "b.cost_price", "b.internal_quantity", "b.product", "b.company", "b.write_date", "b.to_location", "b.from_location", "b.quantity", "b.package", "b.lot", "b.internal_volume", "b.internal_weight", "b.unit_landed_cost", "b.production_input", "b.production_output", "b.unit_price_updated", "b.production_cost_price_updated", "b.shipment_out_cost_price"],
"Filter": "((b.effective_date >= '2019-12-18'::date) AND (b.effective_date <= '2020-12-18'::date) AND (b.company = 1) AND ((b.state)::text = 'done'::text) AND ((b.quantity <> '0'::double precision) OR (b.internal_quantity <> '0'::double precision)))",
"Rows Removed by Filter": 173376,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 11390,
"Shared Dirtied Blocks": 5,
"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": 1.20,
"Total Cost": 1.20,
"Plan Rows": 4,
"Plan Width": 12,
"Actual Startup Time": 0.059,
"Actual Total Time": 0.059,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["c.currency", "c.start_date", "c.end_date"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Shared Hit Blocks": 1,
"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": "c",
"Startup Cost": 1.08,
"Total Cost": 1.20,
"Plan Rows": 4,
"Plan Width": 12,
"Actual Startup Time": 0.042,
"Actual Total Time": 0.049,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["c.currency", "c.start_date", "c.end_date"],
"Shared Hit Blocks": 1,
"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": 1.08,
"Total Cost": 1.16,
"Plan Rows": 4,
"Plan Width": 44,
"Actual Startup Time": 0.041,
"Actual Total Time": 0.045,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["d.currency", "NULL::numeric", "d.date", "nth_value(d.date, 2) OVER (?)"],
"Shared Hit Blocks": 1,
"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": 1.08,
"Total Cost": 1.09,
"Plan Rows": 4,
"Plan Width": 8,
"Actual Startup Time": 0.032,
"Actual Total Time": 0.034,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["d.currency", "d.date"],
"Sort Key": ["d.currency", "d.date"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 1,
"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": "currency_currency_rate",
"Schema": "public",
"Alias": "d",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 8,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.006,
"Actual Rows": 6,
"Actual Loops": 1,
"Output": ["d.currency", "d.date"],
"Shared Hit Blocks": 1,
"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": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.21,
"Plan Rows": 6,
"Plan Width": 13,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.001,
"Actual Rows": 7,
"Actual Loops": 133525,
"Output": ["k.id", "k.type"],
"Shared Hit Blocks": 1,
"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": "stock_location",
"Schema": "public",
"Alias": "k",
"Startup Cost": 0.00,
"Total Cost": 1.18,
"Plan Rows": 6,
"Plan Width": 13,
"Actual Startup Time": 0.020,
"Actual Total Time": 0.030,
"Actual Rows": 7,
"Actual Loops": 1,
"Output": ["k.id", "k.type"],
"Filter": "(((k.type)::text = ANY ('{storage,drop}'::text[])) OR ((k.type)::text = 'customer'::text))",
"Rows Removed by Filter": 7,
"Shared Hit Blocks": 1,
"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": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.21,
"Plan Rows": 6,
"Plan Width": 13,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.001,
"Actual Rows": 7,
"Actual Loops": 125707,
"Output": ["l.id", "l.type"],
"Shared Hit Blocks": 1,
"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": "stock_location",
"Schema": "public",
"Alias": "l",
"Startup Cost": 0.00,
"Total Cost": 1.18,
"Plan Rows": 6,
"Plan Width": 13,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.016,
"Actual Rows": 7,
"Actual Loops": 1,
"Output": ["l.id", "l.type"],
"Filter": "(((l.type)::text = 'customer'::text) OR ((l.type)::text = ANY ('{storage,drop}'::text[])))",
"Rows Removed by Filter": 7,
"Shared Hit Blocks": 1,
"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": "Inner",
"Parallel Aware": false,
"Relation Name": "currency_currency",
"Schema": "public",
"Alias": "g",
"Startup Cost": 0.00,
"Total Cost": 8.70,
"Plan Rows": 170,
"Plan Width": 8,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.024,
"Actual Rows": 170,
"Actual Loops": 104600,
"Output": ["g.id", "g.code", "g.create_date", "g.write_date", "g.p_sep_by_space", "g.write_uid", "g.active", "g.mon_grouping", "g.create_uid", "g.rounding", "g.numeric_code", "g.n_cs_precedes", "g.n_sign_posn", "g.p_cs_precedes", "g.mon_decimal_point", "g.symbol", "g.mon_thousands_sep", "g.negative_sign", "g.n_sep_by_space", "g.positive_sign", "g.digits", "g.name", "g.p_sign_posn"],
"Shared Hit Blocks": 732193,
"Shared Read Blocks": 7,
"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": "Forward",
"Index Name": "product_product_pkey",
"Relation Name": "product_product",
"Schema": "public",
"Alias": "m",
"Startup Cost": 0.28,
"Total Cost": 0.30,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.007,
"Actual Rows": 1,
"Actual Loops": 104600,
"Output": ["m.id", "m.code", "m.create_date", "m.write_uid", "m.create_uid", "m.template", "m.description", "m.write_date", "m.active", "m.webshop_description", "m.webshop_youtube_id", "m.webshop_meta_description", "m.webshop_youtube_ratio", "m.suffix_code"],
"Index Cond": "(m.id = b.product)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 299921,
"Shared Read Blocks": 14069,
"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": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 368.04,
"Total Cost": 392.33,
"Plan Rows": 2429,
"Plan Width": 36,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.745,
"Actual Rows": 3075,
"Actual Loops": 104600,
"Output": ["(((o.id * 2) + 0))", "o.category", "o.create_date", "o.create_uid", "o.template", "o.write_date", "o.write_uid"],
"Group Key": ["(((o.id * 2) + 0))", "o.category", "o.create_date", "o.create_uid", "o.template", "o.write_date", "o.write_uid"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 77,
"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": 325.53,
"Plan Rows": 2429,
"Plan Width": 36,
"Actual Startup Time": 0.036,
"Actual Total Time": 4.308,
"Actual Rows": 3075,
"Actual Loops": 1,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 77,
"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": "Member",
"Parallel Aware": false,
"Relation Name": "product_template-product_category",
"Schema": "public",
"Alias": "o",
"Startup Cost": 0.00,
"Total Cost": 161.40,
"Plan Rows": 1380,
"Plan Width": 36,
"Actual Startup Time": 0.034,
"Actual Total Time": 1.915,
"Actual Rows": 2019,
"Actual Loops": 1,
"Output": ["((o.id * 2) + 0)", "o.category", "o.create_date", "o.create_uid", "o.template", "o.write_date", "o.write_uid"],
"Filter": "((o.category IS NOT NULL) AND (((o.category >= 5) AND (o.category <= 16)) OR ((o.category >= 45) AND (o.category <= 95)) OR ((o.category >= 97) AND (o.category <= 126)) OR ((o.category >= 134) AND (o.category <= 179)) OR ((o.category >= 181) AND (o.category <= 188)) OR ((o.category >= 190) AND (o.category <= 212)) OR ((o.category >= 214) AND (o.category <= 244)) OR ((o.category >= 247) AND (o.category <= 299)) OR (o.category = ANY ('{1,22,23,24,25,27,34,36,37,38,128,129,130,131,132}'::integer[]))))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 17,
"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": "Member",
"Parallel Aware": false,
"Relation Name": "product_template",
"Schema": "public",
"Alias": "q",
"Startup Cost": 0.00,
"Total Cost": 139.84,
"Plan Rows": 1049,
"Plan Width": 36,
"Actual Startup Time": 0.047,
"Actual Total Time": 2.024,
"Actual Rows": 1056,
"Actual Loops": 1,
"Output": ["((q.id * 2) + 1)", "q.account_category", "q.create_date", "q.create_uid", "q.id", "q.write_date", "q.write_uid"],
"Filter": "((q.account_category IS NOT NULL) AND (q.account_category IS NOT NULL) AND (((q.account_category >= 5) AND (q.account_category <= 16)) OR ((q.account_category >= 45) AND (q.account_category <= 95)) OR ((q.account_category >= 97) AND (q.account_category <= 126)) OR ((q.account_category >= 134) AND (q.account_category <= 179)) OR ((q.account_category >= 181) AND (q.account_category <= 188)) OR ((q.account_category >= 190) AND (q.account_category <= 212)) OR ((q.account_category >= 214) AND (q.account_category <= 244)) OR ((q.account_category >= 247) AND (q.account_category <= 299)) OR (q.account_category = ANY ('{1,22,23,24,25,27,34,36,37,38,128,129,130,131,132}'::integer[]))))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 60,
"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": 14.025,
"Triggers": [
],
"Execution Time": 126617.521
}
]
SELECT "a"."id"
FROM (
SELECT "n"."category" AS "id", 0 AS "create_uid", CURRENT_TIMESTAMP AS "create_date", CAST(NULL AS INT4) AS "write_uid", CAST(NULL AS TIMESTAMP) AS "write_date", "b"."company" AS "company", CAST(ROUND(SUM(((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."internal_quantity" AS NUMERIC)) * "b"."cost_price")), "g"."digits") AS NUMERIC) AS "cost", CAST(ROUND(SUM(((((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."quantity" AS NUMERIC)) * "b"."unit_price") * "h"."rate") / "c"."rate")), "g"."digits") AS NUMERIC) AS "revenue", CAST(ROUND((SUM(((((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."quantity" AS NUMERIC)) * "b"."unit_price") * "h"."rate") / "c"."rate")) - SUM(((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."internal_quantity" AS NUMERIC)) * "b"."cost_price"))), "g"."digits") AS NUMERIC) AS "profit", CAST(ROUND(CASE WHEN (SUM(((((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."quantity" AS NUMERIC)) * "b"."unit_price") * "h"."rate") / "c"."rate")) != 0) THEN ((SUM(((((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."quantity" AS NUMERIC)) * "b"."unit_price") * "h"."rate") / "c"."rate")) - SUM(((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."internal_quantity" AS NUMERIC)) * "b"."cost_price"))) / SUM(((((CASE WHEN (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))) THEN -1 ELSE 1 END * CAST("b"."quantity" AS NUMERIC)) * "b"."unit_price") * "h"."rate") / "c"."rate"))) END, 4) AS NUMERIC) AS "margin", "g"."digits" AS "currency_digits", "n"."category" AS "category"
FROM "stock_move" AS "b"
INNER JOIN (
SELECT "d"."currency" AS "currency", "d"."rate" AS "rate", "d"."date" AS "start_date", NTH_VALUE("d"."date", 2) OVER "e" AS "end_date"
FROM "currency_currency_rate" AS "d" WINDOW "e" AS (PARTITION BY "d"."currency" ORDER BY "d"."date" ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)) AS "c" ON ((("b"."currency" = "c"."currency") AND ("c"."start_date" <= "b"."effective_date")) AND (("c"."end_date" IS NULL) OR ("c"."end_date" >= "b"."effective_date")))
INNER JOIN "company_company" AS "f" ON ("b"."company" = "f"."id")
INNER JOIN "currency_currency" AS "g" ON ("f"."currency" = "g"."id")
INNER JOIN (
SELECT "i"."currency" AS "currency", "i"."rate" AS "rate", "i"."date" AS "start_date", NTH_VALUE("i"."date", 2) OVER "j" AS "end_date"
FROM "currency_currency_rate" AS "i" WINDOW "j" AS (PARTITION BY "i"."currency" ORDER BY "i"."date" ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)) AS "h" ON ((("f"."currency" = "h"."currency") AND ("h"."start_date" <= "b"."effective_date")) AND (("h"."end_date" IS NULL) OR ("h"."end_date" >= "b"."effective_date")))
INNER JOIN "stock_location" AS "k" ON ("b"."from_location" = "k"."id")
INNER JOIN "stock_location" AS "l" ON ("b"."to_location" = "l"."id")
INNER JOIN "product_product" AS "m" ON ("b"."product" = "m"."id")
INNER JOIN (
SELECT (("o"."id" * 2) + 0) AS "id", CAST("o"."category" AS INT4) AS "category", CAST("o"."create_date" AS TIMESTAMP) AS "create_date", CAST("o"."create_uid" AS INT4) AS "create_uid", CAST("o"."template" AS INT4) AS "template", CAST("o"."write_date" AS TIMESTAMP) AS "write_date", CAST("o"."write_uid" AS INT4) AS "write_uid"
FROM "product_template-product_category" AS "o"
UNION
SELECT (("p"."id" * 2) + 1) AS "id", CAST("p"."category" AS INT4) AS "category", CAST("p"."create_date" AS TIMESTAMP) AS "create_date", CAST("p"."create_uid" AS INT4) AS "create_uid", CAST("p"."template" AS INT4) AS "template", CAST("p"."write_date" AS TIMESTAMP) AS "write_date", CAST("p"."write_uid" AS INT4) AS "write_uid"
FROM (
SELECT "q"."id" AS "id", "q"."create_uid" AS "create_uid", "q"."create_date" AS "create_date", "q"."write_uid" AS "write_uid", "q"."write_date" AS "write_date", "q"."id" AS "template", "q"."account_category" AS "category"
FROM "product_template" AS "q"
WHERE ("q"."account_category" IS NOT NULL)) AS "p") AS "n" ON ("m"."template" = "n"."template")
WHERE ((((((("b"."company" = 1) AND ((("k"."type" IN ('storage', 'drop')) AND ("l"."type" IN ('customer'))) OR (("k"."type" IN ('customer')) AND ("l"."type" IN ('storage', 'drop'))))) AND ("b"."state" = 'done')) AND (("b"."quantity" != 0) OR ("b"."internal_quantity" != 0))) AND ("b"."effective_date" >= '2019-12-18'::date)) AND ("b"."effective_date" <= '2020-12-18'::date)) AND ("n"."category" IS NOT NULL))
GROUP BY "b"."company", "g"."digits", "n"."category") AS "a"
WHERE ((("a"."id" >= 5) AND ("a"."id" <= 16)) OR (("a"."id" >= 45) AND ("a"."id" <= 95)) OR (("a"."id" >= 97) AND ("a"."id" <= 126)) OR (("a"."id" >= 134) AND ("a"."id" <= 179)) OR (("a"."id" >= 181) AND ("a"."id" <= 188)) OR (("a"."id" >= 190) AND ("a"."id" <= 212)) OR (("a"."id" >= 214) AND ("a"."id" <= 244)) OR (("a"."id" >= 247) AND ("a"."id" <= 299)) OR ("a"."id" IN (1, 22, 23, 24, 25, 27, 34, 36, 37, 38, 128, 129, 130, 131, 132)))
| Table | Count | Time | |
|---|---|---|---|
| currency_currency | 1 | 2s 510ms | 2% |
| #28 Seq Scan | 2s 510ms | 2% | |
| product_product | 1 | 732ms | 1% |
| #29 Index Scan | 732ms | 1% | |
| stock_move | 1 | 175ms | 0% |
| #18 Seq Scan | 175ms | 0% | |
| product_template | 1 | 2.02ms | 0% |
| #33 Seq Scan | 2.02ms | 0% | |
| product_template-product_category | 1 | 1.92ms | 0% |
| #32 Seq Scan | 1.92ms | 0% | |
| currency_currency_rate | 2 | 0.05ms | 0% |
| #13 Seq Scan | 0.044ms | 0% | |
| #23 Seq Scan | 0.006ms | 0% | |
| stock_location | 2 | 0.046ms | 0% |
| #25 Seq Scan | 0.03ms | 0% | |
| #27 Seq Scan | 0.016ms | 0% | |
| company_company | 1 | 0.022ms | 0% |
| #15 Seq Scan | 0.022ms | 0% | |
| Node Type | Count | Time | |
|---|---|---|---|
| HashAggregate | 1 | 1m 17s | 62% |
| #30 HashAggregate | 1m 17s | 62% | |
| Nested Loop | 7 | 44s 226ms | 35% |
| #4 Nested Loop | 40s 425ms | 32% | |
| #6 Nested Loop | 3s 129ms | 2% | |
| #7 Nested Loop | 258ms | 0% | |
| #8 Nested Loop | 187ms | 0% | |
| #5 Nested Loop | 117ms | 0% | |
| #9 Nested Loop | 110ms | 0% | |
| #10 Nested Loop | 0.027ms | 0% | |
| Seq Scan | 9 | 2s 689ms | 2% |
| #28 Seq Scan | 2s 510ms | 2% | |
| #18 Seq Scan | 175ms | 0% | |
| #33 Seq Scan | 2.02ms | 0% | |
| #32 Seq Scan | 1.92ms | 0% | |
| #13 Seq Scan | 0.044ms | 0% | |
| #25 Seq Scan | 0.03ms | 0% | |
| #15 Seq Scan | 0.022ms | 0% | |
| #27 Seq Scan | 0.016ms | 0% | |
| #23 Seq Scan | 0.006ms | 0% | |
| Index Scan | 1 | 732ms | 1% |
| #29 Index Scan | 732ms | 1% | |
| Sort | 3 | 516ms | 0% |
| #3 Sort | 516ms | 0% | |
| #12 Sort | 0.038ms | 0% | |
| #22 Sort | 0.028ms | 0% | |
| Materialize | 4 | 370ms | 0% |
| #24 Materialize | 133ms | 0% | |
| #26 Materialize | 126ms | 0% | |
| #16 Materialize | 111ms | 0% | |
| #14 Materialize | 0.014ms | 0% | |
| Hash Join | 1 | 90.7ms | 0% |
| #17 Hash Join | 90.7ms | 0% | |
| GroupAggregate | 1 | 64.7ms | 0% |
| #2 GroupAggregate | 64.7ms | 0% | |
| Append | 1 | 0.369ms | 0% |
| #31 Append | 0.369ms | 0% | |
| WindowAgg | 2 | 0.068ms | 0% |
| #11 WindowAgg | 0.057ms | 0% | |
| #21 WindowAgg | 0.011ms | 0% | |
| Subquery Scan | 2 | 0.045ms | 0% |
| #1 Subquery Scan | 0.041ms | 0% | |
| #20 Subquery Scan | 0.004ms | 0% | |
| Hash | 1 | 0.01ms | 0% |
| #19 Hash | 0.01ms | 0% | |