我正在使用最新的 PostgreSQL docker 镜像来创建本地数据库(在 Apple M1 Pro - MacOS Sonoma 14.5 机器上)。我创建一个table0
包含单个列的表,并用 2 到 16 个字符的随机字符串填充它。我在和上col0
创建了一个三元索引。具体步骤:col0
vacuum (analyze)
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 (analyze) table0;
我检查了选择包含字符串的 200 行的查询计划abc
:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
输出,确认不是三字母索引,而是使用顺序扫描:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..351.78 rows=200 width=10) (actual time=0.313..15.640 rows=200 loops=1)
-> Seq Scan on table0 (cost=0.00..216614.79 rows=123154 width=10) (actual time=0.312..15.620 rows=200 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 115643
Planning Time: 4.401 ms
Execution Time: 15.841 ms
(6 rows)
但是,如果我不搜索包含以下内容的行,而是abc
搜索包含以下内容的行bcd
:
explain analyze
select * from table0 where col0 like '%bcd%' limit 200;
然后我得到一个不同的查询计划,它现在包括一个索引扫描:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..764.83 rows=200 width=10) (actual time=7.032..7.230 rows=200 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.34..4394.94 rows=1219 width=10) (actual time=7.031..7.220 rows=200 loops=1)
Recheck Cond: ((col0)::text ~~ '%bcd%'::text)
Heap Blocks: exact=169
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=5.100..5.100 rows=21264 loops=1)
Index Cond: ((col0)::text ~~ '%bcd%'::text)
Planning Time: 0.521 ms
Execution Time: 7.366 ms
(8 rows)
setseed(0.12343);
即使使用了,此设置也可能无法在第一次尝试时完全重现,因为analyze
“根据其自己的随机行选择收集统计信息”(请参阅此处的第二段)。我多次重现了上述情况,并且我从未尝试过设置步骤超过 4 次,所以我希望它很容易重现,即使我提供的代码不是完全确定的。(我在尝试之间删除并重新启动了 docker 容器。)
这个答案对为什么曾经使用顺序扫描以及为什么有时使用索引扫描给出了基本解释。它还提供了 2 条关于如何阻止顺序扫描的建议:修改random_page_cost
和STATISTICS
值。
我设置random_page_cost
为 1.1 (通过)。我还通过(通过) ALTER DATABASE postgres SET random_page_cost = 1.1;
“提高了收集的统计数据量” 。再一次我重新运行:analyze
ALTER TABLE table0 ALTER COLUMN col0 SET STATISTICS 1000;
vacuum (analyze) table0;
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
这次col0_gin_trgm_idx
确实使用了 trigram 索引。之后,我重新创建了上述场景,在不修改random_page_cost
或STATISTICS
重新运行的情况下vacuum (analyze) table0;
,这也修改了行为并导致从顺序扫描切换到索引扫描。我相信这是由于 收集的统计数据具有不确定性analyze
。
这次,我不再能够触发索引的使用(我现在能够这样做,这主要归功于提到的答案),而是想了解如何在顺序扫描和索引扫描之间做出决定的细节。理想情况下,我希望能够预测查询是否:
explain analyze
select * from table0 where col0 like '%xyz%' limit 200;
将触发索引扫描或顺序扫描,了解xyz
,以及与数据库统计信息或设置相关的任何内容。以前,在类似问题的背景下,我被建议检查SELECT name, setting FROM pg_settings WHERE name = ANY ( '{shared_buffers, effective_cache_size, random_page_cost, effective_io_concurrency, work_mem}'::text[]);
。它返回(在修改默认设置之前):
name | setting
--------------------------+---------
effective_cache_size | 524288
effective_io_concurrency | 1
random_page_cost | 4
shared_buffers | 16384
work_mem | 4096
(5 rows)
我认为这些值会影响顺序扫描与索引扫描的决定。我希望存在一个具有两个可能输出的函数f :顺序扫描或索引扫描。我想象f将xyz
、、random_page_cost
收集的统计数据analyze
等作为输入。我想了解输入列表(即什么是等?)以及f如何处理它们。
如何预测查询计划是否涉及使用索引?