使用gin_trgm_ops
或gist_trgm_ops
索引时,我看到一些奇怪的行为。ILIKE
使用 say或~
并搜索 ascii 短语与多字节字符短语时,计划似乎有很大差异。就好像当操作数是非 ASCII 操作数时成本更高。
我所看到的是预期的吗?其原因何在?
我在最新的 Postgreql 12 和 13 上尝试过。
这是一个场景:
CREATE DATABASE postgres
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
-- snip
CREATE TABLE test_table (
id uuid PRIMARY KEY,
label varchar
);
-- insert 1m rows
VACUUM ANALYZE test_table;
在数据集中,我有 10 个包含 的标签'acl'
和 10 个包含'定す'
.
使用GIN索引时
CREATE INDEX test_table_label_gin_idx
ON test_table USING gin
(label gin_trgm_ops);
我看到以下内容。
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%定す%' LIMIT 100;
Limit (cost=1000.00..16573.18 rows=100 width=52) (actual time=392.153..395.095 rows=10 loops=1)
-> Gather (cost=1000.00..16728.91 rows=101 width=52) (actual time=392.135..394.830 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_table (cost=0.00..15718.81 rows=42 width=52) (actual time=382.922..388.082 rows=3 loops=3)
Filter: ((label)::text ~~* '%定す%'::text)
Rows Removed by Filter: 338417
Planning Time: 0.656 ms
Execution Time: 395.233 ms
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%acl%' LIMIT 100;
Limit (cost=28.78..400.51 rows=100 width=52) (actual time=0.072..0.406 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=28.78..404.23 rows=101 width=52) (actual time=0.053..0.197 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%acl%'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on test_table_label_gin_idx (cost=0.00..28.76 rows=101 width=0) (actual time=0.025..0.034 rows=10 loops=1)
Index Cond: ((label)::text ~~* '%acl%'::text)
Planning Time: 0.231 ms
Execution Time: 0.542 ms
患有胃肠道间质瘤
DROP INDEX test_table_label_gin_idx;
CREATE INDEX test_table_label_gist_idx
ON test_table USING gist
(label gist_trgm_ops);
我懂了
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%定す%' LIMIT 100;
Limit (cost=13.19..384.92 rows=100 width=52) (actual time=303.772..1557.498 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=13.19..388.64 rows=101 width=52) (actual time=303.752..1557.286 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%定す%'::text)
Rows Removed by Index Recheck: 1015250
Heap Blocks: exact=10431
-> Bitmap Index Scan on test_table_label_gist_idx (cost=0.00..13.17 rows=101 width=0) (actual time=301.046..301.053 rows=1015260 loops=1)
Index Cond: ((label)::text ~~* '%定す%'::text)
Planning Time: 0.215 ms
Execution Time: 1557.643 ms
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%acl%' LIMIT 100;
Limit (cost=13.19..384.92 rows=100 width=52) (actual time=257.385..257.751 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=13.19..388.64 rows=101 width=52) (actual time=257.366..257.551 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%acl%'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on test_table_label_gist_idx (cost=0.00..13.17 rows=101 width=0) (actual time=257.319..257.328 rows=10 loops=1)
Index Cond: ((label)::text ~~* '%acl%'::text)
Planning Time: 0.377 ms
Execution Time: 257.948 ms
仅仅改变操作数的字符就可以很大程度地改变计划。
编辑
SELECT show_trgm('定す');
"{0x145ed8,0x6628fa,0x6cb12d}"
SELECT encode('定す', 'escape')
\345\256\232\343\201\231
问题是您的查询中没有三元组。pg_trgm 使用散列来表示宽字符的方式使得仅根据 show_trgm 输出很难看到这一点,但我们可以看到输出中只有三个三元组。但这些三元组都不能在 ILIKE 的上下文中使用。如果我们查看 的输出
show_trgm('ab')
,我们会发现它也具有三个三元组:但请注意,它们三个中至少有一个空格,并且这些空格不可用于 ILIKE 的图案一侧。(这不是一个笼统的规则。如果模式中实际上有空格或标点符号,那么一些包含空格的三元组将是可用的;只有那些包含“人造”空格的三元组不可用。)
如果我们对“acl”执行同样的操作,我们会看到它有 4 个三元组,其中一个没有空格,因此可用。
如果您要使用字符串“%ab%”重复查询,我想您会发现该查询也具有很高的成本估计并选择“错误”的查询计划。因为规划者在估计和选择计划之前提取可用的三元组,发现它们的数量为零,并且知道这对性能来说将是可怕的。
我认为三元组方法确实不太适合表意语言。但我不知道替代方案是什么。