PostgreSQL 11 preview - 优化器 增强 汇总

背景

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子句的选择性(返回多少行)成本估算（以前不对这部分进行估算）。

digoal’s 大量PostgreSQL文章入口