我有一张 PostgreSQL 17 表table0
。它有一个col0
包含 2 到 16 个字符的随机字符串的列。我想执行部分匹配查询,因此我创建了一个 GIN 索引gin_trgm_ops
。令我惊讶的是,我发现我可以选择最多 1000 行包含 的行,abc
这比最多 200 行包含 的行快得多abc
。可重现的设置:
使用 Docker启动数据库:
docker run \
--name postgres-db \
-e POSTGRES_DB=postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-p 5432:5432\
-d postgres
我使用DBeaver执行查询或将其保存到query.sql
然后:
PGPASSWORD=mysecretpassword psql \
-h localhost \
-p 5432 \
-U postgres \
-d postgres \
-v ON_ERROR_STOP=1 \
-f query.sql
设置表格:
create table public.table0 (
col0 varchar(25)
);
select setseed(0.12343);
insert into table0 (col0)
select substring(md5(random()::text), 1, (2 + (random() * 14))::int)
from generate_series(1, 12345678);
create extension pg_trgm;
create index col0_gin_trgm_idx on table0 using gin (col0 gin_trgm_ops);
vacuum (full, analyze) table0;
检查选择 200 行数据的执行计划和运行时间,其中包含abc
:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
输出:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..352.27 rows=200 width=9) (actual time=0.672..49.646 rows=200 loops=1)
-> Seq Scan on table0 (cost=0.00..216621.29 rows=122985 width=9) (actual time=0.671..49.599 rows=200 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 114081
Planning Time: 4.540 ms
Execution Time: 50.960 ms
(6 rows)
检查选择最多 1000 行的执行计划和运行时间,其中包含abc
:
explain analyze
select * from table0 where col0 like '%abc%' limit 1000;
输出:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=848.36..1369.03 rows=1000 width=9) (actual time=17.373..26.987 rows=1000 loops=1)
-> Bitmap Heap Scan on table0 (cost=848.36..64883.21 rows=122985 width=9) (actual time=17.371..26.931 rows=1000 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=846
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..817.62 rows=122985 width=0) (actual time=14.689..14.690 rows=21318 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Planning Time: 2.165 ms
Execution Time: 27.356 ms
(8 rows)
可以看出,当我使用 时LIMIT 200
,引擎执行Seq Scan on table0
,但是当我使用 时LIMIT 1000
,Bitmap Index Scan on col0_gin_trgm_idx
则使用 。这从表面上解释了为什么使用 的查询LIMIT 200
花费了 4.540+50.960= 55.5 毫秒,而LIMIT 1000
查询花费的时间更少,为 2.165+27.356=29.521 毫秒。
我读到(参见此或此),理想情况下,我不应该在生产环境中强制使用索引。天真地讲,使用索引搜索包含的 200 行似乎abc
比当前使用的顺序扫描更快,因为使用索引搜索 1000 行比使用顺序扫描搜索 200 行更快。
在我的实际场景中(在AWS RDS上运行的Aurora PostgreSQL实例),这种差异是有限的:当我需要从表中选择 25 行时,选择其中的 100 行要快得多,然后只需通过其他方式过滤这 100 行(或者我可以修改应用程序,以便选择 100 行而不是 25 行也是可以的)。postgresql
我想知道我是否在索引方面做了一些不太优化的事情,或者我遗漏了一些东西。
我应该怎么做才能使查询LIMIT 200
至少与查询一样快LIMIT 1000
?
我主要对建议在生产环境中使用的方法感兴趣。可以肯定地说,table0
永远不需要修改就可以编辑其内容。
我无法 100% 可靠地重现此情况(这很奇怪,因为我认为 setseed 每次都会使其相同,但对我来说并非如此)。当复制失败时,这是因为它对两个查询都使用了位图扫描。
问题似乎出在行估计上(预期 122985 实际 21318,比例为 5.8)。错误估计使限制为 1 的 seq 扫描看起来更快,因为它期望在扫描较少的表后找到前 N 行。
我认为估计的问题在于 LIKE 通常不会产生远小于 1/统计大小的选择性估计,而且这对于手头的任务来说不够准确。
您可以通过以下方式修复此问题:
在分析表格之前,改进统计数据。您可能希望超过 1000。
不过,这似乎是一个非常不稳定的解决方案。我会反驳你链接到的其他帖子。如果这个性能上的小变化足以让人担心,我认为最好的解决办法是直接提示查询以强制它使用你想要的计划(参见pg_hint_plan)