CTE Inline On TPC-DS Query 95 - Mailing list pgsql-performance

From Ba Jinsheng
Subject CTE Inline On TPC-DS Query 95
Date
Msg-id SEZPR06MB649446E1B2745EEF5B49BA238A2E2@SEZPR06MB6494.apcprd06.prod.outlook.com
Whole thread Raw
Responses Re: CTE Inline On TPC-DS Query 95
List pgsql-performance
Hi all,

Still for the query 95:

with ws_wh as
(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
 from web_sales ws1,web_sales ws2
 where ws1.ws_order_number = ws2.ws_order_number
   and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
 select  
   count(distinct ws_order_number) as "order count"
  ,sum(ws_ext_ship_cost) as "total shipping cost"
  ,sum(ws_net_profit) as "total net profit"
from
   web_sales ws1
  ,date_dim
  ,customer_address
  ,web_site
where
    d_date between '1999-5-01' and
           (cast('1999-5-01' as date) + interval '60 days')
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'TX'
and ws1.ws_web_site_sk = web_site_sk
and web_company_name = 'pri'
and ws1.ws_order_number in (select ws_order_number
                            from ws_wh)
and ws1.ws_order_number in (select wr_order_number
                            from web_returns,ws_wh
                            where wr_order_number = ws_wh.ws_order_number)
order by count(distinct ws_order_number)
limit 100;


If we specify ``NOT MATERIALIZED`` for the query, the execution time is reduced from 1min to 1s due to the usage of inline CTE. It seems expected as described in the PostgreSQL documentation. However, from the code: https://github.com/postgres/postgres/blob/REL_17_STABLE/src/backend/optimizer/plan/subselect.c#L939, I understand that this query does not contain volatile functions and includes simple predicates. I am wondering whether we can relax this condition checking, to enable inline CTE for such queries? Because it seems the performance benefit is huge on a standard benchmark.



Best regards,

Jinsheng Ba

 

Notice: This email is generated from the account of an NUS alumnus. Contents, views, and opinions therein are solely those of the sender.

pgsql-performance by date:

Previous
From: Ba Jinsheng
Date:
Subject: Performance of TPC-DS Query 95
Next
From: David Rowley
Date:
Subject: Re: Performance of TPC-DS Query 95