PostgreSQL 11 preview - 优化器增强汇总

最新推荐文章于 2022-11-25 11:32:52 发布

weixin_33881753

最新推荐文章于 2022-11-25 11:32:52 发布

阅读量109

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/620126

PostgreSQL 11 对优化器进行了多项改进，包括优化了高频词选择性的计算方式，改善了某些条件下的选择性估计，重写了部分表达式以提高评估准确性，并增强了 EXIST 和 NOT EXISTS 的行数评估。

背景

PostgreSQL 11 优化器增强。

E.1.3.1.4. Optimizer

Improve the selection of the optimizer statistics' most-common-values (Jeff Janes, Dean Rasheed)

高频词的选择性计算更好。

postgres=# \d pg_stats  
                     View "pg_catalog.pg_stats"  
         Column         |   Type   | Collation | Nullable | Default   
------------------------+----------+-----------+----------+---------  
 schemaname             | name     |           |          |   
 tablename              | name     |           |          |   
 attname                | name     |           |          |   
 inherited              | boolean  |           |          |   
 null_frac              | real     |           |          |   
 avg_width              | integer  |           |          |   
 n_distinct             | real     |           |          |   
 most_common_vals       | anyarray |           |          |   
 most_common_freqs      | real[]   |           |          |   
 histogram_bounds       | anyarray |           |          |   
 correlation            | real     |           |          |   
 most_common_elems      | anyarray |           |          |   
 most_common_elem_freqs | real[]   |           |          |   
 elem_count_histogram   | real[]   |           |          |

Previously most-common-values (MCV) were chosen based on their significance compared to all column values. Now, MCV are chosen based on their significance compared to the non-MCV values. This improves the statistics for uniform (fewer) and non-uniform (more) distributions.

Improve selectivity estimates for >= and <= when the constants are not common values (Tom Lane)

Previously such cases used the same selectivity as > and <, respectively. This change is particularly useful for BETWEEN with small ranges.

大于等于、小于等于某常量时，如果这个常量是一个非高频词(不在most_common_vals中)，使用更优的选择算法。

Optimize var = var to var IS NOT NULL where equivalent (Tom Lane)

重写var=var这样的表达式，改成var is not null，从而提高选择性评估的准确性。

This leads to better selectivity estimates.

PostgreSQL 11:

postgres=# explain select * from aaa where id=id and info='abc';  
                        QUERY PLAN                           
-----------------------------------------------------------  
 Seq Scan on aaa  (cost=0.00..379776.80 rows=16 width=368)  
   Filter: ((id IS NOT NULL) AND (info = 'abc'::text))  
(2 rows)

PostgreSQL 10:

postgres=# explain select * from aaa where id=id and info='abc';  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Seq Scan on aaa  (cost=10000000000.00..10000990476.50 rows=1 width=368)  
   Filter: ((id = id) AND (info = 'abc'::text))  
(2 rows)

Improve row count optimizer estimates for EXISTS and NOT EXISTS queries (Tom Lane)

增强exists, not exists的行数评估。
Add optimizer selectivity costs for HAVING clauses (Tom Lane)

增加having子句的选择性(返回多少行)成本估算（以前不对这部分进行估算）。