public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Ba Jinsheng <[email protected]>
To: [email protected] <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Performance of Query 4 on TPC-DS Benchmark
Date: Sun, 10 Nov 2024 23:18:47 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <SEZPR06MB6494F6A2837995BDD4E0BF9A8A5F2@SEZPR06MB6494.apcprd06.prod.outlook.com>
<[email protected]>
On 10.11.2024 23:16, Alena Rybakina wrote:
>
> Hi!
>
> On 10.11.2024 22:35, Ba Jinsheng wrote:
>> Hi all,
>>
>> Please see this case:
>>
>>
>> Query 4 on TPC-DS benchmark:
>>
>> with year_total as (
>> select c_customer_id customer_id
>> ,c_first_name customer_first_name
>> ,c_last_name customer_last_name
>> ,c_preferred_cust_flag customer_preferred_cust_flag
>> ,c_birth_country customer_birth_country
>> ,c_login customer_login
>> ,c_email_address customer_email_address
>> ,d_year dyear
>> ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2)
>> year_total
>> ,'s' sale_type
>> from customer
>> ,store_sales
>> ,date_dim
>> where c_customer_sk = ss_customer_sk
>> and ss_sold_date_sk = d_date_sk
>> group by c_customer_id
>> ,c_first_name
>> ,c_last_name
>> ,c_preferred_cust_flag
>> ,c_birth_country
>> ,c_login
>> ,c_email_address
>> ,d_year
>> union all
>> select c_customer_id customer_id
>> ,c_first_name customer_first_name
>> ,c_last_name customer_last_name
>> ,c_preferred_cust_flag customer_preferred_cust_flag
>> ,c_birth_country customer_birth_country
>> ,c_login customer_login
>> ,c_email_address customer_email_address
>> ,d_year dyear
>> ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2)
>> ) year_total
>> ,'c' sale_type
>> from customer
>> ,catalog_sales
>> ,date_dim
>> where c_customer_sk = cs_bill_customer_sk
>> and cs_sold_date_sk = d_date_sk
>> group by c_customer_id
>> ,c_first_name
>> ,c_last_name
>> ,c_preferred_cust_flag
>> ,c_birth_country
>> ,c_login
>> ,c_email_address
>> ,d_year
>> union all
>> select c_customer_id customer_id
>> ,c_first_name customer_first_name
>> ,c_last_name customer_last_name
>> ,c_preferred_cust_flag customer_preferred_cust_flag
>> ,c_birth_country customer_birth_country
>> ,c_login customer_login
>> ,c_email_address customer_email_address
>> ,d_year dyear
>> ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2)
>> ) year_total
>> ,'w' sale_type
>> from customer
>> ,web_sales
>> ,date_dim
>> where c_customer_sk = ws_bill_customer_sk
>> and ws_sold_date_sk = d_date_sk
>> group by c_customer_id
>> ,c_first_name
>> ,c_last_name
>> ,c_preferred_cust_flag
>> ,c_birth_country
>> ,c_login
>> ,c_email_address
>> ,d_year
>> )
>> select
>> t_s_secyear.customer_id
>> ,t_s_secyear.customer_first_name
>> ,t_s_secyear.customer_last_name
>> ,t_s_secyear.customer_email_address
>> from year_total t_s_firstyear
>> ,year_total t_s_secyear
>> ,year_total t_c_firstyear
>> ,year_total t_c_secyear
>> ,year_total t_w_firstyear
>> ,year_total t_w_secyear
>> where t_s_secyear.customer_id = t_s_firstyear.customer_id
>> and t_s_firstyear.customer_id = t_c_secyear.customer_id
>> and t_s_firstyear.customer_id = t_c_firstyear.customer_id
>> and t_s_firstyear.customer_id = t_w_firstyear.customer_id
>> and t_s_firstyear.customer_id = t_w_secyear.customer_id
>> and t_s_firstyear.sale_type = 's'
>> and t_c_firstyear.sale_type = 'c'
>> and t_w_firstyear.sale_type = 'w'
>> and t_s_secyear.sale_type = 's'
>> and t_c_secyear.sale_type = 'c'
>> and t_w_secyear.sale_type = 'w'
>> and t_s_firstyear.dyear = 2001
>> and t_s_secyear.dyear = 2001+1
>> and t_c_firstyear.dyear = 2001
>> and t_c_secyear.dyear = 2001+1
>> and t_w_firstyear.dyear = 2001
>> and t_w_secyear.dyear = 2001+1
>> and t_s_firstyear.year_total > 0
>> and t_c_firstyear.year_total > 0
>> and t_w_firstyear.year_total > 0
>> and case when t_c_firstyear.year_total > 0 then
>> t_c_secyear.year_total / t_c_firstyear.year_total else null end
>> > case when t_s_firstyear.year_total > 0 then
>> t_s_secyear.year_total / t_s_firstyear.year_total else null end
>> and case when t_c_firstyear.year_total > 0 then
>> t_c_secyear.year_total / t_c_firstyear.year_total else null end
>> > case when t_w_firstyear.year_total > 0 then
>> t_w_secyear.year_total / t_w_firstyear.year_total else null end
>> order by t_s_secyear.customer_id
>> ,t_s_secyear.customer_first_name
>> ,t_s_secyear.customer_last_name
>> ,t_s_secyear.customer_email_address
>> limit 100;
>>
>>
>>
>>
>>
>> The execution time is more than 50 minutes:
>> QUERY PLAN
>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Limit (cost=1255378.56..1255378.57 rows=1 width=132) (actual
>> time=3024403.311..3024403.342 rows=8 loops=1)
>> CTE year_total
>> -> Append (cost=197433.23..461340.62 rows=5041142 width=216)
>> (actual time=4126.043..7897.747 rows=384208 loops=1)
>> -> HashAggregate (cost=197433.23..233436.60 rows=2880270
>> width=216) (actual time=4126.042..4231.703 rows=190581 loops=1)
>> Group Key: customer.c_customer_id,
>> customer.c_first_name, customer.c_last_name,
>> customer.c_preferred_cust_flag, customer.c_birth_country,
>> customer.c_login, customer.c_email_address, date_dim.d_year
>> Batches: 1 Memory Usage: 213017kB
>> -> Hash Join (cost=8151.60..103824.45 rows=2880270
>> width=174) (actual time=69.110..1686.608 rows=2685453 loops=1)
>> Hash Cond: (store_sales.ss_sold_date_sk =
>> date_dim.d_date_sk)
>> -> Hash Join (cost=5103.00..93214.72
>> rows=2880270 width=174) (actual time=49.517..1162.567 rows=2750652
>> loops=1)
>> Hash Cond: (store_sales.ss_customer_sk =
>> customer.c_customer_sk)
>> -> Seq Scan on store_sales
>> (cost=0.00..80550.70 rows=2880270 width=30) (actual
>> time=0.018..208.022 rows=2880404 loops=1)
>> -> Hash (cost=3853.00..3853.00
>> rows=100000 width=152) (actual time=49.271..49.271 rows=100000 loops=1)
>> Buckets: 131072 Batches: 1
>> Memory Usage: 17161kB
>> -> Seq Scan on customer
>> (cost=0.00..3853.00 rows=100000 width=152) (actual
>> time=0.011..26.448 rows=100000 loops=1)
>> -> Hash (cost=2135.49..2135.49 rows=73049
>> width=8) (actual time=19.369..19.370 rows=73049 loops=1)
>> Buckets: 131072 Batches: 1 Memory
>> Usage: 3878kB
>> -> Seq Scan on date_dim
>> (cost=0.00..2135.49 rows=73049 width=8) (actual time=0.037..11.763
>> rows=73049 loops=1)
>> -> HashAggregate (cost=114410.03..132428.63 rows=1441488
>> width=216) (actual time=2369.202..2447.868 rows=136978 loops=1)
>> Group Key: customer_1.c_customer_id,
>> customer_1.c_first_name, customer_1.c_last_name,
>> customer_1.c_preferred_cust_flag, customer_1.c_birth_country,
>> customer_1.c_login, customer_1.c_email_address, date_dim_1.d_year
>> Batches: 1 Memory Usage: 131097kB
>> -> Hash Join (cost=8151.60..67561.67 rows=1441488
>> width=177) (actual time=62.483..974.143 rows=1430939 loops=1)
>> Hash Cond: (catalog_sales.cs_sold_date_sk =
>> date_dim_1.d_date_sk)
>> -> Hash Join (cost=5103.00..60728.94
>> rows=1441488 width=177) (actual time=46.571..687.972 rows=1434519
>> loops=1)
>> Hash Cond:
>> (catalog_sales.cs_bill_customer_sk = customer_1.c_customer_sk)
>> -> Seq Scan on catalog_sales
>> (cost=0.00..51841.88 rows=1441488 width=33) (actual
>> time=0.029..128.238 rows=1441548 loops=1)
>> -> Hash (cost=3853.00..3853.00
>> rows=100000 width=152) (actual time=46.311..46.325 rows=100000 loops=1)
>> Buckets: 131072 Batches: 1
>> Memory Usage: 17161kB
>> -> Seq Scan on customer
>> customer_1 (cost=0.00..3853.00 rows=100000 width=152) (actual
>> time=0.005..23.350 rows=100000 loops=1)
>> -> Hash (cost=2135.49..2135.49 rows=73049
>> width=8) (actual time=15.677..15.677 rows=73049 loops=1)
>> Buckets: 131072 Batches: 1 Memory
>> Usage: 3878kB
>> -> Seq Scan on date_dim date_dim_1
>> (cost=0.00..2135.49 rows=73049 width=8) (actual time=0.015..7.957
>> rows=73049 loops=1)
>> -> HashAggregate (cost=61277.38..70269.68 rows=719384
>> width=216) (actual time=1166.953..1198.730 rows=56649 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
>> Batches: 1 Memory Usage: 57369kB
>> -> Hash Join (cost=8151.60..37897.40 rows=719384
>> width=177) (actual time=68.327..508.594 rows=719119 loops=1)
>> Hash Cond: (web_sales.ws_sold_date_sk =
>> date_dim_2.d_date_sk)
>> -> Hash Join (cost=5103.00..32960.30
>> rows=719384 width=177) (actual time=52.240..357.963 rows=719217 loops=1)
>> Hash Cond:
>> (web_sales.ws_bill_customer_sk = customer_2.c_customer_sk)
>> -> Seq Scan on web_sales
>> (cost=0.00..25968.84 rows=719384 width=33) (actual
>> time=0.032..62.464 rows=719384 loops=1)
>> -> Hash (cost=3853.00..3853.00
>> rows=100000 width=152) (actual time=51.959..51.960 rows=100000 loops=1)
>> Buckets: 131072 Batches: 1
>> Memory Usage: 17161kB
>> -> Seq Scan on customer
>> customer_2 (cost=0.00..3853.00 rows=100000 width=152) (actual
>> time=0.004..25.350 rows=100000 loops=1)
>> -> Hash (cost=2135.49..2135.49 rows=73049
>> width=8) (actual time=15.831..15.834 rows=73049 loops=1)
>> Buckets: 131072 Batches: 1 Memory
>> Usage: 3878kB
>> -> Seq Scan on date_dim date_dim_2
>> (cost=0.00..2135.49 rows=73049 width=8) (actual time=0.014..8.100
>> rows=73049 loops=1)
>> -> Sort (cost=794037.94..794037.95 rows=1 width=132) (actual
>> time=3024403.310..3024403.313 rows=8 loops=1)
>> Sort Key: t_s_secyear.customer_id,
>> t_s_secyear.customer_first_name, t_s_secyear.customer_last_name,
>> t_s_secyear.customer_email_address
>> Sort Method: quicksort Memory: 26kB
>> -> Nested Loop (cost=0.00..794037.93 rows=1 width=132)
>> (actual time=354851.431..3024403.218 rows=8 loops=1)
>> Join Filter: ((t_s_secyear.customer_id =
>> t_w_secyear.customer_id) AND (CASE WHEN (t_c_firstyear.year_total >
>> '0'::numeric) THEN (t_c_secyear.year_total /
>> t_c_firstyear.year_total) ELSE NULL::numeric END > CASE WHEN
>> (t_w_firstyear.year_total > '0'::numeric) THEN
>> (t_w_secyear.year_total / t_w_firstyear.year_total) ELSE
>> NULL::numeric END))
>> Rows Removed by Join Filter: 810136
>> -> Nested Loop (cost=0.00..668006.23 rows=1
>> width=308) (actual time=33554.075..3021248.646 rows=72 loops=1)
>> Join Filter: ((t_s_secyear.customer_id =
>> t_c_secyear.customer_id) AND (CASE WHEN (t_c_firstyear.year_total >
>> '0'::numeric) THEN (t_c_secyear.year_total /
>> t_c_firstyear.year_total) ELSE NULL::numeric END > CASE WHEN
>> (t_s_firstyear.year_total > '0'::numeric) THEN
>> (t_s_secyear.year_total / t_s_firstyear.year_total) ELSE
>> NULL::numeric END))
>> Rows Removed by Join Filter: 11876277
>> * -> Nested Loop (cost=0.00..541974.53 rows=1 width=320) (actual
>> time=14866.104..3001271.961 rows=437 loops=1)*
>> * Join Filter: (t_s_firstyear.customer_id =
>> t_s_secyear.customer_id)*
>> * Rows Removed by Join Filter: 44702488*
>> * -> Nested Loop (cost=0.00..415941.57
>> rows=2 width=156) (actual time=11739.944..2946020.749 rows=1171 loops=1)*
>> * Join Filter:
>> (t_s_firstyear.customer_id = t_w_firstyear.customer_id)*
>> * Rows Removed by Join Filter: 112695277*
>> * -> Nested Loop
>> (cost=0.00..277302.08 rows=9 width=104) (actual
>> time=8139.729..2351733.795 rows=9952 loops=1)*
>> * Join Filter:
>> (t_s_firstyear.customer_id = t_c_firstyear.customer_id)*
>> * Rows Removed by Join Filter:
>> 997895870*
>> * -> CTE Scan on year_total
>> t_s_firstyear (cost=0.00..138631.41 rows=42 width=52) (actual
>> time=4126.046..4234.598 rows=37923 loops=1)*
>> * Filter: ((year_total >
>> '0'::numeric) AND (sale_type = 's'::text) AND (dyear = 2001))*
>> * Rows Removed by Filter:
>> 346285*
>> * -> CTE Scan on year_total
>> t_c_firstyear (cost=0.00..138631.41 rows=42 width=52) (actual
>> time=28.926..60.356 rows=26314 loops=37923)*
>> * Filter: ((year_total >
>> '0'::numeric) AND (sale_type = 'c'::text) AND (dyear = 2001))*
>> * Rows Removed by Filter:
>> 357894*
>> * -> CTE Scan on year_total
>> t_w_firstyear (cost=0.00..138631.41 rows=42 width=52) (actual
>> time=49.572..59.057 rows=11324 loops=9952)*
>> * Filter: ((year_total >
>> '0'::numeric) AND (sale_type = 'w'::text) AND (dyear = 2001))*
>> * Rows Removed by Filter: 372884*
>> * -> CTE Scan on year_total t_s_secyear
>> (cost=0.00..126028.55 rows=126 width=164) (actual time=0.002..44.949
>> rows=38175 loops=1171)*
>> * Filter: ((sale_type = 's'::text)
>> AND (dyear = 2002))*
>> * Rows Removed by Filter: 346033*
>> * -> CTE Scan on year_total t_c_secyear
>> (cost=0.00..126028.55 rows=126 width=52) (actual time=21.023..44.097
>> rows=27177 loops=437)*
>> * Filter: ((sale_type = 'c'::text) AND
>> (dyear = 2002))*
>> * Rows Removed by Filter: 357031*
>> * -> CTE Scan on year_total t_w_secyear
>> (cost=0.00..126028.55 rows=126 width=52) (actual time=36.137..43.090
>> rows=11252 loops=72)*
>> * Filter: ((sale_type = 'w'::text) AND (dyear =
>> 2002))*
>> * Rows Removed by Filter: 372956*
>> Planning Time: 4.529 ms
>> Execution Time: 3024486.695 ms
>> (83 rows)
>>
>>
>>
> I have noticed significant underestimation here. In addition, these
> nodes are executed noticeably slower in the plan, you can see it by
> the current time in the explain.
Sorry, I meant actual time, not current time.
--
Regards,
Alena Rybakina
Postgres Professional
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]
Subject: Re: Performance of Query 4 on TPC-DS Benchmark
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