在 Postgres 9.6 中，為什麼 GIN 索引不用於帶有 text/int 數組的 JSONB 列？

在 Postgres 9.6 中，為什麼在下面的 SELECT 查詢中沒有為帶有 text/int 數組的 JSONB 列使用 GIN 索引？如何強制 Postgres 使用 GIN 索引？

SET enable_seqscan to FALSE;
EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}'

EXPLAIN 的輸出

Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
 Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
 Rows Removed by Filter: 37
Planning Time: 0.201 ms
Execution Time: 0.197 ms

表結構

CREATE TABLE "items" ( 
   "item_uuid" UUid NOT NULL,
   "metadata" JSONB,
   PRIMARY KEY ("item_uuid") 
);

int_array_cast函式定義

CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[] 
AS 
$$
 SELECT CAST($1 AS INT[])
$$
IMMUTABLE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT

使用 GIN 創建的索引

CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types'))

表中的範例項目

item_uuid | metadata
--------------------------------------------------
       1 | {"types":"{1,2}", "name": "item_name1"}
       2 | {"types":"{10,11}", "name": "item_name2"}
       3 | {"types":"12", "name": "item_name3"}
       3 | {"name": "item_name4"}

如果您希望使用索引在數組中進行搜尋，則需要首先將值儲存為正確的數組，而不是用大括號括起來的逗號分隔值的字元串，例如：{"types": [1,2], "name": "item_name1"}

然後您需要以 JSONB 的形式訪問該值，而不是文本。所以你需要使用->not ->>。

SELECT * 
FROM items
WHERE metadata -> 'types'  @> '[52,53]'::jsonb

最後，您需要索引types數組以使其正常工作，而不是metadata列的完整內容：

create index idx_metadata_types on items using gin  ((metadata -> 'types'));

---

要測試索引使用情況，您需要一個比只有 38 行的表大得多的表。

使用此查詢：

explain (analyze, buffers)
SELECT * 
FROM items
WHERE metadata -> 'types' @> '[52,53]'::jsonb

在一張有 100000 行的表上，我得到以下計劃（在我的 Windows 10 筆記型電腦上使用 Postgres 11）：

Bitmap Heap Scan on items  (cost=5.38..103.08 rows=100 width=78) (actual time=0.315..0.422 rows=54 loops=1)
 Recheck Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
 Heap Blocks: exact=51
 Buffers: shared hit=57
 ->  Bitmap Index Scan on idx_metadata_types  (cost=0.00..5.35 rows=100 width=0) (actual time=0.298..0.298 rows=54 loops=1)
       Index Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
       Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms

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