sql - 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

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

Answer 1

更新

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

Gin comes with built-in support for one-dimensional arrays (eg. integer[], text[]), but no support for NULL elements. The following operations are available:

• contains: value_array @> query_array
• overlap: value_array && query_array
• contained: value_array <@ query_array

如果你想利用 GIN 的优势，请使用 @>，而不是 LIKE 运算符

另外，请看much better Erwins answer on close question