我正在使用最新的 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如何处理它们。
如何预测查询计划是否涉及使用索引?
LIKE 运算符使用 pg_stats 中的 histogram_bounds 数组列来估计具有固定模式的该运算符的选择性。它将该数组用作表的随机样本(这样处理并不完全正确,但无论如何它就是这样做的)。
鉴于您的示例数据集,该数组可能有 0 个与“%abc%”匹配的条目,或者 1 个,具体取决于 ANALYZE 使用的随机样本如何分布。如果数组有一个匹配项,那么它将估计选择性约为 1/101。如果数组没有匹配项,那么它最初将估计选择性为 0,但稍后在使用之前将其限制为 0.0001。(几天前我对此有不同的描述,但后来我查看了源代码以弄清发生了什么,请参阅源代码)。根据发生的情况,它将执行顺序扫描或索引扫描,因为它认为其中一个或另一个会更快。
这个解释忽略了 most_common_vals 和 null_frac 的影响,它们通常被使用,但我认为它们在你的具体情况下没有太大区别。
为了预测会发生什么,你可以检查数组并计算其中有多少个元素与 %abc% 匹配:
其输出结果取决于 ANALYZE 获得的随机样本。样本似乎在四分之一的时间里包含 1/101 匹配,其余时间包含 0/101 匹配。
目前还不清楚你能用它做什么有用的事情,但你的问题似乎更多的是出于好奇而不是实用性。(要把它变成一个完整的预测需要做大量的工作,包括整合你的计划器设置、其他目录表等,这对于没有明显实际应用的东西来说将是一项非常艰巨的工作。)
Jeff对查询规划器如何估算提供了一流的见解。
至于您希望的功能:您已经拥有它:
EXPLAIN
。我们可能想出的任何自定义函数(在努力模仿大部分查询计划器功能之后)
EXPLAIN
最多只能接近。因为EXPLAIN
是真理的来源。如果您确实想要“此查询是否会使用该索引?”这个问题的布尔答案,您可以
EXPLAIN
在函数中循环输出。基本上:致电获取示例:
请注意,传递代码很容易受到 SQL 注入和其他问题的影响。因此这仅适用于受信任的用户。
也就是说,在大多数情况下,只看
EXPLAIN
输出就是最好的。