PostgreSQL query optimization: Query Condition Optimization 1 (condition classification)

In SQL, query conditions need to be divided into three types in the query optimization stage. The three types of conditions have different effects and can be converted to each other in some cases.
First, describe the SQL statement execution steps, which can be divided into three steps: 1. Read the tuples in the table; 2. If join exists, join is started; 3. filter the where condition. Take simple SQL as an example:
Table TBL (C1 int, C2 int, C3 INT );
SQL statement: select a. C1, B. C3 from tbl a, tbl B where a. C1 = B. C3 and A. C2 = 4;
For the preceding SQL statement, the execution process is as follows: 1. read each tuple in Table A and table B; 2. Use each tuple of table A and table B as a Cartesian product; 3, use the where condition to filter the connection results.
For the three stages of the preceding execution, we divide the SQL statements into three types:
Class A: filtering conditions for tables. It filters the tuples read from the base table;
Class B: Table connection condition. The two tables are connected and use it as the connection condition;
Class C: filtering condition of the connection. After the connection is complete, filter the connection results based on this condition.
For the preceding SQL statement, it is equivalent to the following SQL statement:
Select a. C1, B. C3 from (
Select * From TBL where a. C2 = 4
) A inner join tbl B on (A. C1 = B. C3) where true;
Then, two conditions in the WHERE clause of the original SQL statement are pushed down to the base table as the filtering condition of the table (. c2 = 4), the other is pushed down to two tables as their connection conditions (. c1 = B. c3 ). In this case, the join filter condition is true in the WHERE clause, that is, no join filter condition.

When the connection between tables is an internal join, the expressions connected by and on the where condition can be pushed to the base table as the filter condition of the base table or the join condition, however, when the connection is not an internal connection, the push-down operation is incorrect. See the following example:
Table tbl_1 (C1 int, C2 INT); tuples (1, 1), (2, 2)
Table tbl_2 (C1 int, C2 INT); tuples (1, 1), (null, null)
Then
Select * From tbl_1 left join tbl_2 on true whrer tbl_1.c1 = tbl_2.c2;
The query result is: (1, 1, 1, 1)
When the where condition is pushed down to the connection condition, that is, the SQL statement is
Select * From tbl_1 left join tbl_2 on (tbl_1.c1 = tbl_2.c2) whrer true;
The query result is (1, 1, 1, 1), (2, 2, null, null ).
The difference between these results is that (tbl_1.c1 = tbl_2.c2) is used as the connection condition, which indicates that for a tuples in tbl_1, if it can be set to true (tbl_1.c1 = tbl_2.c2) with any tuples in tbl_2, it is connected and returned. If not, the tbl_1 tuples are output and left blank on the right side. When this condition is used as the filter condition for the connection, it filters out (2, 2, null, null) because it does not meet the filter condition for the connection.