jiaqizho commented on PR #1203: URL: https://github.com/apache/cloudberry/pull/1203#issuecomment-3190422009
<img width="3464" height="266" alt="image" src="https://github.com/user-attachments/assets/2b9acb5f-dca6-45dc-b927-859b16a35073"; /> TPCDS 1T - ID1423: main branch, commit hash: f225eeb2f3da9018d5a120df4f07a35c3e6e9810 - ID1424: main branch + current PR compare: <img width="1446" height="415" alt="image" src="https://github.com/user-attachments/assets/0bf1bea5-0ff1-4a27-abd0-1f5bbd45453b"; /> You can see that there is a performance degradation for SQL104. There is a performance improvement for SQL159. SQL104 --- Main branch plan in part of CTE: ``` -> Result (cost=0.00..10469.21 rows=98438 width=52) (actual time=1538.026..1717.028 rows=158594 loops=1) Filter: (((('w'::text) = 's'::text) AND (date_dim_2.d_year = 1999) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 's'::text) AND (date_dim_2.d_year = 2000)) OR ((('w'::text) = 'c'::text) AND (date_dim_2.d_year = 1999) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 'c'::text) AND (date_dim_2.d_year = 2000)) OR ((('w'::text) = 'w'::text) AND (date_dim_2.d_year = 1999) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 'w'::text) AND (date_dim_2.d_year = 2000))) -> Finalize HashAggregate (cost=0.00..10448.02 rows=140625 width=51) (actual time=1568.015..1717.017 rows=159466 loops=1) Group Key: customer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year Extra Text: (seg0) hash table(s): 1; 159207 groups total in 5 batches, 48216 spill partitions; disk usage: 2784KB; chain length 2.6 avg, 11 max; using 159207 of 1310720 buckets; total 0 expansions. -> Redistribute Motion 96:96 (slice6; segments: 96) (cost=0.00..10302.57 rows=140625 width=81) (actual time=0.000..1213.020 rows=191880 loops=1) Hash Key: customer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year -> Streaming Partial HashAggregate (cost=0.00..10266.92 rows=140625 width=81) (actual time=14084.185..15653.205 rows=198774 loops=1) Group Key: customer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year Extra Text: (seg5) hash table(s): 1; chain length 2.5 avg, 13 max; using 198341 of 524288 buckets; total 0 expansions. -> Hash Join (cost=0.00..6564.84 rows=2991186 width=98) (actual time=166.003..6938.115 rows=3034069 loops=1) Hash Cond: (web_sales.ws_bill_customer_sk = customer_2.c_customer_sk) Extra Text: (seg49) Hash chain length 2.2 avg, 11 max, using 55888 of 65536 buckets. -> Redistribute Motion 96:96 (slice7; segments: 96) (cost=0.00..4281.16 rows=2991186 width=33) (actual time=0.000..1998.033 rows=3034069 loops=1) Hash Key: web_sales.ws_bill_customer_sk -> Hash Join (cost=0.00..3972.20 rows=2991186 width=33) (actual time=102.001..6882.090 rows=3248320 loops=1) Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Extra Text: (seg21) Hash chain length 1.0 avg, 1 max, using 731 of 262144 buckets. -> Seq Scan on web_sales (cost=0.00..1272.50 rows=7499994 width=33) (actual time=53.001..3757.049 rows=7934949 loops=1) -> Hash (cost=439.44..439.44 rows=728 width=8) (actual time=41.001..41.001 rows=731 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2077kB -> Seq Scan on date_dim date_dim_2 (cost=0.00..439.44 rows=728 width=8) (actual time=38.000..41.001 rows=731 loops=1) Filter: ((d_year = 1999) OR (d_year = 2000)) -> Hash (cost=441.11..441.11 rows=125000 width=73) (actual time=289.003..289.003 rows=125744 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 13435kB -> Seq Scan on customer customer_2 (cost=0.00..441.11 rows=125000 width=73) (actual time=18.000..137.001 rows=125744 loops=1) ``` Current commit plan in part of CTE: ``` -> Result (cost=0.00..9977.50 rows=98438 width=52) (actual time=124726.205..125158.209 rows=158287 loops=1) Filter: (((('w'::text) = 's'::text) AND (date_dim_2.d_year = 2000) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 's'::text) AND (date_dim_2.d_year = 2001)) OR ((('w'::text) = 'c'::text) AND (date_dim_2.d_year = 2000) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 'c'::text) AND (date_dim_2.d_year = 2001)) OR ((('w'::text) = 'w'::text) AND (date_dim_2.d_year = 2000) AND ((sum(((((web_sales.ws_ext_list_price - web_sales.ws_ext_wholesale_cost) - web_sales.ws_ext_discount_amt) + web_sales.ws_ext_sales_price) / '2'::numeric))) > '0'::numeric)) OR ((('w'::text) = 'w'::text) AND (date_dim_2.d_year = 2001))) -> HashAggregate (cost=0.00..9956.31 rows=140625 width=44) (actual time=124726.205..125113.208 rows=159238 loops=1) Group Key: customer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year Extra Text: (seg0) hash table(s): 1; 157041 groups total in 5 batches, 463372 spill partitions; disk usage: 15104KB; chain length 2.6 avg, 13 max; using 157041 of 1310720 buckets; total 0 expansions. -> Redistribute Motion 96:96 (slice6; segments: 96) (cost=0.00..7275.82 rows=2678562 width=98) (actual time=0.000..118001.140 rows=3019314 loops=1) Hash Key: customer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year -> Hash Join (cost=0.00..6454.19 rows=2678562 width=98) (actual time=474.005..4128.040 rows=3025036 loops=1) Hash Cond: (web_sales.ws_bill_customer_sk = customer_2.c_customer_sk) Extra Text: (seg26) Hash chain length 1.6 avg, 7 max, using 80932 of 131072 buckets. -> Redistribute Motion 96:96 (slice7; segments: 96) (cost=0.00..4281.25 rows=2973133 width=33) (actual time=0.000..117048.528 rows=3035044 loops=1) Hash Key: web_sales.ws_bill_customer_sk -> Hash Join (cost=0.00..3974.15 rows=2973133 width=33) (actual time=117.002..5409.071 rows=3194638 loops=1) Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Extra Text: (seg21) Hash chain length 1.0 avg, 2 max, using 729 of 262144 buckets. -> Seq Scan on web_sales (cost=0.00..1276.60 rows=7499757 width=33) (actual time=84.001..3460.045 rows=7929813 loops=1) -> Hash (cost=439.44..439.44 rows=724 width=8) (actual time=49.001..49.001 rows=731 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2077kB -> Seq Scan on date_dim date_dim_2 (cost=0.00..439.44 rows=724 width=8) (actual time=46.001..49.001 rows=731 loops=1) Filter: ((d_year = 2000) OR (d_year = 2001)) -> Hash (cost=441.11..441.11 rows=125000 width=73) (actual time=450.004..450.004 rows=125744 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 13948kB -> Seq Scan on customer customer_2 (cost=0.00..441.11 rows=125000 width=73) (actual time=56.001..232.002 rows=125744 loops=1) ``` one-step-AGG become two-step-AGG, which is wrose. SQL159 --- Main branch plan in CTE: ``` -> Shared Scan (share slice:id 1:0) (cost=0.00..19674.05 rows=30641 width=1) (actual time=135704.775..135704.775 rows=0 loops=1) -> Finalize HashAggregate (cost=0.00..19674.02 rows=30641 width=64) (actual time=135698.247..135702.247 rows=1478 loops=1) Group Key: date_dim.d_week_seq, store_sales.ss_store_sk Extra Text: (seg51) hash table(s): 1; chain length 1.9 avg, 2 max; using 1478 of 65536 buckets; total 0 expansions. -> Redistribute Motion 96:96 (slice2; segments: 96) (cost=0.00..19665.12 rows=30641 width=64) (actual time=275.005..97315.612 rows=16945121 loops=1) Hash Key: date_dim.d_week_seq, store_sales.ss_store_sk -> Streaming Partial HashAggregate (cost=0.00..19658.99 rows=30641 width=64) (actual time=280.004..92594.211 rows=16482550 loops=1) Group Key: date_dim.d_week_seq, store_sales.ss_store_sk Extra Text: (seg21) hash table(s): 1; chain length 2.6 avg, 17 max; using 16482550 of 28311552 buckets; total 1 expansions. -> Hash Join (cost=0.00..11923.39 rows=29999643 width=22) (actual time=34.000..12943.169 rows=30305723 loops=1) Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Extra Text: (seg21) Hash chain length 1.1 avg, 5 max, using 63798 of 262144 buckets. -> Seq Scan on store_sales (cost=0.00..2889.47 rows=29999643 width=14) (actual time=5.000..5564.073 rows=31732841 loops=1) -> Hash (cost=437.03..437.03 rows=73049 width=16) (actual time=23.000..23.000 rows=73049 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 5483kB -> Seq Scan on date_dim (cost=0.00..437.03 rows=73049 width=16) (actual time=1.000..10.000 rows=73049 loops=1) ``` Current commit plan in CTE: ``` -> Shared Scan (share slice:id 1:0) (cost=0.00..21620.44 rows=30688 width=1) (actual time=32581.427..32581.427 rows=0 loops=1) -> HashAggregate (cost=0.00..21620.41 rows=30688 width=64) (actual time=32579.538..32581.538 rows=1478 loops=1) Group Key: date_dim.d_week_seq, store_sales.ss_store_sk Extra Text: (seg0) hash table(s): 1; chain length 1.9 avg, 2 max; using 1333 of 65536 buckets; total 0 expansions. Extra Text: (seg51) hash table(s): 1; chain length 1.9 avg, 2 max; using 1478 of 65536 buckets; total 0 expansions. -> Redistribute Motion 96:96 (slice2; segments: 96) (cost=0.00..13989.26 rows=29999878 width=22) (actual time=41.001..17123.283 rows=31572684 loops=1) Hash Key: date_dim.d_week_seq, store_sales.ss_store_sk -> Hash Join (cost=0.00..11923.47 rows=29999878 width=22) (actual time=33.000..18240.239 rows=30305131 loops=1) Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Extra Text: (seg21) Hash chain length 1.1 avg, 4 max, using 68177 of 524288 buckets. -> Seq Scan on store_sales (cost=0.00..2889.49 rows=29999878 width=14) (actual time=5.000..7884.103 rows=31733198 loops=1) -> Hash (cost=437.03..437.03 rows=73049 width=16) (actual time=29.000..29.000 rows=73049 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 7531kB -> Seq Scan on date_dim (cost=0.00..437.03 rows=73049 width=16) (actual time=1.000..8.000 rows=73049 loops=1) ``` Two-step-AGG become one-step-AGG, which is more better. **In fact, the current commit does not work for sql04 and sql59.** Whether it is sql59 or sql04, both are join before the AGG. The stats `NDVBySeg` will become invalid after the join (as I said, the current commit is relatively conservative). -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org For additional commands, e-mail: commits-h...@cloudberry.apache.org
