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