PostgreSQL Query Optimization Detailed

1.2.2 PostgreSQL

1.2.2.1 S6 Statement

Review the query execution plan, and the subquery is optimized (materialized so that the subquery results are cached, and the subquery is executed only once).

postgres=# EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select B1 from T1);

Nested Loop Semi Join (cost=0.00..41707.39 rows=680 width=12)

Join Filter: (t3.b3 >= t1.b1)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> materialize (cost=0.00..40.60 rows=2040 width=4)

-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)

1.2.2.2 S7 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

postgres=# EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select A1 from T1);

Nested Loop Semi Join (cost=0.15..377.82 rows=680 width=12)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Index Scan using T1_a1_key on T1 (cost=0.15..12.08 rows=680 width=4)

Index Cond: (A1 <= t3.b3)

1.2.2.3 S8 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

postgres=# EXPLAIN SELECT * from T3 WHERE b3 <= SOME (select A1 from T1);

Nested Loop Semi Join (cost=0.15..377.82 rows=680 width=12)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Index Scan using T1_a1_key on T1 (cost=0.15..12.08 rows=680 width=4)

Index Cond: (A1 >= t3.b3)

1.2.2.4 S9 Statement

View the query execution plan, and the subquery is optimized.

postgres=# EXPLAIN SELECT * from t3 WHERE b3 = SOME (select A1 from T1);

Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)

Hash Cond: (t3.b3 = t1.a1)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Hash (cost=30.40..30.40 rows=2040 width=4)

-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)

1.2.2.5 S10 Statement

View the query execution plan and the subquery has not been optimized.

postgres=# EXPLAIN SELECT * from T3 WHERE b3 <= All (select A1 from T1);

Seq Scan on t3 (cost=0.00..46649.50 rows=1020 width=12)

Filter: (Subplan 1)

Subplan 1

-> materialize (cost=0.00..40.60 rows=2040 width=4)

-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)

1.2.2.6 S11 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

View the query execution plan and the subquery has not been optimized.

postgres=# EXPLAIN SELECT * from T3 where B3 <= all (select A1 from T1 where a3=a1);

Seq Scan on t3 (cost=0.00..8527.00 rows=1020 width=12)

Filter: (Subplan 1)

Subplan 1

-> Index Scan using T1_a1_key on T1 (cost=0.15..8.17 Rows=1 width=4)

Index Cond: (a1 = t3.a3)

1.2.3 MySQL

1.2.3.1 S6 Statement

View the query execution plan and the subquery has not been optimized.

Mysql> EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select B1 from T1);

+----+-------------+-------+------+------+-------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY | T3 | All | NULL | Using where |

| 2 | subquery | T1 | All | NULL | NULL |

+----+-------------+-------+------+------+-------------+

2 rows in Set (0.04 sec)

1.2.3.2 S7 Statement

Viewing the query execution plan, subqueries are not optimized (but note that "Select tables optimized Away" indicates that subqueries are optimized with unique techniques, but not regular subqueries are Oberra optimized).

Mysql> EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select A1 from T1);

+----+-------------+-------+------+------+------------------------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+-------------+-------+------+------+------------------------------+

| 1 | PRIMARY | T3 | All | NULL | Using where |

| 2 | subquery | NULL | NULL | NULL | Select Tables Optimized Away |

+----+-------------+-------+------+------+------------------------------+

2 rows in Set (0.00 sec)

1.2.3.3 S8 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

Mysql> EXPLAIN SELECT * from T3 WHERE b3 <= SOME (select A1 from T1);

+----+-------------+-------+------+------+------------------------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+-------------+-------+------+------+------------------------------+

| 1 | PRIMARY | T3 | All | NULL | Using where |

| 2 | subquery | NULL | NULL | NULL | Select Tables Optimized Away |

+----+-------------+-------+------+-----+------------------------------+

2 rows in Set (0.00 sec)

1.2.3.4 S9 Statement

View the query execution plan, and the subquery is optimized.

Mysql> EXPLAIN SELECT * from t3 WHERE b3 = SOME (select A1 from T1);

+----+-------------+-------+-------+------+----------------------------------------------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+-------------+-------+-------+------+----------------------------------------------------+

| 1 | Simple | T1 | Index | A1 | Using Index |

| 1 | Simple | T3 | All | NULL | The Using where; Using Join buffer (block Nested Loop) |

+----+-------------+-------+-------+------+----------------------------------------------------+

2 rows in Set (0.00 sec)

1.2.3.5 S10 Statement

View the query execution plan and the subquery has not been optimized.

Mysql> EXPLAIN SELECT * from T3 WHERE b3 <= All (select A1 from T1);

+----+-------------+-------+-------+------+-------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+-------------+-------+-------+------+-------------+

| 1 | PRIMARY | T3 | All | NULL | Using where |

| 2 | subquery | T1 | Index | A1 | Using Index |

+----+-------------+-------+-------+------+-------------+

2 rows in Set (0.00 sec)

1.2.3.6 S11 Statement

View the query execution plan and the subquery has not been optimized.

Mysql> EXPLAIN SELECT * from T3 where B3 <= all (select A1 from T1 where a3=a1);

+----+--------------------+-------+------+------+--------------------------+

| ID | Select_type | Table | Type | Key | Extra |

+----+--------------------+-------+------+------+--------------------------+

| 1 | PRIMARY | T3 | All | NULL | Using where |

| 2 | DEPENDENT subquery | T1 | Ref | A1 | The Using where; Using Index |

+----+--------------------+-------+------+------+--------------------------+

2 rows in Set (0.00 sec)

1.2.4 Contrast

We copy the original SQL statement to make it easy to read.

S6:select * from T3 WHERE b3 >= "any" (SELECT B1 from T1);

S7:select * from T3 WHERE b3 >= "Any" (SELECT A1 from T1);

S8:select * from T3 WHERE b3 <= SOME (SELECT A1 from T1);

S9:select * from t3 WHERE B3 = SOME (SELECT a1 from T1);

S10:select * from T3 WHERE b3 <= All (SELECT A1 from T1);

S11:select * from T3 where B3 <= all (SELECT A1 from T1 where a3=a1);

Then compare the following:

SQL statement

Statement features

Toprowdb

PostgreSQL

Mysql

S6

Non-correlated subqueries, >= any operations, B1 normal columns

Join optimization

Semi Join optimization + materialized (unrelated subqueries)

has not been optimized

S7

Non-correlated subquery, >= any operation, A1 index key

Join optimization

Semi Join optimization + index Scan

Specific optimizations

S8

Non-correlated subqueries, <= SOME operations, A1 index keys

Join optimization

Semi Join optimization + index Scan

S9

Non-correlated subqueries, = SOME Operations, A1 index keys

has not been optimized

Semi Join Optimization

Be optimized

S10

Non-correlated subquery, <= All operation, A1 index key

has not been optimized

has not been optimized

Specific optimizations

S11

Related subquery, <= all operation, A1 index key, Condition A3=A1 as index key

Semi Join Optimization

has not been optimized

Specific optimizations

Analysis:

Q from the overall view, for the Any/some/all type of subqueries, PostgreSQL, MySQL, toprowdb have their own characteristics, the MySQL surface does not pull the subquery appears weaker, but the internal enabled index on the subquery on the target column for aggregation operations (using the index to The maximum minimum, compared to S6 and S7, greatly accelerates the execution speed, which is the meaning of the expression "specific optimizations" in the table above

Q Contrast S8 and S9, can be seen, toprowdb to S9 did not provide optimization, and PostgreSQL, MySQL can optimize, this toprowdb need to work hard

Q contrast S11, toprowdb and MySQL are stronger than PostgreSQL

In the first part of the subquery optimization, we found that MySQL's in subquery optimization technology is higher than other databases, and this article is heavy, EXISTS type of subquery, MySQL is weaker than PostgreSQL, toprowdb, and Any/some/all type of subqueries are almost Ping, so we can think of the three sub-query optimization ability.

1.3 Is there another type of subquery?

The first few sections, see a variety of various subqueries, a wide range of varieties appear, the content and form of subqueries is very colorful. However, these are not yet covered by all types, such as not in, not EXISTS, various any (=any, >any, >=any, <any, <=any) and type any SOME, various all Wait a minute.