PostgreSQL 11 preview - 表达式索引柱状图buckets\STATISTICS\default_statistics_target可设置

标签

PostgreSQL , 表达式索引 , 柱状图 , buckets , 增强 , 11

背景

PostgreSQL 支持表达式索引，优化器支持CBO，对于普通字段，我们有默认统计信息bucket控制，也可以通过alter table alter column来设置bucket，提高或降低字段级的统计精度。

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]  
    action [, ... ]  
  
    ALTER [ COLUMN ] column_name SET STATISTICS integer  

SET STATISTICS

This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using the system default statistics target (default_statistics_target). For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.

SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.

但是对于表达式索引，它可能是多列，它可能内嵌表达式，因为表达式它没有列名，只有第几列（或表达式），怎么调整表达式索引的统计信息bucket数呢？

PostgreSQL 将这个设置功能放到了alter index中。

ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number  
    SET STATISTICS integer  
  
ALTER [ COLUMN ] column_number SET STATISTICS integer  

This form sets the per-column statistics-gathering target for subsequent [ANALYZE](https://www.postgresql.org/docs/devel/static/sql-analyze.html) operations, though can be used only on index columns that are defined as an expression. Since expressions lack a unique name, we refer to them using the ordinal number of the index column. The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using the system default statistics target ([default_statistics_target](https://www.postgresql.org/docs/devel/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)). For more information on the use of statistics by the PostgreSQL query planner, refer to [Section 14.2](https://www.postgresql.org/docs/devel/static/planner-stats.html).  

例子

create table measured (x text, y text, z int, t int);

CREATE INDEX coord_idx ON measured (x, y, (z + t));  
  
-- 将(z + t)的统计信息柱状图设置为1000  
  
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;  
  
-- psql 可以看到这个统计信息柱状图的设置值

postgres=# \d+ coord_idx
                Index "public.coord_idx"
 Column |  Type   | Definition | Storage  | Stats target 
--------+---------+------------+----------+--------------
 x      | text    | x          | extended | 
 y      | text    | y          | extended | 
 expr   | integer | (z + t)    | plain    | 1000
btree, for table "public.measured"