标签
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"
