21 posts tagged with "optimize"

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.

Problem​

Functionrand(MySQL) or random(PostgreSQL) returns a random floating-point value in the range 0 to 1.0. It is useful to get a random sample of dataset using following query.

select * from orders order by rand() limit 1; 

This works fine if customer table is less than 10,000 rows. But when you have 1,000,000 rows, the overhead for sorting the rows becomes unacceptable. The reason is quiet clear: we sort all rows, but only keep one.

Indexes are one of the most important tools for optimizing database performance. However, creating too many indexes or indexing the wrong columns can also have a negative impact on performance. Therefore, it is important to follow certain principles when designing indexes.

A database index is a data structure used to speed up database queries. It is similar to the catalog of books and can help quickly locate a certain row or rows in a table. A database index usually consists of a set of index keys (or index fields) whose values are stored in a data structure for fast lookup of specific rows.

PostgreSQL​

数据访问​

SEQ SCAN: 通过顺序扫描输入记录集来查找相关记录，顺序扫描（与索引扫描不同）执行单个读取操作。 CTE SCAN: 对公共表达式（CTE）查询结果执行顺序扫描。请注意，CTE的结果被计算并临时存储(Materialization)。 INDEX SCAN: 根据索引查找相关记录。索引扫描执行两次读取操作：一次读取索引，另一次从表中读取实际值。