Re: Performance of Query 4 on TPC-DS Benchmark

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