postgresql - 为什么 postgres 不使用 BTree 索引来满足类似 'CONSTANT_PREFIX_%' 的条件？

Question

我有一个带有 B-Tree 索引的表:

CREATE INDEX idx_test_report_accession on test_report (accession);

我使用 explain 运行以下查询:

"QUERY PLAN"
"Seq Scan on public.test_report r  (cost=0.00..705829.12 rows=30694 width=1140) (actual time=0.143..6253.818 rows=11094 loops=1)"
"  Output: detected_transcript_translation_id, peptide_spectrum_match_id, accession, peptide, modified_sequence, var_mod, spectrum_title, spectrum_file, confidence, mz, retention_time, precursor_mz_error_ppm, sample_name, transcript, gene_symbol, prot_seq, ref_based_location, external_identifier, experiment_name, report_filename, line_number, experiment_path"
"  Filter: (r.accession ~~ 'IP_%'::text)"
"  Rows Removed by Filter: 4296116"
"Planning time: 1.331 ms"
"Execution time: 6255.560 ms"

似乎认为表中只有30694行，决定不使用索引。鉴于 like 通配符是后缀，我认为没有理由不使用索引...

行数是:

SELECT count(*) from test_report r;
4307210

匹配行的数量相当少:

SELECT count(*) from test_report r WHERE r.accession like 'IP_%';
11094

注意事项:

• Postgres 版本为 9.4

Answer 1

在许多排序规则下，共享相同前缀的单词可能不会按排序顺序彼此相邻出现。 See here例如。这意味着不能有效地使用具有这些排序规则的索引来完成前缀查询。

除非您的数据库排序规则是“C”，否则您的默认索引将无法用于前缀搜索。您可以手动指定要在支持它的索引中使用的排序规则或运算符类:

create index on foobar (x collate "C");

或

create index on foobar (x text_pattern_ops);

我只是使用“C”排序规则创建我的所有数据库(即使您需要使用 UTF8 编码也可以这样做)。有些人不喜欢它的排序方式，但这主要是由于区分大小写。从来没有人向我提示过“嘿，你需要按照 en_US.UTF-8 归类规则进行排序”。这可能是因为几乎没有人知道这些规则是什么。