PostgreSQL 過濾 JSON 中的數組長度

我有一個table帶有 JSONB 欄位的表data，其中包含一個可變長度數組，例如

{"label": "xyz", "items": [ ... ]}

"items"我在元素的長度上創建了一個索引：

CREATE INDEX n_items ON table ( JSONB_ARRAY_LENGTH(data->'items') )

但是當我過濾時，當我嘗試對其進行過濾時，我仍然會得到順序掃描：

EXPLAIN ANALYZE SELECT COUNT(*) FROM table WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

                                      QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate  (cost=2565655.67..2565655.68 rows=1 width=8)
  ->  Seq Scan on table (cost=0.00..2535256.19 rows=12159794 width=8)
        Filter: (jsonb_array_length((table.data -> 'items'::text)) = 2)
Planning time: 0.121 ms
Execution time: 482891.694 ms

過濾大約需要 8 分鐘！我在這裡做錯了什麼，或者這是 PostgreSQL 沒有保留 JSON(B) 對象的統計資訊的結果？應該可以展平這個data專欄，但我想確定這是我在開始工作之前需要做的。

編輯：這些數組長度變化不大。目前數據中只有 4 個不同的值，我預計不會有更多。在這種情況下，索引不是很有用，還是我可以通過其他方式改進過濾？

在不知道您的數據的情況下，我只能猜測您的索引的選擇性很低（如果數組的長度變化不大，則會發生這種情況）。

克服這個問題的一個技巧可能是稍微改變查詢並創建一個覆蓋索引。為此，選擇一NOT NULL列（例如，表的主鍵）進行計數，然後將此列包含在索引中：

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
 FROM your_table
WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

這將有望變成僅索引掃描（我對此進行了測試，省略了該jsonb部分，但您將能夠判斷它是否有效）。

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