[{ "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"] } ] } ] } ] } ] } } ]