08.05.2023 16:00, Alexander Lakhin wrote:
... Having compared 15.3 (56e869a09) with master
(58f5edf84) I haven't seen significant regressions except a few minor ones.
First regression observed with a simple pgbench test:
Another noticeable, but not critical performance degradation is revealed by
query 87 from TPC-DS (I use s64da-benchmark):
https://github.com/swarm64/s64da-benchmark-toolkit/blob/master/benchmarks/tpcds/queries/queries_10/87.sql
With `prepare_benchmark --scale-factor=2`, `run_benchmark --scale-factor=10`
I get on master:
2023-05-10 09:27:52,888 INFO : finished 80/103: query 87 of stream 0: 2.26s
OK
but on REL_15_STABLE:
2023-05-10 08:13:40,648 INFO : finished 80/103: query 87 of stream 0: 1.94s
OK
This time `git bisect` pointed at 3c6fc5820. Having compared execution plans
(both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820):
-> Subquery Scan on "*SELECT* 1" (cost=149622.00..149958.68 rows=16834 width=21) (actual time=1018.606..1074.468
rows=93891 loops=1)
-> Unique (cost=149622.00..149790.34 rows=16834 width=17) (actual
time=1018.604..1064.790 rows=93891 loops=1)
-> Sort (cost=149622.00..149664.09 rows=16834 width=17) (actual
time=1018.603..1052.591 rows=94199 loops=1)
-> Gather (cost=146588.59..148440.33 rows=16834 width=17) (actual
time=880.899..913.978 rows=94199 loops=1)
vs
-> Subquery Scan on "*SELECT* 1" (cost=147576.79..149829.53 rows=16091 width=21) (actual time=1126.489..1366.751
rows=93891 loops=1)
-> Unique (cost=147576.79..149668.62 rows=16091 width=17) (actual
time=1126.487..1356.938 rows=93891 loops=1)
-> Gather Merge (cost=147576.79..149547.94 rows=16091 width=17) (actual
time=1126.487..1345.253 rows=94204 loops=1)
-> Unique (cost=146576.78..146737.69 rows=16091 width=17) (actual
time=1124.426..1306.532 rows=47102 loops=2)
-> Sort (cost=146576.78..146617.01 rows=16091 width=17) (actual
time=1124.424..1245.110 rows=533434 loops=2)
-> Subquery Scan on "*SELECT* 2" (cost=52259.82..52428.16 rows=8417 width=21) (actual time=653.640..676.879 rows=62744
loops=1)
-> Unique (cost=52259.82..52343.99 rows=8417 width=17) (actual
time=653.639..670.405 rows=62744 loops=1)
-> Sort (cost=52259.82..52280.86 rows=8417 width=17) (actual
time=653.637..662.428 rows=62744 loops=1)
-> Gather (cost=50785.20..51711.07 rows=8417 width=17) (actual
time=562.158..571.737 rows=62744 loops=1)
-> HashAggregate (cost=49785.20..49869.37 rows=8417 width=17) (actual
time=538.263..544.336 rows=31372 loops=2)
-> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual
time=2.049..469.747 rows=284349 loops=2)
vs
-> Subquery Scan on "*SELECT* 2" (cost=48503.68..49630.12 rows=8046 width=21) (actual time=700.050..828.388 rows=62744
loops=1)
-> Unique (cost=48503.68..49549.66 rows=8046 width=17) (actual
time=700.047..821.836 rows=62744 loops=1)
-> Gather Merge (cost=48503.68..49489.31 rows=8046 width=17) (actual
time=700.047..814.136 rows=62744 loops=1)
-> Unique (cost=47503.67..47584.13 rows=8046 width=17) (actual
time=666.348..763.403 rows=31372 loops=2)
-> Sort (cost=47503.67..47523.78 rows=8046 width=17) (actual
time=666.347..730.336 rows=284349 loops=2)
-> Nested Loop (cost=0.85..46981.72 rows=8046 width=17) (actual
time=1.852..454.111 rows=284349 loops=2)
-> Subquery Scan on "*SELECT* 3" (cost=50608.83..51568.70 rows=7165 width=21) (actual time=302.571..405.305 rows=23737
loops=1)
-> Unique (cost=50608.83..51497.05 rows=7165 width=17) (actual
time=302.568..402.818 rows=23737 loops=1)
-> Gather Merge (cost=50608.83..51443.31 rows=7165 width=17) (actual
time=302.567..372.246 rows=287761 loops=1)
-> Sort (cost=49608.81..49616.27 rows=2985 width=17) (actual
time=298.204..310.075 rows=95920 loops=3)
-> Nested Loop (cost=2570.65..49436.52 rows=2985 width=17) (actual
time=3.205..229.192 rows=95920 loops=3)
vs
-> Subquery Scan on "*SELECT* 3" (cost=50541.84..51329.11 rows=5708 width=21) (actual time=302.042..336.820 rows=23737
loops=1)
-> Unique (cost=50541.84..51272.03 rows=5708 width=17) (actual
time=302.039..334.329 rows=23737 loops=1)
-> Gather Merge (cost=50541.84..51229.22 rows=5708 width=17) (actual
time=302.039..331.296 rows=24128 loops=1)
-> Unique (cost=49541.81..49570.35 rows=2854 width=17) (actual
time=298.771..320.560 rows=8043 loops=3)
-> Sort (cost=49541.81..49548.95 rows=2854 width=17) (actual
time=298.770..309.603 rows=95920 loops=3)
-> Nested Loop (cost=2570.52..49378.01 rows=2854 width=17) (actual
time=3.209..230.291 rows=95920 loops=3)
From the commit message and the discussion [1], it's not clear to me, whether
this plan change is expected. Perhaps it's too minor issue to bring attention
to, but maybe this information could be useful for v16 performance analysis.
[1]
https://postgr.es/m/CAApHDvo8Lz2H=42urbbfp65ltceuoh288mt7dsg2_ewtw1a...@mail.gmail.com
Best regards,
Alexander
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=251725.01..251725.02 rows=1 width=8) (actual
time=2617.632..2636.767 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=147576.79..251684.78 rows=16091
width=0) (actual time=2601.794..2633.189 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=147576.79..251523.87 rows=16091 width=144)
(actual time=2601.792..2624.473 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name,
"*SELECT* 1".d_date, (0)
-> Append (cost=147576.79..251360.38 rows=21799 width=144)
(actual time=2213.850..2598.501 rows=117004 loops=1)
-> Result (cost=147576.79..199922.27 rows=16091
width=144) (actual time=2213.850..2255.342 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT*
1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=147576.79..199761.36
rows=16091 width=144) (actual time=2213.848..2245.447 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT*
1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=147576.79..199580.33
rows=24137 width=144) (actual time=1126.490..2203.619 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1"
(cost=147576.79..149829.53 rows=16091 width=21) (actual time=1126.489..1366.751
rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name,
"*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique
(cost=147576.79..149668.62 rows=16091 width=17) (actual time=1126.487..1356.938
rows=93891 loops=1)
Output:
customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Gather Merge
(cost=147576.79..149547.94 rows=16091 width=17) (actual time=1126.487..1345.253
rows=94204 loops=1)
Output:
customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned: 1
Workers Launched: 1
-> Unique
(cost=146576.78..146737.69 rows=16091 width=17) (actual time=1124.426..1306.532
rows=47102 loops=2)
Output:
customer.c_last_name, customer.c_first_name, date_dim.d_date
Worker 0:
actual time=1122.677..1304.272 rows=47025 loops=1
-> Sort
(cost=146576.78..146617.01 rows=16091 width=17) (actual time=1124.424..1245.110
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: external merge Disk: 15176kB
Worker 0:
actual time=1122.675..1241.764 rows=532473 loops=1
Sort
Method: external merge Disk: 15136kB
->
Parallel Hash Join (cost=140703.38..145452.51 rows=16091 width=17) (actual
time=644.066..761.474 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=647.901..758.829 rows=532473 loops=1
->
Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17)
(actual time=0.009..6.282 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.009..6.398 rows=71611 loops=1
->
Parallel Hash (cost=140560.90..140560.90 rows=11398 width=8) (actual
time=617.582..617.586 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage:
7360kB
Worker 0: actual time=616.651..616.654 rows=547248 loops=1
-> Parallel Hash Join (cost=2570.10..140560.90 rows=11398 width=8) (actual
time=5.390..508.118 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.456..507.951 rows=547248 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131691.82
rows=2399482 width=8) (actual time=0.018..260.792 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.020..261.441 rows=2887593 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=204 width=8) (actual
time=5.352..5.353 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.409..4.410 rows=152 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55
rows=204 width=8) (actual time=2.560..5.299 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.621..4.358 rows=152 loops=1
-> Subquery Scan on "*SELECT* 2"
(cost=48503.68..49630.12 rows=8046 width=21) (actual time=700.050..828.388
rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name,
"*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique
(cost=48503.68..49549.66 rows=8046 width=17) (actual time=700.047..821.836
rows=62744 loops=1)
Output:
customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Gather Merge
(cost=48503.68..49489.31 rows=8046 width=17) (actual time=700.047..814.136
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=47503.67..47584.13 rows=8046 width=17) (actual time=666.348..763.403
rows=31372 loops=2)
Output:
customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
Worker 0:
actual time=632.937..724.548 rows=29426 loops=1
-> Sort
(cost=47503.67..47523.78 rows=8046 width=17) (actual time=666.347..730.336
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: external merge Disk: 8576kB
Worker 0:
actual time=632.936..692.503 rows=266513 loops=1
Sort
Method: external merge Disk: 7576kB
-> Nested
Loop (cost=0.85..46981.72 rows=8046 width=17) (actual time=1.852..454.111
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.681..433.435 rows=266513 loops=1
->
Nested Loop (cost=0.43..43423.72 rows=8046 width=8) (actual time=1.829..88.512
rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.648..85.747 rows=267146 loops=1
-> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55
rows=204 width=8) (actual time=1.789..3.883 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.597..4.725 rows=172 loops=1
-> Index Scan using idx_cs_sold_date_sk on public.catalog_sales
(cost=0.43..184.68 rows=1560 width=8) (actual time=0.004..0.317 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.323 rows=1553 loops=172
->
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=267146
-> Subquery Scan on "*SELECT* 3"
(cost=50541.84..51329.11 rows=5708 width=21) (actual time=302.042..336.820
rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT*
3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50541.84..51272.03 rows=5708
width=17) (actual time=302.039..334.329 rows=23737 loops=1)
Output: customer_2.c_last_name,
customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50541.84..51229.22
rows=5708 width=17) (actual time=302.039..331.296 rows=24128 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=49541.81..49570.35
rows=2854 width=17) (actual time=298.771..320.560 rows=8043 loops=3)
Output: customer_2.c_last_name,
customer_2.c_first_name, date_dim_2.d_date
Worker 0: actual
time=297.820..319.743 rows=7997 loops=1
Worker 1: actual
time=296.746..318.198 rows=7891 loops=1
-> Sort (cost=49541.81..49548.95
rows=2854 width=17) (actual time=298.770..309.603 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: external merge
Disk: 2792kB
Worker 0: actual
time=297.818..308.737 rows=95675 loops=1
Sort Method: external
merge Disk: 2720kB
Worker 1: actual
time=296.745..307.315 rows=93910 loops=1
Sort Method: external
merge Disk: 2664kB
-> Nested Loop
(cost=2570.52..49378.01 rows=2854 width=17) (actual time=3.209..230.291
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.294..228.843 rows=95675 loops=1
Worker 1: actual
time=2.670..230.164 rows=93910 loops=1
-> Parallel Hash Join
(cost=2570.10..48102.56 rows=2854 width=8) (actual time=3.191..99.159
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.270..97.201 rows=95690 loops=1
Worker 1:
actual time=2.653..98.648 rows=93924 loops=1
-> Parallel Seq
Scan on public.web_sales (cost=0.00..43955.21 rows=600821 width=8) (actual
time=0.017..53.280 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.023..52.210 rows=480453 loops=1
Worker 1:
actual time=0.020..54.103 rows=473734 loops=1
-> Parallel
Hash (cost=2567.55..2567.55 rows=204 width=8) (actual time=3.032..3.032
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.170..2.171 rows=76 loops=1
Worker 1:
actual time=2.559..2.560 rows=96 loops=1
->
Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=204
width=8) (actual time=1.329..2.976 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.464..2.104 rows=76 loops=1
Worker 1: actual time=0.857..2.506 rows=96 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=95690
Worker 1:
actual time=0.001..0.001 rows=1 loops=93924
Planning Time: 2.926 ms
Execution Time: 2642.408 ms
(147 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=254949.93..254949.94 rows=1 width=8) (actual
time=2242.324..2259.657 rows=1 loops=1)
Output: count(*)
-> Subquery Scan on cool_cust (cost=149622.00..254907.85 rows=16834
width=0) (actual time=2226.567..2256.123 rows=93140 loops=1)
Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date
-> HashSetOp Except (cost=149622.00..254739.51 rows=16834 width=144)
(actual time=2226.566..2247.422 rows=93140 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name,
"*SELECT* 1".d_date, (0)
-> Append (cost=149622.00..254559.51 rows=23999 width=144)
(actual time=1771.245..2221.971 rows=117004 loops=1)
-> Result (cost=149622.00..202870.82 rows=16834
width=144) (actual time=1771.245..1810.319 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT*
1".c_first_name, "*SELECT* 1".d_date, 0
-> HashSetOp Except (cost=149622.00..202702.48
rows=16834 width=144) (actual time=1771.244..1800.321 rows=93267 loops=1)
Output: "*SELECT* 1".c_last_name, "*SELECT*
1".c_first_name, "*SELECT* 1".d_date, (0)
-> Append (cost=149622.00..202513.10
rows=25251 width=144) (actual time=1018.607..1759.828 rows=156635 loops=1)
-> Subquery Scan on "*SELECT* 1"
(cost=149622.00..149958.68 rows=16834 width=21) (actual time=1018.606..1074.468
rows=93891 loops=1)
Output: "*SELECT* 1".c_last_name,
"*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0
-> Unique
(cost=149622.00..149790.34 rows=16834 width=17) (actual time=1018.604..1064.790
rows=93891 loops=1)
Output:
customer.c_last_name, customer.c_first_name, date_dim.d_date
-> Sort
(cost=149622.00..149664.09 rows=16834 width=17) (actual time=1018.603..1052.591
rows=94199 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: external
merge Disk: 2696kB
-> Gather
(cost=146588.59..148440.33 rows=16834 width=17) (actual time=880.899..913.978
rows=94199 loops=1)
Output:
customer.c_last_name, customer.c_first_name, date_dim.d_date
Workers Planned:
1
Workers
Launched: 1
->
HashAggregate (cost=145588.59..145756.93 rows=16834 width=17) (actual
time=879.119..887.352 rows=47100 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: 4625kB
Worker 0:
actual time=877.609..886.041 rows=44455 loops=1
Batches:
1 Memory Usage: 4369kB
->
Parallel Hash Join (cost=140710.34..145462.34 rows=16834 width=17) (actual
time=644.707..767.476 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=650.882..772.536 rows=503226 loops=1
->
Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17)
(actual time=0.009..6.004 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.009..6.171 rows=70942 loops=1
->
Parallel Hash (cost=140561.29..140561.29 rows=11924 width=8) (actual
time=615.499..615.504 rows=546248 loops=2)
Output: store_sales.ss_customer_sk, date_dim.d_date
Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage:
7360kB
Worker 0: actual time=614.329..614.333 rows=542130 loops=1
-> Parallel Hash Join (cost=2570.23..140561.29 rows=11924 width=8) (actual
time=5.364..506.303 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.176..503.249 rows=542130 loops=1
-> Parallel Seq Scan on public.store_sales (cost=0.00..131692.02
rows=2399502 width=8) (actual time=0.017..258.551 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.021..258.112 rows=2861258 loops=1
-> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual
time=5.302..5.303 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.121..4.122 rows=172 loops=1
-> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55
rows=214 width=8) (actual time=2.697..5.256 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.520..4.075 rows=172 loops=1
-> Subquery Scan on "*SELECT* 2"
(cost=52259.82..52428.16 rows=8417 width=21) (actual time=653.640..676.879
rows=62744 loops=1)
Output: "*SELECT* 2".c_last_name,
"*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1
-> Unique
(cost=52259.82..52343.99 rows=8417 width=17) (actual time=653.639..670.405
rows=62744 loops=1)
Output:
customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date
-> Sort
(cost=52259.82..52280.86 rows=8417 width=17) (actual time=653.637..662.428
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: external
merge Disk: 1800kB
-> Gather
(cost=50785.20..51711.07 rows=8417 width=17) (actual time=562.158..571.737
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=49785.20..49869.37 rows=8417 width=17) (actual
time=538.263..544.336 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=514.615..520.864 rows=29639 loops=1
Batches:
1 Memory Usage: 3601kB
-> Nested
Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=2.049..469.747
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.961..449.691 rows=268480 loops=1
->
Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=2.029..93.688
rows=285052 loops=2)
Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date
Worker 0: actual time=0.932..90.920 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.983..4.233 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.886..5.324 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.341 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.005..0.342 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.83..51568.70 rows=7165 width=21) (actual time=302.571..405.305
rows=23737 loops=1)
Output: "*SELECT* 3".c_last_name, "*SELECT*
3".c_first_name, "*SELECT* 3".d_date, 1
-> Unique (cost=50608.83..51497.05 rows=7165
width=17) (actual time=302.568..402.818 rows=23737 loops=1)
Output: customer_2.c_last_name,
customer_2.c_first_name, date_dim_2.d_date
-> Gather Merge (cost=50608.83..51443.31
rows=7165 width=17) (actual time=302.567..372.246 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=49608.81..49616.27
rows=2985 width=17) (actual time=298.204..310.075 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: external merge Disk:
2824kB
Worker 0: actual
time=297.401..308.205 rows=96226 loops=1
Sort Method: external merge
Disk: 2736kB
Worker 1: actual
time=294.914..308.683 rows=92128 loops=1
Sort Method: external merge
Disk: 2624kB
-> Nested Loop
(cost=2570.65..49436.52 rows=2985 width=17) (actual time=3.205..229.192
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.263..227.978 rows=96226 loops=1
Worker 1: actual
time=2.608..228.161 rows=92128 loops=1
-> Parallel Hash Join
(cost=2570.23..48102.52 rows=2985 width=8) (actual time=3.186..100.585
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.240..100.451 rows=96240 loops=1
Worker 1: actual
time=2.587..98.637 rows=92152 loops=1
-> Parallel Seq Scan
on public.web_sales (cost=0.00..43955.08 rows=600808 width=8) (actual
time=0.017..53.842 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.021..53.860 rows=486377 loops=1
Worker 1:
actual time=0.022..54.070 rows=460098 loops=1
-> Parallel Hash
(cost=2567.55..2567.55 rows=214 width=8) (actual time=3.007..3.008 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.123..2.123 rows=76 loops=1
Worker 1:
actual time=2.433..2.434 rows=114 loops=1
-> Parallel Seq
Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8)
(actual time=1.326..2.956 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.438..2.064 rows=76 loops=1
Worker 1:
actual time=0.751..2.375 rows=114 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=96240
Worker 1: actual
time=0.001..0.001 rows=1 loops=92152
Planning Time: 3.040 ms
Execution Time: 2262.778 ms
(145 rows)
