PostgreSQL citext索引与lower表达式索引的性能对比

Question

我想在使用带索引的citext列和使用lower()上的带索引的text列之间做出决策。

我进行了一些基准测试。令我惊讶的是，对于使用lower()索引的搜索导致了索引扫描，但在citext情况下，我得到了仅索引扫描。我原本期望在lower()索引上也会产生仅索引扫描。

此外，使用citext索引的总成本为4.44，但使用lower()索引的总成本为8.44。

所以，我首先想到的是citext列索引比text列上的表达式索引更好。

CREATE TABLE test_citext(a citext NOT NULL);

INSERT INTO test_citext
   SELECT cast(x as text)
   FROM generate_series(1, 1000000) AS x;

VACUUM (FREEZE, ANALYZE) test_citext;

create index citextind on test_citext(a);

Select * from test_citext where a = 'test';
--Index Only Scan.Total cost 4.44

CREATE TABLE test_textlowerindex(a text NOT NULL);

INSERT INTO test_textlowerindex
   SELECT cast(x as text)
   FROM generate_series(1, 1000000) AS x;

VACUUM (FREEZE, ANALYZE) test_textlowerindex;

create index lowertextind on test_textlowerindex(lower(a));

Select * from test_textlowerindex where lower(a) = 'test';
--Index Scan.Total cost 8.44

我没错吧？

感谢Laurenz Albe先生的回答。我按照您的建议修改了上述脚本。 结果：

CREATE TABLE test_citext(a citext NOT NULL);

INSERT INTO test_citext
   SELECT cast(x as text)
   FROM generate_series(1, 1000000) AS x;

create index citextind on test_citext(a);

VACUUM (FREEZE, ANALYZE) test_citext;

Select count(*) from test_citext where a = 'test';
--Index Only Scan 4.44 + 4.46

CREATE TABLE test_textlowerindex(a text NOT NULL);

INSERT INTO test_textlowerindex
   SELECT cast(x as text)
   FROM generate_series(1, 1000000) AS x;

create index lowertextind on test_textlowerindex(lower(a));

VACUUM (FREEZE, ANALYZE) test_textlowerindex;

Select count(*) from test_textlowerindex where lower(a) = 'test';
--Index Scan 8.44 + 8.46

但是即使我在创建索引后运行分析并在select语句中使用count(*)，仍然没有任何变化。使用lower()函数的索引扫描仍然继续。

- fatih kosal

---

我认为导致这个问题的原因通常是“索引无法将小写值转换回去，因此需要查阅表格”，但使用SELECT lower(a)也无法使用仅索引。 - Łukasz Kamiński

Answer 1

你的测试结果是误导性的，存在两个问题：

1. 你在创建索引 `lowertextind` 后没有运行 `ANALYZE` 命令。如果没有运行该命令，PostgreSQL 不知道如何分配 `lower(a)` 并且可能会计算错误的成本评估。 2. 在第一个查询中，你使用了 `SELECT *` 来意外地允许了使用“仅索引扫描”，但在第二个查询中却没有。这是因为第一个索引包含所有表列，而第二个索引不包括。由于第二个索引不包含 `a`，需要从表中获取该值，导致额外的工作量。你可以使用 `SELECT count(*) FROM ...` 进行更公正的基准测试。

- Laurenz Albe