JSONB ILIKE索引化

Question

我有一张名为people的表，其中body列是一个jsonb类型。

                                        Table "public.people"
     Column      |            Type             | Collation | Nullable |      Default       | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+--------------------+----------+--------------+-------------
 id              | uuid                        |           | not null | uuid_generate_v4() | plain    |              |
 body            | jsonb                       |           | not null |                    | extended |              |

Indexes:
    "people_pkey" PRIMARY KEY, btree (id)
    "idx_name" gin ((body ->> 'name'::text) gin_trgm_ops)

我的索引如下所示：

CREATE INDEX idx_name ON people USING gin ((body ->> 'name') gin_trgm_ops);

然而，当我执行以下操作时：

EXPLAIN ANALYZE SELECT * FROM "people" WHERE ((body ->> 'name') ILIKE '%asd%') LIMIT 40 OFFSET 0;

我明白：

Limit  (cost=0.00..33.58 rows=40 width=104) (actual time=100.037..4066.964 rows=11 loops=1)                                                     
   ->  Seq Scan on people  (cost=0.00..2636.90 rows=3141 width=104) (actual time=99.980..4066.782 rows=11 loops=1) 
         Filter: ((body ->> 'name'::text) ~~* '%asd%'::text)                                                                                     
         Rows Removed by Filter: 78516                                                                                                           
 Planning time: 0.716 ms                                                                                                                         
 Execution time: 4067.038 ms

为什么那里没有使用索引？

- Kamil Lelonek

---

你曾经找到答案了吗？我也遇到了同样的问题：JSONB值内的字符串无法使用trigram索引与ILIKE一起使用。 - user124114

Answer 1

更新

为避免与上述运算符混淆，我将引用http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gin。

Gin内置支持一维数组（例如整数[], 文本[]），但不支持NULL元素。以下操作可用：

• 包含：value_array @> query_array
• 重叠：value_array && query_array
• 被包含：value_array <@ query_array

如果要使用GIN的优势，请使用@>而不是LIKE运算符。

此外，请查看关闭问题的Erwins更好的答案。

- Vao Tsun

---

此外，请查看关于非可搜索查询的此讨论。 - Michel Milezzi

全文检索的问题在于我无法在其中模拟“%query%”。 - Kamil Lelonek

你不需要模拟它 - ilike '%val%' 可以正常工作，只是它不会使用全文搜索索引。我的意思是，如果你问“为什么在那里没有使用索引？”，简短的答案是“因为你使用了 LIKE 运算符”。 - Vao Tsun

我放了一个令人困惑的引用 - 抱歉。GIN本身是一个键值对存储，这就是为什么它对于数组或jsonb如此有效的原因。 - Vao Tsun

我正在寻找body中是否有与name匹配的特定字符串，而不是在body中是否有完全相同的JSON。 - Kamil Lelonek