PostgreSQL hstore: повысить производительность LIKE с помощью индекса?

У меня есть большой набор денормализованных данных с неравномерными атрибутами (некоторые атрибуты есть, некоторые нет) и вставляю его в одно шоу hstore столбец. Этот столбец содержит около 300 пар ключ / значение с общим размером 5000 символов в строке. Я хочу выполнять строковые поисковые запросы по некоторым из этих атрибутов с операторами ilike и OR для всего 100000 строк.

Запрос:

SELECT hstore->'a' AS a, hstore->'b' AS b,hstore->'c' AS c
  FROM table
  WHERE
       hstore->'x' ILIKE '123%' 
    or hstore->'y' ILIKE '123%'
    or hstore->'z' ILIKE '123%'

С этим запросом к неиндексированной таблице я получаю более 500 мс времени выполнения (explain analyze).

С моей старой индексированной таблицей РСУБД, где каждый атрибут находится в одном столбце, я добился гораздо большей производительности, хотя и менее гибкой.

Я пробовал разные / несколько индексов для этих атрибутов hstore, например

CREATE INDEX idx_table_hstore ON table( (hstore->'a') )

и по одному индексу для каждого, но производительность такая же, как и при отсутствии индекса.

Насколько я понимаю, индексы GIN / GIST не имеют особого смысла, поскольку столбец довольно большой и не требует геометрических операторов (я могу ошибаться в этом).

Какой метод индексации вы бы использовали в такой ситуации для достижения аналогичной или даже лучшей производительности, чем при использовании классической модели?


postgresql indexing sql-like hstore
person Peter    schedule 24.10.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Это во многом зависит от вашего конкретного варианта использования, что не совсем понятно. В вашем примере запроса вы проверяете значения ключей x, y и z. Если эти три ключа (или какое-то относительно небольшое подмножество всех ваших ключей) являются единственными, которые используются для поиска, вы можете подумать о перемещении их в их собственные столбцы - тогда ваши поля поиска будут фиксированными, но у вас по-прежнему есть гибкость столбца hstore.

Также неясно, создали ли вы индексы для каждого отдельного ключа или только для столбцов подстановки. Если вы выполнили один из них для каждого ключа, вы говорите о примерно 300 индексах (вы упомянули о наличии около 300 ключей), а затем вы также отказываетесь от некоторой гибкости hstore (создавая один из этих индексов для каждого ключа). Я бы остановился здесь только на столбцах поиска (x, y, z) и немного изменил их, чтобы они выглядели так:

create index idx_t_h_x on t ((lower(h->'x')));

Указанный вами индекс не поддерживает оператор ilike, поэтому вам нужно проиндексировать нижние (или верхние) значения, а затем изменить свой предикат, чтобы он соответствовал, например:

SELECT hstore->'a' AS a, hstore->'b' AS b,hstore->'c' AS c
FROM table
WHERE lower(hstore->'x') LIKE '123%'

Кроме того, индексы gin / gist предназначены не только для геометрических операций (фактически, буква «g» в обоих именах является «обобщенной» - они предназначены для многоцелевого использования). Если вы посмотрите документацию по модулю hstore, вы увидите, какие Операторы поддерживаются индексом gist или gin в столбце hstore *. Один из них - «?», Который проверяет наличие ключа. В зависимости от разреженности ваших ключей поиска (x, y, z) вам может повезти, если вы определите индекс gist или gin в столбце и добавите дополнительное условие, например «where (hstore? 'X' and hstore-> ' x 'ilike' 123% ') "; предполагая, что не многие строки имеют ключ x, это должно дать вам приличный импульс, иначе, если ключ x находится почти в каждой строке, вы вернетесь к полному сканированию таблицы.

Когда дело доходит до решения, использовать ли gist или gin, если вы просмотрите документы postgres, и здесь, на SO, вы найдете некоторые рекомендации, в основном, gin, как правило, быстрее ищет, но занимает больше места и медленнее строить и поддерживать (это означает, что имейте в виду, пишете ли вы или читаете данные больше) - я не уверен, есть ли конкретные рекомендации для типа hstore.

Да, и, очевидно, все это предполагает, что ваш сервер настроен соответствующим образом для вашего оборудования и использования. Как я уже отмечал, предоставленный вами индекс не поддерживает оператор ilike, поэтому он никогда не будет использоваться. Как только вы получите индекс, который, по вашему мнению, следует использовать, вы можете попробовать отключить сканирование таблиц (проверьте конфигурацию для enable_seqscan), чтобы узнать, сможете ли вы выяснить, почему планировщик не использует его. Если ваша конфигурация находится вне коробки, у вас может быть установлено высокое значение random_page_cost, вы можете выполнять много сортировок на диске, если ваш work_mem недостаточно высокий, и т. Д.

* Просто чтобы указать на тему, не все типы индексов поддерживают все операторы.

person yieldsfalsehood    schedule 24.10.2013