public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: David Rowley <[email protected]>
Cc: MARK CALLAGHAN <[email protected]>
Cc: [email protected]
Cc: Andres Freund <[email protected]>
Subject: Re: benchmark results comparing versions 15.2 and 16
Date: Thu, 11 May 2023 16:00:01 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvqeYkHJEkh0unC5OsJDDY9o2ZfwPFdQDFdqYxCYOXgLFw@mail.gmail.com>
References: <CAFbpF8N4bVFZ+eyWFj_+fZXZFSayk2kJVtZnouUVKGHudiDrSw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAApHDvqeYkHJEkh0unC5OsJDDY9o2ZfwPFdQDFdqYxCYOXgLFw@mail.gmail.com>
11.05.2023 01:27, David Rowley wrote:
> On Thu, 11 May 2023 at 01:00, Alexander Lakhin <[email protected]> wrote:
>> This time `git bisect` pointed at 3c6fc5820. Having compared execution plans
>> (both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820):
> Based on what you've sent, I'm uninspired to want to try to do
> anything about it. The patched version finds a plan that's cheaper.
> The row estimates are miles off with both plans.
I've made sure that s64da-benchmark performs analyze before running the
queries (pg_class.reltuples fields for tables in question contain actual
counts), so it seems that nothing can be done on the benchmark side to
improve those estimates.
> ... It's pretty hard to make changes to the
> planner's path generation without risking that a bad plan is chosen
> when it wasn't beforehand with bad row estimates.
Yeah, I see. It's also interesting to me, which tests perform better after
that commit. It takes several hours to run all tests, so I can't present
results quickly, but I'll try to collect this information next week.
> Is the new plan still slower if you increase work_mem so that the sort
> no longer goes to disk? Maybe the planner would have picked Hash
> Aggregate if the row estimates had been such that cost_tuplesort()
> knew that the sort would have gone to disk.
Yes, increasing work_mem to 50MB doesn't affect the plans (new plans
attached), though the sort method changed to quicksort. The former plan is
still executed slightly faster.
Best regards,
Alexander
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=254862.89..254862.90 rows=1 width=8) (actual time=2567.064..2581.619 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=147645.17..254820.81 rows=16834 width=0) (actual time=2551.091..2578.005 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=147645.17..254652.47 rows=16834 width=144) (actual time=2551.090..2569.217 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=147645.17..254481.44 rows=22804 width=144) (actual time=2162.417..2542.403 rows=117004 loops=1)
-> Result (cost=147645.17..202935.12 rows=16834 width=144) (actual time=2162.416..2200.221 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=147645.17..202766.78 rows=16834 width=144) (actual time=2162.414..2190.284 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=147645.17..202577.39 rows=25251 width=144) (actual time=1175.080..2146.489 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=147645.17..150001.93 rows=16834 width=21) (actual time=1175.079..1315.210 rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique (cost=147645.17..149833.59 rows=16834 width=17) (actual time=1175.075..1305.410 rows=93891 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Gather Merge (cost=147645.17..149707.33 rows=16834 width=17) (actual time=1175.075..1293.810 rows=94207 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned: 1
Workers Launched: 1
-> Unique (cost=146645.16..146813.50 rows=16834 width=17) (actual time=1171.845..1259.795 rows=47104 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Worker 0: actual time=1168.901..1257.819 rows=47011 loops=1
-> Sort (cost=146645.16..146687.24 rows=16834 width=17) (actual time=1171.842..1199.145 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Method: quicksort Memory: 41416kB
Worker 0: actual time=1168.898..1196.043 rows=533101 loops=1
Sort Method: quicksort Memory: 41393kB
-> Parallel Hash Join (cost=140711.42..145463.49 rows=16834 width=17) (actual time=612.177..710.988 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk)
Worker 0: actual time=611.023..709.758 rows=533101 loops=1
-> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.007..6.935 rows=72000 loops=2)
Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk
Worker 0: actual time=0.012..6.906 rows=72071 loops=1
-> Parallel Hash (cost=140562.37..140562.37 rows=11924 width=8) (actual time=611.999..612.003 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB
Worker 0: actual time=610.930..610.934 rows=548215 loops=1
-> Parallel Hash Join (cost=2570.23..140562.37 rows=11924 width=8) (actual time=5.025..509.830 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Inner Unique: true
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Worker 0: actual time=3.992..508.085 rows=548215 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131692.88 rows=2399588 width=8) (actual time=0.016..263.040 rows=2879322 loops=2)
Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit
Worker 0: actual time=0.018..262.235 rows=2882396 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=4.951..4.953 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 72kB
Worker 0: actual time=3.877..3.879 rows=137 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.164..4.905 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=1.096..3.834 rows=137 loops=1
-> Subquery Scan on "*SELECT* 2" (cost=51270.83..52449.21 rows=8417 width=21) (actual time=750.303..822.786 rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique (cost=51270.83..52365.04 rows=8417 width=17) (actual time=750.300..816.251 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Gather Merge (cost=51270.83..52301.92 rows=8417 width=17) (actual time=750.299..808.594 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Workers Planned: 1
Workers Launched: 1
-> Unique (cost=50270.82..50354.99 rows=8417 width=17) (actual time=720.125..765.136 rows=31372 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Worker 0: actual time=690.255..733.699 rows=29639 loops=1
-> Sort (cost=50270.82..50291.87 rows=8417 width=17) (actual time=720.123..732.766 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Method: quicksort Memory: 25655kB
Worker 0: actual time=690.253..702.224 rows=268480 loops=1
Sort Method: quicksort Memory: 24250kB
-> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=1.831..466.098 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Inner Unique: true
Worker 0: actual time=0.526..449.880 rows=268480 loops=1
-> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=1.811..91.963 rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.500..90.643 rows=269122 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.774..4.005 rows=182 loops=2)
Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year
Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=0.461..4.861 rows=175 loops=1
-> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.328 rows=1562 loops=365)
Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit
Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk)
Worker 0: actual time=0.004..0.333 rows=1538 loops=175
-> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105)
Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk
Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=269122
-> Subquery Scan on "*SELECT* 3" (cost=50608.89..51432.30 rows=5970 width=21) (actual time=307.757..335.810 rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50608.89..51372.60 rows=5970 width=17) (actual time=307.755..333.304 rows=23737 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50608.89..51327.82 rows=5970 width=17) (actual time=307.754..330.282 rows=24175 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Workers Planned: 2
Workers Launched: 2
-> Unique (cost=49608.86..49638.71 rows=2985 width=17) (actual time=303.752..318.599 rows=8058 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Worker 0: actual time=304.217..319.193 rows=8146 loops=1
Worker 1: actual time=300.725..315.145 rows=7821 loops=1
-> Sort (cost=49608.86..49616.33 rows=2985 width=17) (actual time=303.751..307.798 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Method: quicksort Memory: 7446kB
Worker 0: actual time=304.216..308.298 rows=96768 loops=1
Sort Method: quicksort Memory: 7377kB
Worker 1: actual time=300.724..304.578 rows=92852 loops=1
Sort Method: quicksort Memory: 7205kB
-> Nested Loop (cost=2570.65..49436.58 rows=2985 width=17) (actual time=3.708..232.553 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Inner Unique: true
Worker 0: actual time=2.743..231.575 rows=96768 loops=1
Worker 1: actual time=3.080..232.464 rows=92852 loops=1
-> Parallel Hash Join (cost=2570.23..48102.58 rows=2985 width=8) (actual time=3.688..99.912 rows=95936 loops=3)
Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date
Inner Unique: true
Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk)
Worker 0: actual time=2.717..98.228 rows=96786 loops=1
Worker 1: actual time=3.058..98.845 rows=92874 loops=1
-> Parallel Seq Scan on public.web_sales (cost=0.00..43955.13 rows=600813 width=8) (actual time=0.012..53.735 rows=480649 loops=3)
Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit
Worker 0: actual time=0.015..52.402 rows=481473 loops=1
Worker 1: actual time=0.014..54.245 rows=472826 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.508..3.509 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 104kB
Worker 0: actual time=2.634..2.635 rows=76 loops=1
Worker 1: actual time=2.932..2.933 rows=99 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.731..3.452 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187))
Rows Removed by Filter: 24228
Worker 0: actual time=0.853..2.568 rows=76 loops=1
Worker 1: actual time=1.155..2.874 rows=99 loops=1
-> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809)
Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk
Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=96786
Worker 1: actual time=0.001..0.001 rows=1 loops=92874
Planning Time: 2.935 ms
Execution Time: 2588.373 ms
(147 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=252319.25..252319.26 rows=1 width=8) (actual time=2185.816..2200.051 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=149504.61..252278.56 rows=16276 width=0) (actual time=2169.488..2196.223 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=149504.61..252115.80 rows=16276 width=144) (actual time=2169.487..2187.308 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=149504.61..251941.76 rows=23205 width=144) (actual time=1712.746..2160.576 rows=117004 loops=1)
-> Result (cost=149504.61..200338.87 rows=16276 width=144) (actual time=1712.746..1750.336 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=149504.61..200176.11 rows=16276 width=144) (actual time=1712.744..1740.325 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=149504.61..199992.99 rows=24415 width=144) (actual time=973.112..1698.406 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=149504.61..149830.13 rows=16276 width=21) (actual time=973.111..1013.840 rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique (cost=149504.61..149667.37 rows=16276 width=17) (actual time=973.109..1004.160 rows=93891 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Sort (cost=149504.61..149545.30 rows=16276 width=17) (actual time=973.108..992.133 rows=94197 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Method: quicksort Memory: 7280kB
-> Gather (cost=146575.70..148366.06 rows=16276 width=17) (actual time=820.555..850.480 rows=94197 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned: 1
Workers Launched: 1
-> HashAggregate (cost=145575.70..145738.46 rows=16276 width=17) (actual time=818.787..827.198 rows=47098 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Batches: 1 Memory Usage: 5649kB
Worker 0: actual time=817.326..826.427 rows=46986 loops=1
Batches: 1 Memory Usage: 5649kB
-> Parallel Hash Join (cost=140703.75..145453.63 rows=16276 width=17) (actual time=608.035..706.762 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk)
Worker 0: actual time=606.747..705.672 rows=531889 loops=1
-> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..7.236 rows=72000 loops=2)
Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk
Worker 0: actual time=0.010..7.344 rows=71598 loops=1
-> Parallel Hash (cost=140559.64..140559.64 rows=11529 width=8) (actual time=607.826..607.830 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB
Worker 0: actual time=606.664..606.666 rows=546687 loops=1
-> Parallel Hash Join (cost=2570.12..140559.64 rows=11529 width=8) (actual time=5.418..506.881 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Inner Unique: true
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Worker 0: actual time=4.257..506.966 rows=546687 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131690.80 rows=2399380 width=8) (actual time=0.019..257.782 rows=2879322 loops=2)
Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit
Worker 0: actual time=0.022..257.891 rows=2878318 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=5.371..5.371 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 72kB
Worker 0: actual time=4.204..4.205 rows=155 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=206 width=8) (actual time=2.189..5.321 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=1.028..4.157 rows=155 loops=1
-> Subquery Scan on "*SELECT* 2" (cost=49878.01..50040.79 rows=8139 width=21) (actual time=658.619..676.077 rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique (cost=49878.01..49959.40 rows=8139 width=17) (actual time=658.617..669.592 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Sort (cost=49878.01..49898.36 rows=8139 width=17) (actual time=658.616..661.616 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Method: quicksort Memory: 4341kB
-> Gather (cost=48454.07..49349.36 rows=8139 width=17) (actual time=561.197..571.678 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Workers Planned: 1
Workers Launched: 1
-> HashAggregate (cost=47454.07..47535.46 rows=8139 width=17) (actual time=535.356..541.636 rows=31372 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Batches: 1 Memory Usage: 3601kB
Worker 0: actual time=509.782..515.593 rows=29639 loops=1
Batches: 1 Memory Usage: 3601kB
-> Nested Loop (cost=0.85..47393.02 rows=8139 width=17) (actual time=1.863..466.491 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Inner Unique: true
Worker 0: actual time=0.549..444.467 rows=268480 loops=1
-> Nested Loop (cost=0.43..43793.90 rows=8139 width=8) (actual time=1.847..88.255 rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.527..85.258 rows=269122 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.809..3.772 rows=182 loops=2)
Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year
Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=0.489..4.356 rows=175 loops=1
-> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.53 rows=1560 width=8) (actual time=0.004..0.314 rows=1562 loops=365)
Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit
Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk)
Worker 0: actual time=0.004..0.315 rows=1538 loops=175
-> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105)
Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk
Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=269122
-> Subquery Scan on "*SELECT* 3" (cost=50558.61..51486.87 rows=6929 width=21) (actual time=308.103..403.898 rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50558.61..51417.58 rows=6929 width=17) (actual time=308.101..401.424 rows=23737 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50558.61..51365.61 rows=6929 width=17) (actual time=308.100..370.805 rows=287761 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=49558.59..49565.81 rows=2887 width=17) (actual time=304.699..310.446 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Method: quicksort Memory: 7444kB
Worker 0: actual time=304.767..309.513 rows=96821 loops=1
Sort Method: quicksort Memory: 7379kB
Worker 1: actual time=301.512..309.827 rows=92877 loops=1
Sort Method: quicksort Memory: 7205kB
-> Nested Loop (cost=2570.55..49392.65 rows=2887 width=17) (actual time=3.419..230.307 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Inner Unique: true
Worker 0: actual time=2.440..229.401 rows=96821 loops=1
Worker 1: actual time=2.704..230.216 rows=92877 loops=1
-> Parallel Hash Join (cost=2570.12..48102.45 rows=2887 width=8) (actual time=3.401..99.460 rows=95936 loops=3)
Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date
Inner Unique: true
Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk)
Worker 0: actual time=2.417..97.885 rows=96841 loops=1
Worker 1: actual time=2.685..97.490 rows=92890 loops=1
-> Parallel Seq Scan on public.web_sales (cost=0.00..43955.11 rows=600811 width=8) (actual time=0.016..53.317 rows=480649 loops=3)
Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit
Worker 0: actual time=0.019..52.418 rows=484786 loops=1
Worker 1: actual time=0.020..53.691 rows=460693 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=3.208..3.209 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 104kB
Worker 0: actual time=2.256..2.257 rows=152 loops=1
Worker 1: actual time=2.557..2.558 rows=76 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.291..3.153 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187))
Rows Removed by Filter: 24228
Worker 0: actual time=0.329..2.197 rows=152 loops=1
Worker 1: actual time=0.643..2.497 rows=76 loops=1
-> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809)
Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk
Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=96841
Worker 1: actual time=0.001..0.001 rows=1 loops=92890
Planning Time: 3.013 ms
Execution Time: 2203.990 ms
(145 rows)
Attachments:
[text/plain] tpcds-q87-3c6fc5820-wm50-plan.txt (26.0K, ../[email protected]/2-tpcds-q87-3c6fc5820-wm50-plan.txt)
download | inline:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=254862.89..254862.90 rows=1 width=8) (actual time=2567.064..2581.619 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=147645.17..254820.81 rows=16834 width=0) (actual time=2551.091..2578.005 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=147645.17..254652.47 rows=16834 width=144) (actual time=2551.090..2569.217 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=147645.17..254481.44 rows=22804 width=144) (actual time=2162.417..2542.403 rows=117004 loops=1)
-> Result (cost=147645.17..202935.12 rows=16834 width=144) (actual time=2162.416..2200.221 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=147645.17..202766.78 rows=16834 width=144) (actual time=2162.414..2190.284 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=147645.17..202577.39 rows=25251 width=144) (actual time=1175.080..2146.489 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=147645.17..150001.93 rows=16834 width=21) (actual time=1175.079..1315.210 rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique (cost=147645.17..149833.59 rows=16834 width=17) (actual time=1175.075..1305.410 rows=93891 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Gather Merge (cost=147645.17..149707.33 rows=16834 width=17) (actual time=1175.075..1293.810 rows=94207 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned: 1
Workers Launched: 1
-> Unique (cost=146645.16..146813.50 rows=16834 width=17) (actual time=1171.845..1259.795 rows=47104 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Worker 0: actual time=1168.901..1257.819 rows=47011 loops=1
-> Sort (cost=146645.16..146687.24 rows=16834 width=17) (actual time=1171.842..1199.145 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Method: quicksort Memory: 41416kB
Worker 0: actual time=1168.898..1196.043 rows=533101 loops=1
Sort Method: quicksort Memory: 41393kB
-> Parallel Hash Join (cost=140711.42..145463.49 rows=16834 width=17) (actual time=612.177..710.988 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk)
Worker 0: actual time=611.023..709.758 rows=533101 loops=1
-> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.007..6.935 rows=72000 loops=2)
Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk
Worker 0: actual time=0.012..6.906 rows=72071 loops=1
-> Parallel Hash (cost=140562.37..140562.37 rows=11924 width=8) (actual time=611.999..612.003 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB
Worker 0: actual time=610.930..610.934 rows=548215 loops=1
-> Parallel Hash Join (cost=2570.23..140562.37 rows=11924 width=8) (actual time=5.025..509.830 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Inner Unique: true
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Worker 0: actual time=3.992..508.085 rows=548215 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131692.88 rows=2399588 width=8) (actual time=0.016..263.040 rows=2879322 loops=2)
Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit
Worker 0: actual time=0.018..262.235 rows=2882396 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=4.951..4.953 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 72kB
Worker 0: actual time=3.877..3.879 rows=137 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.164..4.905 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=1.096..3.834 rows=137 loops=1
-> Subquery Scan on "*SELECT* 2" (cost=51270.83..52449.21 rows=8417 width=21) (actual time=750.303..822.786 rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique (cost=51270.83..52365.04 rows=8417 width=17) (actual time=750.300..816.251 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Gather Merge (cost=51270.83..52301.92 rows=8417 width=17) (actual time=750.299..808.594 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Workers Planned: 1
Workers Launched: 1
-> Unique (cost=50270.82..50354.99 rows=8417 width=17) (actual time=720.125..765.136 rows=31372 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Worker 0: actual time=690.255..733.699 rows=29639 loops=1
-> Sort (cost=50270.82..50291.87 rows=8417 width=17) (actual time=720.123..732.766 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Method: quicksort Memory: 25655kB
Worker 0: actual time=690.253..702.224 rows=268480 loops=1
Sort Method: quicksort Memory: 24250kB
-> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=1.831..466.098 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Inner Unique: true
Worker 0: actual time=0.526..449.880 rows=268480 loops=1
-> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=1.811..91.963 rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.500..90.643 rows=269122 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.774..4.005 rows=182 loops=2)
Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year
Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=0.461..4.861 rows=175 loops=1
-> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.328 rows=1562 loops=365)
Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit
Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk)
Worker 0: actual time=0.004..0.333 rows=1538 loops=175
-> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105)
Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk
Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=269122
-> Subquery Scan on "*SELECT* 3" (cost=50608.89..51432.30 rows=5970 width=21) (actual time=307.757..335.810 rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50608.89..51372.60 rows=5970 width=17) (actual time=307.755..333.304 rows=23737 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50608.89..51327.82 rows=5970 width=17) (actual time=307.754..330.282 rows=24175 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Workers Planned: 2
Workers Launched: 2
-> Unique (cost=49608.86..49638.71 rows=2985 width=17) (actual time=303.752..318.599 rows=8058 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Worker 0: actual time=304.217..319.193 rows=8146 loops=1
Worker 1: actual time=300.725..315.145 rows=7821 loops=1
-> Sort (cost=49608.86..49616.33 rows=2985 width=17) (actual time=303.751..307.798 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Method: quicksort Memory: 7446kB
Worker 0: actual time=304.216..308.298 rows=96768 loops=1
Sort Method: quicksort Memory: 7377kB
Worker 1: actual time=300.724..304.578 rows=92852 loops=1
Sort Method: quicksort Memory: 7205kB
-> Nested Loop (cost=2570.65..49436.58 rows=2985 width=17) (actual time=3.708..232.553 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Inner Unique: true
Worker 0: actual time=2.743..231.575 rows=96768 loops=1
Worker 1: actual time=3.080..232.464 rows=92852 loops=1
-> Parallel Hash Join (cost=2570.23..48102.58 rows=2985 width=8) (actual time=3.688..99.912 rows=95936 loops=3)
Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date
Inner Unique: true
Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk)
Worker 0: actual time=2.717..98.228 rows=96786 loops=1
Worker 1: actual time=3.058..98.845 rows=92874 loops=1
-> Parallel Seq Scan on public.web_sales (cost=0.00..43955.13 rows=600813 width=8) (actual time=0.012..53.735 rows=480649 loops=3)
Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit
Worker 0: actual time=0.015..52.402 rows=481473 loops=1
Worker 1: actual time=0.014..54.245 rows=472826 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.508..3.509 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 104kB
Worker 0: actual time=2.634..2.635 rows=76 loops=1
Worker 1: actual time=2.932..2.933 rows=99 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.731..3.452 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187))
Rows Removed by Filter: 24228
Worker 0: actual time=0.853..2.568 rows=76 loops=1
Worker 1: actual time=1.155..2.874 rows=99 loops=1
-> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809)
Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk
Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=96786
Worker 1: actual time=0.001..0.001 rows=1 loops=92874
Planning Time: 2.935 ms
Execution Time: 2588.373 ms
(147 rows)
[text/plain] tpcds-q87-e5b8a4c09-wm50-plan.txt (25.5K, ../[email protected]/3-tpcds-q87-e5b8a4c09-wm50-plan.txt)
download | inline:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=252319.25..252319.26 rows=1 width=8) (actual time=2185.816..2200.051 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=149504.61..252278.56 rows=16276 width=0) (actual time=2169.488..2196.223 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=149504.61..252115.80 rows=16276 width=144) (actual time=2169.487..2187.308 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=149504.61..251941.76 rows=23205 width=144) (actual time=1712.746..2160.576 rows=117004 loops=1)
-> Result (cost=149504.61..200338.87 rows=16276 width=144) (actual time=1712.746..1750.336 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=149504.61..200176.11 rows=16276 width=144) (actual time=1712.744..1740.325 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=149504.61..199992.99 rows=24415 width=144) (actual time=973.112..1698.406 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=149504.61..149830.13 rows=16276 width=21) (actual time=973.111..1013.840 rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique (cost=149504.61..149667.37 rows=16276 width=17) (actual time=973.109..1004.160 rows=93891 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Sort (cost=149504.61..149545.30 rows=16276 width=17) (actual time=973.108..992.133 rows=94197 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Sort Method: quicksort Memory: 7280kB
-> Gather (cost=146575.70..148366.06 rows=16276 width=17) (actual time=820.555..850.480 rows=94197 loops=1)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned: 1
Workers Launched: 1
-> HashAggregate (cost=145575.70..145738.46 rows=16276 width=17) (actual time=818.787..827.198 rows=47098 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date
Batches: 1 Memory Usage: 5649kB
Worker 0: actual time=817.326..826.427 rows=46986 loops=1
Batches: 1 Memory Usage: 5649kB
-> Parallel Hash Join (cost=140703.75..145453.63 rows=16276 width=17) (actual time=608.035..706.762 rows=533434 loops=2)
Output: customer.c_last_name, customer.c_first_name, date_dim.d_date
Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk)
Worker 0: actual time=606.747..705.672 rows=531889 loops=1
-> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..7.236 rows=72000 loops=2)
Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk
Worker 0: actual time=0.010..7.344 rows=71598 loops=1
-> Parallel Hash (cost=140559.64..140559.64 rows=11529 width=8) (actual time=607.826..607.830 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB
Worker 0: actual time=606.664..606.666 rows=546687 loops=1
-> Parallel Hash Join (cost=2570.12..140559.64 rows=11529 width=8) (actual time=5.418..506.881 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Inner Unique: true
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Worker 0: actual time=4.257..506.966 rows=546687 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131690.80 rows=2399380 width=8) (actual time=0.019..257.782 rows=2879322 loops=2)
Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit
Worker 0: actual time=0.022..257.891 rows=2878318 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=5.371..5.371 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 72kB
Worker 0: actual time=4.204..4.205 rows=155 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=206 width=8) (actual time=2.189..5.321 rows=182 loops=2)
Output: date_dim.d_date, date_dim.d_date_sk
Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=1.028..4.157 rows=155 loops=1
-> Subquery Scan on "*SELECT* 2" (cost=49878.01..50040.79 rows=8139 width=21) (actual time=658.619..676.077 rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique (cost=49878.01..49959.40 rows=8139 width=17) (actual time=658.617..669.592 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Sort (cost=49878.01..49898.36 rows=8139 width=17) (actual time=658.616..661.616 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Sort Method: quicksort Memory: 4341kB
-> Gather (cost=48454.07..49349.36 rows=8139 width=17) (actual time=561.197..571.678 rows=62744 loops=1)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Workers Planned: 1
Workers Launched: 1
-> HashAggregate (cost=47454.07..47535.46 rows=8139 width=17) (actual time=535.356..541.636 rows=31372 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Batches: 1 Memory Usage: 3601kB
Worker 0: actual time=509.782..515.593 rows=29639 loops=1
Batches: 1 Memory Usage: 3601kB
-> Nested Loop (cost=0.85..47393.02 rows=8139 width=17) (actual time=1.863..466.491 rows=284349 loops=2)
Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Inner Unique: true
Worker 0: actual time=0.549..444.467 rows=268480 loops=1
-> Nested Loop (cost=0.43..43793.90 rows=8139 width=8) (actual time=1.847..88.255 rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.527..85.258 rows=269122 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.809..3.772 rows=182 loops=2)
Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year
Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187))
Rows Removed by Filter: 36342
Worker 0: actual time=0.489..4.356 rows=175 loops=1
-> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.53 rows=1560 width=8) (actual time=0.004..0.314 rows=1562 loops=365)
Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit
Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk)
Worker 0: actual time=0.004..0.315 rows=1538 loops=175
-> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105)
Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk
Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=269122
-> Subquery Scan on "*SELECT* 3" (cost=50558.61..51486.87 rows=6929 width=21) (actual time=308.103..403.898 rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50558.61..51417.58 rows=6929 width=17) (actual time=308.101..401.424 rows=23737 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50558.61..51365.61 rows=6929 width=17) (actual time=308.100..370.805 rows=287761 loops=1)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=49558.59..49565.81 rows=2887 width=17) (actual time=304.699..310.446 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Sort Method: quicksort Memory: 7444kB
Worker 0: actual time=304.767..309.513 rows=96821 loops=1
Sort Method: quicksort Memory: 7379kB
Worker 1: actual time=301.512..309.827 rows=92877 loops=1
Sort Method: quicksort Memory: 7205kB
-> Nested Loop (cost=2570.55..49392.65 rows=2887 width=17) (actual time=3.419..230.307 rows=95920 loops=3)
Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date
Inner Unique: true
Worker 0: actual time=2.440..229.401 rows=96821 loops=1
Worker 1: actual time=2.704..230.216 rows=92877 loops=1
-> Parallel Hash Join (cost=2570.12..48102.45 rows=2887 width=8) (actual time=3.401..99.460 rows=95936 loops=3)
Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date
Inner Unique: true
Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk)
Worker 0: actual time=2.417..97.885 rows=96841 loops=1
Worker 1: actual time=2.685..97.490 rows=92890 loops=1
-> Parallel Seq Scan on public.web_sales (cost=0.00..43955.11 rows=600811 width=8) (actual time=0.016..53.317 rows=480649 loops=3)
Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit
Worker 0: actual time=0.019..52.418 rows=484786 loops=1
Worker 1: actual time=0.020..53.691 rows=460693 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=3.208..3.209 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Buckets: 1024 Batches: 1 Memory Usage: 104kB
Worker 0: actual time=2.256..2.257 rows=152 loops=1
Worker 1: actual time=2.557..2.558 rows=76 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.291..3.153 rows=122 loops=3)
Output: date_dim_2.d_date, date_dim_2.d_date_sk
Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187))
Rows Removed by Filter: 24228
Worker 0: actual time=0.329..2.197 rows=152 loops=1
Worker 1: actual time=0.643..2.497 rows=76 loops=1
-> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809)
Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk
Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk)
Worker 0: actual time=0.001..0.001 rows=1 loops=96841
Worker 1: actual time=0.001..0.001 rows=1 loops=92890
Planning Time: 3.013 ms
Execution Time: 2203.990 ms
(145 rows)
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: benchmark results comparing versions 15.2 and 16
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox