postgresql не использует индекс триграммы в текстовом столбце, но использует его в столбце varchar

По сути, я установил очень простую тестовую таблицу для проверки возможностей триграммного и полнотекстового индексирования в postgresql 9.1 (стандартная стабильная версия Debian).

Вот определения таблиц и индексов:

-- Table: fulltextproba
-- DROP TABLE fulltextproba;
CREATE TABLE fulltextproba
(
  id integer NOT NULL,
  text text,
  varchar600 character varying(600) COLLATE pg_catalog."C.UTF-8",
  CONSTRAINT id PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

-- Index: id_index
-- DROP INDEX id_index;
CREATE UNIQUE INDEX id_index
  ON fulltextproba
  USING btree
  (id );

-- Index: text_gin_fulltext_hun
-- DROP INDEX text_gin_fulltext_hun;
CREATE INDEX text_gin_fulltext_hun
  ON fulltextproba
  USING gin
  (to_tsvector('hungarian'::text, text) );

-- Index: text_gin_trgm
-- DROP INDEX text_gin_trgm;
CREATE INDEX text_gin_trgm
  ON fulltextproba
  USING gin
  (text COLLATE pg_catalog."C.UTF-8" gin_trgm_ops);

-- Index: varchar600
-- DROP INDEX varchar600;
CREATE INDEX varchar600
  ON fulltextproba
  USING btree
  (varchar600 COLLATE pg_catalog."C.UTF-8" varchar_pattern_ops);

-- Index: varchar600_gin_trgm
-- DROP INDEX varchar600_gin_trgm;
CREATE INDEX varchar600_gin_trgm
  ON fulltextproba
  USING gin
  (varchar600 COLLATE pg_catalog."C.UTF-8" gin_trgm_ops);

Моя проблема в том, что если я выполняю %foo% поиск, который должен использовать индекс триграммы, то при поиске в текстовом столбце этого не происходит:

SELECT COUNT(id) FROM public.fulltextproba WHERE text LIKE '%almáv%'
 count 
-------
   396
(1 row)

real    0m7.215s
user    0m0.020s
sys 0m0.004s
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Aggregate  (cost=657056.11..657056.12 rows=1 width=4)
   ->  Seq Scan on fulltextproba  (cost=0.00..657052.72 rows=1355 width=4)
         Filter: (text ~~ '%almáv%'::text)
(3 rows)

Но если я ищу в столбце varchar600, он использует индекс триграммы и, что неудивительно, намного быстрее:

SELECT COUNT(id) FROM public.fulltextproba WHERE varchar600 LIKE '%almáv%'
 count 
-------
   373
(1 row)

real    0m0.184s
user    0m0.052s
sys 0m0.004s
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Aggregate  (cost=5283.11..5283.12 rows=1 width=4)
   ->  Bitmap Heap Scan on fulltextproba  (cost=62.50..5279.73 rows=1355 width=4)
         Recheck Cond: ((varchar600)::text ~~ '%almáv%'::text)
         ->  Bitmap Index Scan on varchar600_gin_trgm  (cost=0.00..62.16 rows=1355 width=0)
               Index Cond: ((varchar600)::text ~~ '%almáv%'::text)
(5 rows)

Итак, основные вопросы:

  • Почему postgres не использует индекс триграмм в текстовом столбце.
  • Как можно заставить postgres использовать индекс? Должен ли я определить это как-то иначе?

postgresql indexing trigram
person P.Péter    schedule 19.08.2014    source источник

Ответы (1)


arrow_upward
3
arrow_downward

text прекрасно. Даже лучший вариант, как вы можете видеть в выводе EXPLAIN:

Index Cond: ((varchar600)::text ~~ '%almáv%'::text)

Несоответствие параметров сортировки

Непосредственной причиной, вероятно, является несоответствие параметров сортировки. Ваша таблица определена:

text text,   -- default collation is ???
varchar600 character varying(600) COLLATE pg_catalog."C.UTF-8"

Хотя оба индекса используют COLLATE pg_catalog."C.UTF-8". Какая у вас сортировка по умолчанию? Выход из:

SHOW LC_COLLATE;

Вы можете смешивать разные сопоставления. Повторный тест с:

SELECT COUNT(id) FROM public.fulltextproba
WHERE text COLLATE pg_catalog."C.UTF-8" LIKE '%almáv%'

Прочтите о поддержке сопоставления в Postgres.

Общие проблемы в вашем тесте

Очевидно, что у вас разные значения в любом из столбцов. Повторите тест с идентичными значениями.

Чтобы заставить Postgres использовать индекс, вы можете (только для отладки в вашем сеансе!):

SET enable_seqscan = off;

Затем попробуйте еще раз. Подробности:

Outlook для индекса GIN в Postgres 9.4

Грядущий Postgres 9.4 поставляется с рядом существенных улучшений для индексов GIN. В частности, они будут намного меньше и быстрее.

person Erwin Brandstetter    schedule 19.08.2014
comment
Спасибо, вы были правы! Моя локаль - hu_HU.UTF-8, и, указав сопоставление, сразу начал использоваться индекс триграммы (моя причина использования C.UTF-8 заключалась в том, что этот столбец содержит многоязычный текст). - person P.Péter; 21.08.2014
comment
Что касается размера таблицы, то вы ошибаетесь: таблица содержит 14155098 (~ 14M) строк, поэтому сканирование индекса справедливо быстрее. - person P.Péter; 21.08.2014
comment
С другой стороны: я знаю, что в каждом столбце есть разные значения (столбец varchar600 усечен до 600 символов; это имеет значение только примерно для 10% строк), поэтому у меня нет проблем с разницей в результате. Я не проверяю, передает ли postgres точные или последовательные результаты (я предполагаю, что могу ошибаться :)), я пытаюсь оценить общую производительность для некоторых базовых запросов в определенной среде, чтобы проверить возможность использования postgres. Пока все идет хорошо. :) - person P.Péter; 21.08.2014
comment
Кроме того, изменение параметров сортировки по умолчанию для test заставило исходный запрос использовать индекс. Установка параметров сортировки заняла два часа ... :-P Мораль: Выбирайте параметры сортировки с умом и заранее! - person P.Péter; 21.08.2014
comment
@ P.Péter: Я ошибочно принял количество строк за попадания за количество строк в выводе ANALYZE. Удалена эта часть. Тем не менее, все еще не понятно, почему мы видим rows=1355, но получаем гораздо меньшее число от count(). Я также добавил прогноз для индексов GIN в Postgres 9.4, который может вам понравиться. - person Erwin Brandstetter; 21.08.2014
comment
Я думаю, что rows=1355 - это предварительный набор строк, найденных по индексу триграммы, который дополнительно уточняется фильтром. - person P.Péter; 22.08.2014