我有一张名为
然而,当我执行以下操作时:
为什么那里没有使用索引?
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
为什么那里没有使用索引?