Блог

Главная - Вопросы по программированию - Индекс PostgreSQL citext против более низкой производительности индекса выражения

Индекс PostgreSQL citext против более низкой производительности индекса выражения

#postgresql #query-performance #database-indexes

#postgresql #запрос-производительность #база данных-индексы

Вопрос:

Я хочу выбрать между использованием 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(*) в select .Сканирование индекса все еще продолжается с индексом на lower() .

Комментарии:

1. Я думаю, что причиной этого обычно было бы «индекс не может преобразовать обратно значение в нижнем регистре, поэтому ему нужно заглянуть в таблицу», но SELECT lower (a) также не будет использовать только индекс.

Ответ №1:

Ваш тест вводит в заблуждение. Здесь есть две проблемы:

  1. Вы не запустились ANALYZE после создания индекса lowertextind .

    Без этого PostgreSQL не знает, как lower(a) распределяется, и, вероятно, приведет к неправильной оценке затрат.

  2. Используя SELECT * , вы непреднамеренно разрешили использовать сканирование только индекса для первого запроса, но не для второго. Это потому, что первый индекс содержит все столбцы таблицы, а второй — нет.

    Поскольку второй индекс не содержит a , значение должно быть извлечено из таблицы, что приводит к дополнительной работе.

    Вы могли бы использовать SELECT count(*) FROM ... для более справедливого теста.