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

我想决定使用citext带有索引的列或使用text索引为 on 的列lower().

我执行了一些基准测试。令我惊讶的是，索引打开的搜索lower()导致索引扫描，但在citext如果我只进行索引扫描。我本来期待索引lower()也导致仅索引扫描。

此外，总成本citext指数为 4.44，但指数为lower()，总成本为8.44。

所以我首先想到的是citext列索引优于 a 上的表达式索引text column.

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

但即使我在创建索引并在 select.Index Scan 中使用 count(*) 后运行分析，任何事情都没有改变。Index Scan 仍然继续使用 lower() 上的索引。

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

1. 你没有跑ANALYZE创建索引后lowertextind.

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

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

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

   你可以使用SELECT count(*) FROM ...以获得更公平的基准。