PostgreSQL 11 preview - 表達式索引柱狀圖buckets\STATISTICS\default_statistics_target可設定

标簽

PostgreSQL , 表達式索引 , 柱狀圖 , buckets , 增強 , 11

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

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).  
           

https://github.com/digoal/blog/blob/master/201805/20180519_07.md#%E4%BE%8B%E5%AD%90 例子

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"