PostgreSQL 11 preview - 優化器 增強 彙總

标簽

PostgreSQL , 優化器 , 增強 , 11

https://github.com/digoal/blog/blob/master/201805/20180519_04.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 11 優化器增強。

https://github.com/digoal/blog/blob/master/201805/20180519_04.md#e1314-optimizer 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子句的選擇性(傳回多少行)成本估算（以前不對這部分進行估算）。