标签

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    | 1000btree, for table "public.measured"