PostgreSQL citext 索引与较低的表达式索引性能

Question

我想决定是使用带有索引的citext列还是使用带有索引的text列lower()。

我执行了一些基准测试。令我惊讶的是，使用索引搜索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

我说的对吗？

劳伦兹·阿尔贝先生感谢您的回答。我按照你说的改变了上面的脚本。 结果：

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

但即使我在创建索引并在选择中使用 count(*) 后运行分析，任何事情都没有改变。索引扫描仍然继续，并在 lower() 上建立索引。

Answer 1

您的测试具有误导性。这里有两个问题：

1. 创建索引lowertextind后未运行ANALYZE。

   没有这一点，PostgreSQL不知道lower(a)是如何分布的，并且可能会产生错误的成本估算。

2. 通过使用SELECT *无意中允许仅索引扫描用于第一个查询，而不是第二个查询。这是因为第一个索引包含所有表列，但第二个索引不包含。

   由于第二个索引不包含a，该值必须从表中获取，从而导致额外的工作。

   您可以使用SELECT count(*) FROM ...进行更公平的基准测试。