具有 btree 索引的 jsonb 列的統計資訊不一致

我注意到涉及 jsonb 列的查詢的性能在 VACUUM ANALYZE 執行期間差異很大，同時測試它。在分析表格後，我似乎隨機地得到了完全不同的執行計劃。

我在這裡使用 Postgres 9.6。我的測試設置如下，我在 jsonb 列“params”中插入一個鍵“x”，其值介於 1 和 6 之間，其中 1 是最稀有的值，而 6 是最常見的值。我還有一個正常的 int 列“single_param”，其中包含相同的值分佈以進行比較。：

CREATE TABLE test_data (
   id      serial,
   single_param    int,
   params      jsonb
);

INSERT INTO test_data
SELECT 
   generate_series(1, 1000000) AS id, 
   floor(log(random() * 9999999 + 1)) AS single_param,
   json_build_object(
       'x', floor(log(random() * 9999999 + 1))
   ) AS params;

CREATE INDEX idx_test_btree ON test_data (cast(test_data.params->>'x' AS int));
CREATE INDEX idx_test_gin ON test_data USING GIN (params);
CREATE INDEX ON test_data(id)
CREATE INDEX ON test_data(single_param)

我正在測試的查詢是用於分頁結果的典型查詢，我按 id 排序並將輸出限制為前 50 行。

SELECT * FROM test_data where (params->>'x')::int = 1 ORDER BY id DESC LIMIT 50;

執行後，我隨機得到兩個解釋分析輸出之一VACUUM ANALYZE：

Limit  (cost=0.42..836.59 rows=50 width=33) (actual time=39.679..410.292 rows=10 loops=1)
 ->  Index Scan Backward using test_data_id_idx on test_data  (cost=0.42..44317.43 rows=2650 width=33) (actual time=39.678..410.283 rows=10 loops=1)
       Filter: (((params ->> 'x'::text))::integer = 1)
       Rows Removed by Filter: 999990"
Planning time: 0.106 ms
Execution time: 410.314 ms

要麼

Limit  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.034 rows=10 loops=1)
 ->  Sort  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.032 rows=10 loops=1)
       Sort Key: id DESC
       Sort Method: quicksort  Memory: 25kB
       ->  Index Scan using idx_test_btree on test_data  (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.016 rows=10 loops=1)
             Index Cond: (((params ->> 'x'::text))::integer = 1)
Planning time: 0.320 ms
Execution time: 0.052 ms

不同之處在於，與 where 子句匹配的列數的估計值在兩個計劃之間是不同的。第一個估計是 2650 行，第二個是 1 行，而實際數字是 10 行。

以下可能使用 GIN 索引的查詢版本似乎對 json 列使用了 1% 的預設估計值，這也導致了上述錯誤的查詢計劃：

SELECT * FROM test_data where params @> '{"x": 1}' ORDER BY id DESC LIMIT 50;

我最初的假設是 Postgres 不會對 jsonb 列有任何統計資訊，並且總是使用估計值，就像使用@>運算符進行查詢一樣。但是對於為能夠使用我創建的 btree 索引而編寫的查詢，它使用不同的估計值。有時這些足夠好，有時它們很糟糕。

這些估計來自哪裡？我猜它們是 Postgres 使用索引創建的某種統計資訊。對於列統計資訊，可以選擇收集更準確的統計資訊，這些統計資訊有類似的嗎？或者任何其他方式讓 Postgres 在我的情況下選擇更好的計劃？

目前（9.6 版）， Postgres沒有任何關於文件類型（如、或. （已經討論過是否以及如何改變它。）相反，Postgres 查詢計劃器使用恆定的預設頻率估計（就像你觀察到的那樣）。json``jsonb``xml``hstore

但是，對於像您的idx_test_btree. 該手冊為您提供了以下提示：

提示：雖然頻率的每列調整ANALYZE可能不是很有效率，但您可能會發現對收集的統計資訊的詳細程度進行每列調整是值得的 ANALYZE。在子句中大量使用WHERE且具有高度不規則數據分佈的列可能需要比其他列更細粒度的數據直方圖。請參閱ALTER TABLE SET STATISTICS或使用 default_statistics_target 配置參數更改數據庫範圍的預設值。

此外，預設情況下，有關功能選擇性的資訊有限。但是，如果您創建使用函式呼叫的表達式索引，則會收集有關該函式的有用統計資訊，這可以大大改進使用表達式索引的查詢計劃。

收集的統計量取決於 的一般設置default_statistics_target，可以用每列設置來否決。列的設置自動覆蓋依賴的索引。

的預設設置100是保守的。對於 1M 行的測試，如果數據分佈不均勻，可能有助於大幅增加它。再次檢查這一點，我發現您實際上可以使用 調整每個索引列的統計目標，ALTER INDEX目前沒有記錄。請參閱有關 pgsql-docs 的相關討論。

ALTER TABLE idx_test_btree ALTER int4 SET STATISTICS 2000;  -- max 10000, default 100

索引列的預設名稱並不完全直覺，但您可以通過以下方式查找：

SELECT attname FROM pg_attribute WHERE attrelid = 'idx_test_btree'::regclass

應該將類型名稱int4作為您案例的索引列名稱。

最佳設置STATISTICS取決於幾個因素：數據分佈、數據類型、更新頻率、典型查詢的特徵、…

在內部，這設置了 的值pg_attribute.attstattarget，其確切含義是（根據文件）：

對於標量數據類型，attstattarget既是要收集的“最常見值”的目標數量，也是要創建的直方圖 bin 的目標數量。

ANALYZE如果您不想等待 autovacuum 啟動，請執行：

ANALYZE test_data;

您必須ANALYZE使用該表，因為您不能ANALYZE直接索引。檢查（如果要驗證效果之前和之後）：

SELECT * FROM pg_statistic WHERE starelid = 'idx_test_btree'::regclass;

再次嘗試查詢…

有關的：

• 檢查 PostgreSQL 中的統計目標
• 未使用但影響查詢的索引
• 在具有現有數據的表上創建時未使用 PostgreSQL 部分索引

引用自：https://dba.stackexchange.com/questions/167525