數倉如何進行表級控制analyze？

摘要： 介紹如何設置採樣大小和表級控制analyze。

本文分享自華為雲社區《GaussDB(DWS) 如何表級控制analyze》，作者：leapdb。

一、控制採樣大小

【設置全局採樣大小】

通過參數default_statistics_target設置全局預設採樣大小。

a.default_statistics_target>0，表示按固定值方式採樣。取值範圍:(0, 10000]
analyze採樣大小 = default_statistics_target * 300;

b.default_statistics_target<0，表示按百分比方式採樣。取值範圍：[-100, 0)
analyze採樣大小 = (-1) * default_statistics_target * 表的估算大小。

【設置表級採樣大小】

pg_attribute->attstattarget 用於設置每列採樣大小，所有列的最大值作為表採樣大小。

attstattarget取值範圍從-101到10000：
[-101~-1) 表示使用百分比計算採樣大小。採樣大小 = 表估算條數 * (-1) * (attstattarget + 1) / 100
-1 表示未設置，使用全局參數default_statistics_target計算採樣大小。採樣大小 = default_statistics_target * 300
0 表示該列不進行採樣。
[1,10000] 表示使用固定值計算採樣大小。採樣大小 = attstattarget * 300
ALTER TABLE table_name ALTER column_name SET STATISTICS 200; --把採樣大小調整為60000
ALTER TABLE table_name ALTER column_name SET STATISTICS PERCENT 2; --把採樣大小調整為2%

一般表級採樣大小高於全局採樣大小。歷史原因，813及以下版本default_statistics_target設置負數時，全局採樣大小優先順序高。

二、控制analyze開關

【全局開關參數】

autovacuum --後臺autovacuum線程開關參數
autovacuum_mode --後臺autovacuum任務參數（vacuum：僅作vacuum；analyze：僅作analyze；mix：預設參數，vacuum和analyze都做）
autoanalyze --動態採樣控制參數
autoanalyze_mode --動態採樣類型控制參數，820及以上支持，813及以下都是normal類型

【表級開關參數】

關閉“動態採樣”，“輪詢採樣”和手動analyze等所有形式的統計信息收集。（適用於所有版本）

postgres=# select 'ALTER TABLE t1 ALTER '||attname||' SET STATISTICS 0;' from pg_attribute where attnum > 0 and attrelid='t1'::regclass;
 ?column?                     
-------------------------------------------------
 ALTER TABLE t1 ALTER fooid SET STATISTICS 0;
 ALTER TABLE t1 ALTER foosubid SET STATISTICS 0;
 ALTER TABLE t1 ALTER fooname SET STATISTICS 0;
(3 rows)
執行以上拼接出來的SQL，修改所有列的採樣大小為0即可。再次analyze時則報沒有列可做analyze，即實現了analyze關閉。
postgres=# analyze t1;
INFO:  No columns in "public.t1" can be used to collect statistics.
ANALYZE

820及以上版本，支持表級控制“統計信息自動收集模式”。也可以鎖定統計信息。

alter table lineitem set (analyze_mode='backend'); --只做輪詢採樣analyze
alter table lineitem set (analyze_mode='runtime'); --只做動態採樣analyze
alter table lineitem set (analyze_mode='frozen'); --禁止做所有形式的analyze
alter table lineitem set (analyze_mode='all'); --恢復表analyze模式的預設行為
select pg_options_to_table(reloptions) from pg_class where relname='lineitem'; --查看修改效果
設置成backend即關閉了動態採樣，只做輪詢採樣
設置成frozen即關閉了所有形式analyze，但無統計信息時還是會觸發一次動態採樣，確保至少有基本統計信息可用。

點擊關註，第一時間瞭解華為雲新鮮技術~