Optimize TPCH Queries with PawSQL

Overview

TPCH is a database test benchmark for decision support systems，developed by TPC (Transaction Processing Performance Council) in 1994. TPCH tests consist of a set of 22 complex business queries and some data maintenance queries to measure the performance of relational database management systems under complex analytical queries. These queries simulate real queries in a data warehouse of a wholesale supplier, covering aspects such as order analysis, supply chain management, and partial sales analysis.

Database vendors use TPCH tests to evaluate their products, assessing the impact of different servers, storage configurations, parallel processing, and resource scheduling on database performance.

In this article, we will evaluate the effectiveness of PawSQL's optimizations on the TPCH queries by only rewriting queries and recommending indexes without changing the hardware configuration and database parameters.

Benchmark Setup

To better simulate real scenarios, we have established benchmarks on a relatively high basis.

1. We use the official TPC tool to generate test data;
2. We use the official TPC tool to generate query statements, and ensure that each filtering condition can generate a large amount of data for subsequent aggregation operations;
3. We have created indexes for primary keys and foreign keys to ensure the basic optimization of table joins.

Environment Config

Since this evaluation focuses on PawSQL's optimization recommendations, we reduce the hardware configuration and use the default configuration for database parameters.

• Hardware: 2C4G
• MySQL 8.0.17
• Database Parameters (defaults)
• PawSQL settings(defaults)

Data Volume

Table Name	Row Count	Engine
customer	15000	INNODB
lineitem	600572	INNODB
nation	25	INNODB
orders	150000	INNODB
part	20000	INNODB
partsupp	80000	INNODB
region	5	INNODB
supplier	1000	INNODB

Indexes Predefined

Optimization Summary

• For the 22 query statements in TPCH, 21 have optimization recommendations; recommended 8 query rewrites, recommended 21 indexes;
• 18 queries with performance improvements from optimization recommendations, 86% of the total; 12 queries with performance improvements over 50%, 60% of the total;
• Average performance improvement 276.40%, the maximum improvement for a single query is nearly 15 times.

Metrics of 22 Queries

The optimization results for each query can be viewed through the links. We will take Query-19 as an example to illustrate the capability of PawSQL's automatic optimization.

Query Number	Before Opt (ms)	After Opt (ms)	Rewrite	Indexes	Plan Changed	Perf Improved
Query-9	385.408	24.312	0	1	Yes	1484.69%
Query-11	345.284	26.994	0	1	Yes	1178.71%
Query-19	27.894	3.26	1	2	Yes	753.64%
Query-4	31.451	6.472	0	2	Yes	385.51%
Query-17	297.865	75.647	0	1	Yes	293.73%
Query-14	25.082	9.354	1	2	Yes	168.07%
Query-18	14.676	5.766	1	3	Yes	154.43%
Query-1	183.749	76.813	1	2	Yes	139.21%
Query-6	131.095	64.19	0	1	Yes	104.23%
Query-20	76.326	39.922	0	2	Yes	91.19%
Query-5	894.588	468.449	0	2	Yes	90.97%
Query-13	154.03	98.645	1	3	Yes	56.15%
Query-15	54.335	42.194	0	1	Yes	28.79%
Query-8	161.492	138.446	0	1	Yes	16.65%
Query-10	77.355	70.005	0	2	Yes	10.51%
Query-12	480.841	440.795	1	2	Yes	9.09%
Query-7	506.672	477.108	0	1	Yes	6.2%
Query-22	369.674	364.289	0	1	Yes	1.48%
Query-3	445.12	445.958	0	0	No	-0.19%
Query-16	156.083	184.76	1	2	Yes	-15.51%
Query-2	669.726	855.372	2	2	Yes	-21.7%
Query-21	1085.156	3466.57	0	1	Yes	-68.7%

Analysis of Q19 Optimization

Q19 in the TPCH test set queries the total discount revenue for all orders of 3 different types of parts shipped by air or carrier. The part selection considers specific brands, packaging and size ranges. This query exemplifies a report generated by a data mining tool. The characteristics of Q19 are that it has grouping, sorting, aggregation, and IN subquery operations combined with a 3-table join operation.

Original Q19 SQL:

select sum(lineitem.l_extendedprice * (1 - lineitem.l_discount)) as revenue
from lineitem, part
where part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#52'
    and part.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and lineitem.l_quantity >= 4
    and lineitem.l_quantity <= 4 + 10
    and part.p_size between 1 and 5
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
    or part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#11'
    and part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and lineitem.l_quantity >= 18
    and lineitem.l_quantity <= 18 + 10
    and part.p_size between 1 and 10
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
    or part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#51'
    and part.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and lineitem.l_quantity >= 29
    and lineitem.l_quantity <= 29 + 10
    and part.p_size between 1 and 15
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'

PawSQL's Automatic Optimization for Q19

PawSQL's automated optimization is divided into two main parts:

• Step 1: Apply the "Rewrite OR conditions to UNION" rule to rewrite it to UNION ALL

Note that PawSQL can determine if each OR branch is mutually exclusive and rewrite to UNION ALL to avoid a deduplication operation when they are. This is the case in this example.

The rewritten SQL is:

select sum(PawDT_1698819584185.l_extendedprice * (1 - PawDT_1698819584185.l_discount)) as revenue
from (
  select /*QB_3*/ lineitem.l_discount, lineitem.l_extendedprice
  from lineitem, part
  where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#51'
  and part.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  and lineitem.l_quantity >= 29
  and lineitem.l_quantity <= 29 + 10
  and part.p_size between 1 and 15
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
   
     union all
select /*QB_2*/ lineitem.l_discount, lineitem.l_extendedprice  
from lineitem, part
where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#52'
  and part.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  and lineitem.l_quantity >= 4
  and lineitem.l_quantity <= 4 + 10
  and part.p_size between 1 and 5
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
  
     union all
select /*QB_1*/ lineitem.l_discount, lineitem.l_extendedprice
from lineitem, part 
where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#11'
  and part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  and lineitem.l_quantity >= 18
  and lineitem.l_quantity <= 18 + 10
  and part.p_size between 1 and 10
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
) as PawDT_1698819584185

• Step 2: Recommend indexes based on the rewritten SQL:

CREATE INDEX PAWSQL_IDX2001589724 ON tpch.part(P_BRAND,P_CONTAINER,P_PARTKEY);
-- PART in block QB_1 (DRIVE): MATCHING(part.p_brand = 'Brand#11'), RANGE_SCAN(part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK'))

CREATE INDEX PAWSQL_IDX1523603249 ON tpch.lineitem(L_PARTKEY,L_SHIPINSTRUCT,L_QUANTITY); 
-- LINEITEM in block QB_1 (DRIVEN): MATCHING(part.p_partkey = lineitem.l_partkey and lineitem.l_shipinstruct = 'DELIVER IN PERSON'), RANGE_SCAN(lineitem.l_quantity >= 18)

Q19 Performance Verification

From verification output, the execution time decreased from 27.9ms to 2.26ms, and performance improved by 753%.

About PawSQL

PawSQL focuses on automating and intelligencing database performance optimization, supporting MySQL, PostgreSQL, openGauss, Oracle, etc. The SQL optimization products provided include:

• PawSQL Cloud, an online automated SQL optimization tool that supports SQL review, intelligent query rewriting, cost-based index recommendation, suitable for DBAs and data application developers.
• PawSQL Advisor, an IntelliJ plugin suitable for data application developers, can be installed by searching for "PawSQL Advisor" in the IDEA/DataGrip marketplace.
• PawSQL Engine is the backend optimization engine of PawSQL products, which can be installed and deployed independently as a docker image, and provide SQL optimization services through http/json interfaces.

Contact Us

Website: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql

Scan to follow PawSQL on Wechat