11.05.2023 01:27, David Rowley wrote:
On Thu, 11 May 2023 at 01:00, Alexander Lakhin <exclus...@gmail.com> 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)
