01.08.2024 06:41, Tom Lane wrote:
But beside that, I've found a separate regression. Bisecting for this
degradation:
Best pg-src-17--.* worse than pg-src-16--.* by 105.0 percents (356.63 >
173.96): s64da_tpcds.query95
Average pg-src-17--.* worse than pg-src-16--.* by 105.2 percents (357.79 >
174.38): s64da_tpcds.query95
pointed at f7816aec2.
I'm not terribly concerned about that. The nature of planner changes
like that is that some queries will get worse and some better, because
the statistics and cost estimates we're dealing with are not perfect.
It is probably worth drilling down into that test case to understand
where the planner is going wrong, with an eye to future improvements;
but I doubt it's something we need to address for v17.
Please find attached two plans for that query [1].
(I repeated the benchmark for f7816aec2 and f7816aec2~1 five times and
made sure that both plans are stable.)
Meanwhile I've bisected another degradation:
Best pg-src-17--.* worse than pg-src-16--.* by 11.3 percents (7.17 > 6.44):
job.query6f
and came to the commit b7b0f3f27 again.
[1]
https://github.com/swarm64/s64da-benchmark-toolkit/blob/master/benchmarks/tpcds/queries/queries_10/95.sql
Best regards,
Alexander
[{
"Query Text": "-- RNGSEED: 1\n\n-- EXPLAIN (FORMAT JSON)\nwith ws_wh
as\n(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk
wh2\n from web_sales ws1,web_sales ws2\n where ws1.ws_order_number =
ws2.ws_order_number\n and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)\n
select\n count(distinct ws_order_number) as \"order count\"\n
,sum(ws_ext_ship_cost) as \"total shipping cost\"\n ,sum(ws_net_profit) as
\"total net profit\"\nfrom\n web_sales ws1\n ,date_dim\n
,customer_address\n ,web_site\nwhere\n d_date between '2000-3-01' and\n
(cast('2000-3-01' as date) + 60)\nand ws1.ws_ship_date_sk =
d_date_sk\nand ws1.ws_ship_addr_sk = ca_address_sk\nand ca_state = 'GA'\nand
ws1.ws_web_site_sk = web_site_sk\nand web_company_name = 'pri'\nand
ws1.ws_order_number in (select ws_order_number\n
from ws_wh)\nand ws1.ws_order_number in (select wr_order_number\n
from web_returns,ws_wh\n where
wr_order_number = ws_wh.ws_order_number)\norder by count(distinct
ws_order_number)\nlimit 100;\n",
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 1244768.73,
"Total Cost": 1244768.73,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 277228.286,
"Actual Total Time": 277228.294,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["(count(DISTINCT ws1.ws_order_number))",
"(sum(ws1.ws_ext_ship_cost))", "(sum(ws1.ws_net_profit))"],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE ws_wh",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 76039.92,
"Total Cost": 443675.28,
"Plan Rows": 17135538,
"Plan Width": 12,
"Actual Startup Time": 380.616,
"Actual Total Time": 3160.748,
"Actual Rows": 13341520,
"Actual Loops": 1,
"Output": ["ws1_1.ws_order_number", "ws1_1.ws_warehouse_sk",
"ws2.ws_warehouse_sk"],
"Inner Unique": false,
"Hash Cond": "(ws1_1.ws_order_number = ws2.ws_order_number)",
"Join Filter": "(ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk)",
"Rows Removed by Join Filter": 4787014,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws1_1",
"Startup Cost": 0.00,
"Total Cost": 52382.52,
"Plan Rows": 1441952,
"Plan Width": 8,
"Actual Startup Time": 0.013,
"Actual Total Time": 223.129,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws1_1.ws_order_number", "ws1_1.ws_warehouse_sk"]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 52382.52,
"Total Cost": 52382.52,
"Plan Rows": 1441952,
"Plan Width": 8,
"Actual Startup Time": 379.625,
"Actual Total Time": 379.626,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws2.ws_warehouse_sk", "ws2.ws_order_number"],
"Hash Buckets": 262144,
"Original Hash Buckets": 262144,
"Hash Batches": 16,
"Original Hash Batches": 16,
"Peak Memory Usage": 5602,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws2",
"Startup Cost": 0.00,
"Total Cost": 52382.52,
"Plan Rows": 1441952,
"Plan Width": 8,
"Actual Startup Time": 0.003,
"Actual Total Time": 224.155,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws2.ws_warehouse_sk", "ws2.ws_order_number"]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 801093.44,
"Total Cost": 801093.45,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 277228.286,
"Actual Total Time": 277228.290,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["(count(DISTINCT ws1.ws_order_number))",
"(sum(ws1.ws_ext_ship_cost))", "(sum(ws1.ws_net_profit))"],
"Sort Key": ["(count(DISTINCT ws1.ws_order_number))"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 801093.42,
"Total Cost": 801093.43,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 277228.262,
"Actual Total Time": 277228.266,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["count(DISTINCT ws1.ws_order_number)",
"sum(ws1.ws_ext_ship_cost)", "sum(ws1.ws_net_profit)"],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 801093.38,
"Total Cost": 801093.39,
"Plan Rows": 4,
"Plan Width": 16,
"Actual Startup Time": 277228.167,
"Actual Total Time": 277228.193,
"Actual Rows": 328,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number", "ws1.ws_ext_ship_cost",
"ws1.ws_net_profit"],
"Sort Key": ["ws1.ws_order_number"],
"Sort Method": "quicksort",
"Sort Space Used": 40,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Semi",
"Startup Cost": 385562.13,
"Total Cost": 801093.34,
"Plan Rows": 4,
"Plan Width": 16,
"Actual Startup Time": 8363.110,
"Actual Total Time": 277227.868,
"Actual Rows": 328,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number", "ws1.ws_ext_ship_cost",
"ws1.ws_net_profit"],
"Inner Unique": false,
"Join Filter": "(ws1.ws_order_number =
web_returns.wr_order_number)",
"Rows Removed by Join Filter": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 385561.65,
"Total Cost": 389224.84,
"Plan Rows": 4,
"Plan Width": 20,
"Actual Startup Time": 7282.926,
"Actual Total Time": 8739.741,
"Actual Rows": 458,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws_wh.ws_order_number"],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 385561.36,
"Total Cost": 389194.46,
"Plan Rows": 96,
"Plan Width": 24,
"Actual Startup Time": 7265.020,
"Actual Total Time": 8707.095,
"Actual Rows": 9100,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk",
"ws_wh.ws_order_number"],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 385561.21,
"Total Cost": 389175.49,
"Plan Rows": 572,
"Plan Width": 28,
"Actual Startup Time": 7265.005,
"Actual Total Time": 8691.756,
"Actual Rows": 45703,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk",
"ws1.ws_web_site_sk", "ws_wh.ws_order_number"],
"Inner Unique": true,
"Hash Cond": "(ws1.ws_ship_date_sk =
date_dim.d_date_sk)",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 385550.03,
"Total Cost": 387271.64,
"Plan Rows": 720976,
"Plan Width": 32,
"Actual Startup Time": 7264.812,
"Actual Total Time": 8603.045,
"Actual Rows": 1441940,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_date_sk",
"ws1.ws_ship_addr_sk", "ws1.ws_web_site_sk", "ws_wh.ws_order_number"],
"Inner Unique": false,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 385549.60,
"Total Cost": 385551.60,
"Plan Rows": 200,
"Plan Width": 4,
"Actual Startup Time": 7264.759,
"Actual Total Time": 7546.801,
"Actual Rows": 119999,
"Actual Loops": 1,
"Output": ["ws_wh.ws_order_number"],
"Group Key": ["ws_wh.ws_order_number"],
"Planned Partitions": 0,
"HashAgg Batches": 5,
"Peak Memory Usage": 10305,
"Disk Usage": 44896,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "ws_wh",
"Alias": "ws_wh",
"Startup Cost": 0.00,
"Total Cost": 342710.76,
"Plan Rows": 17135538,
"Plan Width": 4,
"Actual Startup Time": 380.618,
"Actual Total Time": 5522.500,
"Actual Rows": 13341520,
"Actual Loops": 1,
"Output": ["ws_wh.ws_order_number",
"ws_wh.wh1", "ws_wh.wh2"]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_ws_order_number",
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws1",
"Startup Cost": 0.43,
"Total Cost": 8.45,
"Plan Rows": 15,
"Plan Width": 28,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.006,
"Actual Rows": 12,
"Actual Loops": 119999,
"Output": ["ws1.ws_sold_date_sk",
"ws1.ws_sold_time_sk", "ws1.ws_ship_date_sk", "ws1.ws_item_sk",
"ws1.ws_bill_customer_sk", "ws1.ws_bill_cdemo_sk", "ws1.ws_bill_hdemo_sk",
"ws1.ws_bill_addr_sk", "ws1.ws_ship_customer_sk", "ws1.ws_ship_cdemo_sk",
"ws1.ws_ship_hdemo_sk", "ws1.ws_ship_addr_sk", "ws1.ws_web_page_sk",
"ws1.ws_web_site_sk", "ws1.ws_ship_mode_sk", "ws1.ws_warehouse_sk",
"ws1.ws_promo_sk", "ws1.ws_order_number", "ws1.ws_quantity",
"ws1.ws_wholesale_cost", "ws1.ws_list_price", "ws1.ws_sales_price",
"ws1.ws_ext_discount_amt", "ws1.ws_ext_sales_price",
"ws1.ws_ext_wholesale_cost", "ws1.ws_ext_list_price", "ws1.ws_ext_tax",
"ws1.ws_coupon_amt", "ws1.ws_ext_ship_cost", "ws1.ws_net_paid",
"ws1.ws_net_paid_inc_tax", "ws1.ws_net_paid_inc_ship",
"ws1.ws_net_paid_inc_ship_tax", "ws1.ws_net_profit"],
"Index Cond": "(ws1.ws_order_number =
ws_wh.ws_order_number)",
"Rows Removed by Index Recheck": 0
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 10.45,
"Total Cost": 10.45,
"Plan Rows": 58,
"Plan Width": 4,
"Actual Startup Time": 0.056,
"Actual Total Time": 0.056,
"Actual Rows": 61,
"Actual Loops": 1,
"Output": ["date_dim.d_date_sk"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 11,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_d_date",
"Relation Name": "date_dim",
"Schema": "public",
"Alias": "date_dim",
"Startup Cost": 0.29,
"Total Cost": 10.45,
"Plan Rows": 58,
"Plan Width": 4,
"Actual Startup Time": 0.024,
"Actual Total Time": 0.047,
"Actual Rows": 61,
"Actual Loops": 1,
"Output": ["date_dim.d_date_sk"],
"Index Cond": "((date_dim.d_date >=
'2000-03-01'::date) AND (date_dim.d_date <= '2000-04-30'::date))",
"Rows Removed by Index Recheck": 0
}
]
}
]
},
{
"Node Type": "Memoize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.15,
"Total Cost": 0.17,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 0,
"Actual Loops": 45703,
"Output": ["web_site.web_site_sk"],
"Cache Key": "ws1.ws_web_site_sk",
"Cache Mode": "logical",
"Cache Hits": 45682,
"Cache Misses": 21,
"Cache Evictions": 0,
"Cache Overflows": 0,
"Peak Memory Usage": 2,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "web_site_pkey",
"Relation Name": "web_site",
"Schema": "public",
"Alias": "web_site",
"Startup Cost": 0.14,
"Total Cost": 0.16,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 21,
"Output": ["web_site.web_site_sk"],
"Index Cond": "(web_site.web_site_sk =
ws1.ws_web_site_sk)",
"Rows Removed by Index Recheck": 0,
"Filter":
"((web_site.web_company_name)::text = 'pri'::text)",
"Rows Removed by Filter": 1
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "customer_address_pkey",
"Relation Name": "customer_address",
"Schema": "public",
"Alias": "customer_address",
"Startup Cost": 0.29,
"Total Cost": 0.32,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 0,
"Actual Loops": 9100,
"Output": ["customer_address.ca_address_sk",
"customer_address.ca_address_id", "customer_address.ca_street_number",
"customer_address.ca_street_name", "customer_address.ca_street_type",
"customer_address.ca_suite_number", "customer_address.ca_city",
"customer_address.ca_county", "customer_address.ca_state",
"customer_address.ca_zip", "customer_address.ca_country",
"customer_address.ca_gmt_offset", "customer_address.ca_location_type"],
"Index Cond": "(customer_address.ca_address_sk =
ws1.ws_ship_addr_sk)",
"Rows Removed by Index Recheck": 0,
"Filter": "((customer_address.ca_state)::text =
'GA'::text)",
"Rows Removed by Filter": 1
}
]
},
{
"Node Type": "Hash Join",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.48,
"Total Cost": 406975.58,
"Plan Rows": 486,
"Plan Width": 8,
"Actual Startup Time": 586.217,
"Actual Total Time": 586.217,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number",
"ws_wh_1.ws_order_number"],
"Inner Unique": false,
"Hash Cond": "(ws_wh_1.ws_order_number =
web_returns.wr_order_number)",
"Join Filter": "(ws_wh.ws_order_number =
ws_wh_1.ws_order_number)",
"Rows Removed by Join Filter": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "ws_wh",
"Alias": "ws_wh_1",
"Startup Cost": 0.00,
"Total Cost": 342710.76,
"Plan Rows": 17135538,
"Plan Width": 4,
"Actual Startup Time": 0.002,
"Actual Total Time": 450.907,
"Actual Rows": 6487821,
"Actual Loops": 329,
"Output": ["ws_wh_1.ws_order_number",
"ws_wh_1.wh1", "ws_wh_1.wh2"]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.46,
"Total Cost": 0.46,
"Plan Rows": 2,
"Plan Width": 4,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.006,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_wr_order_number",
"Relation Name": "web_returns",
"Schema": "public",
"Alias": "web_returns",
"Startup Cost": 0.42,
"Total Cost": 0.46,
"Plan Rows": 2,
"Plan Width": 4,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.005,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number"],
"Index Cond": "(web_returns.wr_order_number =
ws_wh.ws_order_number)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
}
][{
"Query Text": "-- RNGSEED: 1\n\n-- EXPLAIN (FORMAT JSON)\nwith ws_wh
as\n(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk
wh2\n from web_sales ws1,web_sales ws2\n where ws1.ws_order_number =
ws2.ws_order_number\n and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)\n
select\n count(distinct ws_order_number) as \"order count\"\n
,sum(ws_ext_ship_cost) as \"total shipping cost\"\n ,sum(ws_net_profit) as
\"total net profit\"\nfrom\n web_sales ws1\n ,date_dim\n
,customer_address\n ,web_site\nwhere\n d_date between '2000-3-01' and\n
(cast('2000-3-01' as date) + 60)\nand ws1.ws_ship_date_sk =
d_date_sk\nand ws1.ws_ship_addr_sk = ca_address_sk\nand ca_state = 'GA'\nand
ws1.ws_web_site_sk = web_site_sk\nand web_company_name = 'pri'\nand
ws1.ws_order_number in (select ws_order_number\n
from ws_wh)\nand ws1.ws_order_number in (select wr_order_number\n
from web_returns,ws_wh\n where
wr_order_number = ws_wh.ws_order_number)\norder by count(distinct
ws_order_number)\nlimit 100;\n",
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 2102152.19,
"Total Cost": 2102152.19,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 560220.258,
"Actual Total Time": 560220.349,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["(count(DISTINCT ws1.ws_order_number))",
"(sum(ws1.ws_ext_ship_cost))", "(sum(ws1.ws_net_profit))"],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE ws_wh",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 76038.35,
"Total Cost": 443943.65,
"Plan Rows": 17153293,
"Plan Width": 12,
"Actual Startup Time": 384.664,
"Actual Total Time": 3276.036,
"Actual Rows": 13330577,
"Actual Loops": 1,
"Output": ["ws1_1.ws_order_number", "ws1_1.ws_warehouse_sk",
"ws2.ws_warehouse_sk"],
"Inner Unique": false,
"Hash Cond": "(ws1_1.ws_order_number = ws2.ws_order_number)",
"Join Filter": "(ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk)",
"Rows Removed by Join Filter": 4782988,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws1_1",
"Startup Cost": 0.00,
"Total Cost": 52381.82,
"Plan Rows": 1441882,
"Plan Width": 8,
"Actual Startup Time": 0.006,
"Actual Total Time": 233.783,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws1_1.ws_order_number", "ws1_1.ws_warehouse_sk"]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 52381.82,
"Total Cost": 52381.82,
"Plan Rows": 1441882,
"Plan Width": 8,
"Actual Startup Time": 383.713,
"Actual Total Time": 383.714,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws2.ws_warehouse_sk", "ws2.ws_order_number"],
"Hash Buckets": 262144,
"Original Hash Buckets": 262144,
"Hash Batches": 16,
"Original Hash Batches": 16,
"Peak Memory Usage": 5602,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws2",
"Startup Cost": 0.00,
"Total Cost": 52381.82,
"Plan Rows": 1441882,
"Plan Width": 8,
"Actual Startup Time": 0.004,
"Actual Total Time": 227.843,
"Actual Rows": 1441948,
"Actual Loops": 1,
"Output": ["ws2.ws_warehouse_sk", "ws2.ws_order_number"]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 1658208.54,
"Total Cost": 1658208.55,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 560220.257,
"Actual Total Time": 560220.345,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["(count(DISTINCT ws1.ws_order_number))",
"(sum(ws1.ws_ext_ship_cost))", "(sum(ws1.ws_net_profit))"],
"Sort Key": ["(count(DISTINCT ws1.ws_order_number))"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 1658208.52,
"Total Cost": 1658208.53,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 560220.239,
"Actual Total Time": 560220.327,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["count(DISTINCT ws1.ws_order_number)",
"sum(ws1.ws_ext_ship_cost)", "sum(ws1.ws_net_profit)"],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Semi",
"Startup Cost": 46566.23,
"Total Cost": 1658208.46,
"Plan Rows": 7,
"Plan Width": 16,
"Actual Startup Time": 2898.574,
"Actual Total Time": 560219.538,
"Actual Rows": 328,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number", "ws1.ws_ext_ship_cost",
"ws1.ws_net_profit"],
"Inner Unique": false,
"Join Filter": "(ws1.ws_order_number = ws_wh.ws_order_number)",
"Rows Removed by Join Filter": 2132547425,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Semi",
"Startup Cost": 46566.23,
"Total Cost": 1282175.59,
"Plan Rows": 7,
"Plan Width": 24,
"Actual Startup Time": 2428.724,
"Actual Total Time": 273761.048,
"Actual Rows": 328,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number", "ws1.ws_ext_ship_cost",
"ws1.ws_net_profit", "web_returns.wr_order_number", "ws_wh_1.ws_order_number"],
"Inner Unique": false,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 46565.75,
"Total Cost": 46646.86,
"Plan Rows": 9,
"Plan Width": 16,
"Actual Startup Time": 96.622,
"Actual Total Time": 130.891,
"Actual Rows": 458,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit"],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Gather Merge",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 46565.46,
"Total Cost": 46587.35,
"Plan Rows": 188,
"Plan Width": 20,
"Actual Startup Time": 96.205,
"Actual Total Time": 98.290,
"Actual Rows": 9100,
"Actual Loops": 1,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk"],
"Workers Planned": 2,
"Workers Launched": 2,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 45565.43,
"Total Cost": 45565.63,
"Plan Rows": 78,
"Plan Width": 20,
"Actual Startup Time": 84.564,
"Actual Total Time": 84.753,
"Actual Rows": 3033,
"Actual Loops": 3,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk"],
"Sort Key": ["ws1.ws_order_number"],
"Sort Method": "quicksort",
"Sort Space Used": 252,
"Sort Space Type": "Memory",
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 81.455,
"Actual Total Time": 81.571,
"Actual Rows": 2927,
"Actual Loops": 1,
"Sort Method": "quicksort",
"Sort Space Used": 234,
"Sort Space Type": "Memory"
},
{
"Worker Number": 1,
"Actual Startup Time": 81.486,
"Actual Total Time": 81.601,
"Actual Rows": 2853,
"Actual Loops": 1,
"Sort Method": "quicksort",
"Sort Space Used": 230,
"Sort Space Type": "Memory"
}
],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 13.58,
"Total Cost": 45562.98,
"Plan Rows": 78,
"Plan Width": 20,
"Actual Startup Time": 0.207,
"Actual Total Time": 83.945,
"Actual Rows": 3033,
"Actual Loops": 3,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk"],
"Inner Unique": true,
"Hash Cond": "(ws1.ws_web_site_sk =
web_site.web_site_sk)",
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.245,
"Actual Total Time": 80.829,
"Actual Rows": 2927,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.242,
"Actual Total Time": 80.913,
"Actual Rows": 2853,
"Actual Loops": 1
}
],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 11.15,
"Total Cost": 45559.13,
"Plan Rows": 469,
"Plan Width": 24,
"Actual Startup Time": 0.106,
"Actual Total Time": 82.499,
"Actual Rows": 15234,
"Actual Loops": 3,
"Output": ["ws1.ws_order_number",
"ws1.ws_ext_ship_cost", "ws1.ws_net_profit", "ws1.ws_ship_addr_sk",
"ws1.ws_web_site_sk"],
"Inner Unique": true,
"Hash Cond": "(ws1.ws_ship_date_sk =
date_dim.d_date_sk)",
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.115,
"Actual Total Time": 79.393,
"Actual Rows": 14703,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.113,
"Actual Total Time": 79.513,
"Actual Rows": 14254,
"Actual Loops": 1
}
],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Async Capable": false,
"Relation Name": "web_sales",
"Schema": "public",
"Alias": "ws1",
"Startup Cost": 0.00,
"Total Cost": 43970.84,
"Plan Rows": 600784,
"Plan Width": 28,
"Actual Startup Time": 0.017,
"Actual Total Time": 48.751,
"Actual Rows": 480649,
"Actual Loops": 3,
"Output": ["ws1.ws_sold_date_sk",
"ws1.ws_sold_time_sk", "ws1.ws_ship_date_sk", "ws1.ws_item_sk",
"ws1.ws_bill_customer_sk", "ws1.ws_bill_cdemo_sk", "ws1.ws_bill_hdemo_sk",
"ws1.ws_bill_addr_sk", "ws1.ws_ship_customer_sk", "ws1.ws_ship_cdemo_sk",
"ws1.ws_ship_hdemo_sk", "ws1.ws_ship_addr_sk", "ws1.ws_web_page_sk",
"ws1.ws_web_site_sk", "ws1.ws_ship_mode_sk", "ws1.ws_warehouse_sk",
"ws1.ws_promo_sk", "ws1.ws_order_number", "ws1.ws_quantity",
"ws1.ws_wholesale_cost", "ws1.ws_list_price", "ws1.ws_sales_price",
"ws1.ws_ext_discount_amt", "ws1.ws_ext_sales_price",
"ws1.ws_ext_wholesale_cost", "ws1.ws_ext_list_price", "ws1.ws_ext_tax",
"ws1.ws_coupon_amt", "ws1.ws_ext_ship_cost", "ws1.ws_net_paid",
"ws1.ws_net_paid_inc_tax", "ws1.ws_net_paid_inc_ship",
"ws1.ws_net_paid_inc_ship_tax", "ws1.ws_net_profit"],
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.017,
"Actual Total Time": 46.621,
"Actual Rows": 464926,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.018,
"Actual Total Time": 47.649,
"Actual Rows": 453884,
"Actual Loops": 1
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 10.43,
"Total Cost": 10.43,
"Plan Rows": 57,
"Plan Width": 4,
"Actual Startup Time": 0.054,
"Actual Total Time": 0.055,
"Actual Rows": 61,
"Actual Loops": 3,
"Output": ["date_dim.d_date_sk"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 11,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.058,
"Actual Total Time": 0.059,
"Actual Rows": 61,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.052,
"Actual Total Time": 0.053,
"Actual Rows": 61,
"Actual Loops": 1
}
],
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_d_date",
"Relation Name": "date_dim",
"Schema": "public",
"Alias": "date_dim",
"Startup Cost": 0.29,
"Total Cost": 10.43,
"Plan Rows": 57,
"Plan Width": 4,
"Actual Startup Time": 0.026,
"Actual Total Time": 0.043,
"Actual Rows": 61,
"Actual Loops": 3,
"Output":
["date_dim.d_date_sk"],
"Index Cond":
"((date_dim.d_date >= '2000-03-01'::date) AND (date_dim.d_date <=
'2000-04-30'::date))",
"Rows Removed by Index
Recheck": 0,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time":
0.026,
"Actual Total Time": 0.042,
"Actual Rows": 61,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time":
0.028,
"Actual Total Time": 0.045,
"Actual Rows": 61,
"Actual Loops": 1
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 2.38,
"Total Cost": 2.38,
"Plan Rows": 5,
"Plan Width": 4,
"Actual Startup Time": 0.034,
"Actual Total Time": 0.035,
"Actual Rows": 5,
"Actual Loops": 3,
"Output": ["web_site.web_site_sk"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.033,
"Actual Total Time": 0.033,
"Actual Rows": 5,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.037,
"Actual Total Time": 0.037,
"Actual Rows": 5,
"Actual Loops": 1
}
],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "web_site",
"Schema": "public",
"Alias": "web_site",
"Startup Cost": 0.00,
"Total Cost": 2.38,
"Plan Rows": 5,
"Plan Width": 4,
"Actual Startup Time": 0.020,
"Actual Total Time": 0.031,
"Actual Rows": 5,
"Actual Loops": 3,
"Output": ["web_site.web_site_sk"],
"Filter":
"((web_site.web_company_name)::text = 'pri'::text)",
"Rows Removed by Filter": 25,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 0.018,
"Actual Total Time": 0.030,
"Actual Rows": 5,
"Actual Loops": 1
},
{
"Worker Number": 1,
"Actual Startup Time": 0.023,
"Actual Total Time": 0.034,
"Actual Rows": 5,
"Actual Loops": 1
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "customer_address_pkey",
"Relation Name": "customer_address",
"Schema": "public",
"Alias": "customer_address",
"Startup Cost": 0.29,
"Total Cost": 0.32,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 0,
"Actual Loops": 9100,
"Output": ["customer_address.ca_address_sk",
"customer_address.ca_address_id", "customer_address.ca_street_number",
"customer_address.ca_street_name", "customer_address.ca_street_type",
"customer_address.ca_suite_number", "customer_address.ca_city",
"customer_address.ca_county", "customer_address.ca_state",
"customer_address.ca_zip", "customer_address.ca_country",
"customer_address.ca_gmt_offset", "customer_address.ca_location_type"],
"Index Cond": "(customer_address.ca_address_sk =
ws1.ws_ship_addr_sk)",
"Rows Removed by Index Recheck": 0,
"Filter": "((customer_address.ca_state)::text =
'GA'::text)",
"Rows Removed by Filter": 1
}
]
},
{
"Node Type": "Hash Join",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.48,
"Total Cost": 407395.61,
"Plan Rows": 354,
"Plan Width": 8,
"Actual Startup Time": 597.444,
"Actual Total Time": 597.444,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number",
"ws_wh_1.ws_order_number"],
"Inner Unique": false,
"Hash Cond": "(ws_wh_1.ws_order_number =
web_returns.wr_order_number)",
"Join Filter": "(ws1.ws_order_number =
ws_wh_1.ws_order_number)",
"Rows Removed by Join Filter": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "ws_wh",
"Alias": "ws_wh_1",
"Startup Cost": 0.00,
"Total Cost": 343065.86,
"Plan Rows": 17153293,
"Plan Width": 4,
"Actual Startup Time": 1.174,
"Actual Total Time": 466.423,
"Actual Rows": 6501670,
"Actual Loops": 328,
"Output": ["ws_wh_1.ws_order_number",
"ws_wh_1.wh1", "ws_wh_1.wh2"]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.46,
"Total Cost": 0.46,
"Plan Rows": 2,
"Plan Width": 4,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.008,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_wr_order_number",
"Relation Name": "web_returns",
"Schema": "public",
"Alias": "web_returns",
"Startup Cost": 0.42,
"Total Cost": 0.46,
"Plan Rows": 2,
"Plan Width": 4,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.007,
"Actual Rows": 1,
"Actual Loops": 458,
"Output": ["web_returns.wr_order_number"],
"Index Cond": "(web_returns.wr_order_number =
ws1.ws_order_number)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0
}
]
}
]
}
]
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "ws_wh",
"Alias": "ws_wh",
"Startup Cost": 0.00,
"Total Cost": 343065.86,
"Plan Rows": 17153293,
"Plan Width": 4,
"Actual Startup Time": 0.001,
"Actual Total Time": 452.323,
"Actual Rows": 6501670,
"Actual Loops": 328,
"Output": ["ws_wh.ws_order_number", "ws_wh.wh1",
"ws_wh.wh2"]
}
]
}
]
}
]
}
]
}
}
]
