openGauss - query rewrite
Published: 2023-03-09 02:04:19

Query rewriting—rewriter
query rewriting uses existing statement features and relational algebra operations to generate more efficient equivalent statements, and plays a key role in the database optimizer; especially in complex queries, it can bring orders of magnitude in performance Improvement can be described as a "black technology" with "immediate results". The SQL language is rich and diverse, and it is very flexible. Different developers have different handwritten SQL statements based on their experience, and they can also be automatically generated by tools. At the same time, the SQL language is a descriptive language. The users of the database only describe the desired results, and do not care about the specific way of obtaining the data. Contains some redundant information, which can be mined to generate more efficient SQL statements. Query rewriting is to convert the SQL statement entered by the user into a more efficient equivalent SQL. Query rewriting follows two basic principles:

Equivalence: the output of the original statement and the rewritten statement are the same.
Efficiency: The rewritten statement is more efficient in terms of execution time and resource usage than the original statement.
Introduce the following key query rewriting technologies of openGauss:

Constant expression simplification: Constant expression is the expression that contains the operation result as a constant in the user input SQL statement, which is divided into arithmetic expression, logical operation expression, and function expression . Query rewriting can precompute constant expressions to improve efficiency. For example the "SELECT * FROM table WHERE a=1+1;" statement is rewritten as a "SELECT * FROM table WHERE a=2" statement.
Sub-query improvement: Since the structure of sub-queries is clearer and conforms to people's reading and comprehension habits, the SQL statements entered by users often contain a large number of sub-queries, but related sub-queries often need to be implemented using nested loops to improve execution efficiency. Therefore, optimizing the subquery as a "Semi Join" can select other execution methods when optimizing the plan, or can improve execution efficiency. For example, the "SELECT * FROM t1 WHERE t1.a in (SELECT t2.a FROM t2);" statement can be rewritten as the "SELECT * FROM t1 LEFT SEMI JOIN t2 ON t1.a=t2.a" statement.
Predicate pushdown: Predicate, usually a condition in an SQL statement, for example, "t1.a=1" in the statement "SELECT * FROM t1 WHERE t1.a=1;" is a predicate. Equivalent-Class refers to a collection of objects such as equivalent attributes and entities. For example, in the "WHERE t1.a=t2.a" statement, t1.a and t2.a are equivalent to each other, forming an equivalent class {t1.a, t2.a}. Using equivalence class inference (also known as transitive closure), we can generate new predicate conditions, so as to achieve the purpose of reducing the amount of data and maximizing the use of indexes. As shown in Figure 2, we give a vivid example to illustrate the power of predicate pushdown. Suppose there are two tables t1 and t2; they respectively contain 100 rows of data [1,2,3,...100], then the query statement "SELECT * FROM t1 JOIN t2 ON t1.a=t2.a WHERE t1.a= 1" logical plan before and after query rewriting.



|Figure 3 Comparison before and after query rewriting|

The main work of query rewriting is implemented in the optimizer. In addition, openGauss also provides a rule-based rewrite interface. Users can rewrite the logical execution plan by creating replacement rules . For example, the view expansion function is replaced by the rules in the rewrite module, and the view expansion rules are created by default during the view creation process.

Original link: https://blog.csdn.net/GaussDB/article/details/116132257