Почему небольшое изменение в поисковом запросе так сильно замедляет запрос?

Question

У меня есть следующий запрос в PostgreSQL (9.5.1):

select e.id, (select count(id) from imgitem ii where ii.tabid = e.id and ii.tab = 'esp') as imgs,
 e.ano, e.mes, e.dia, cast(cast(e.ano as varchar(4))||'-'||right('0'||cast(e.mes as varchar(2)),2)||'-'|| right('0'||cast(e.dia as varchar(2)),2) as varchar(10)) as data,
 pl.pltag, e.inpa, e.det, d.ano anodet, coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')' determinador, d.tax, coalesce(v.val,v.valf)||' '||vu.unit as altura,
 coalesce(v1.val,v1.valf)||' '||vu1.unit as DAP, d.fam, tf.nome família, d.gen, tg.nome gênero, d.sp, ts.nome espécie, d.inf, e.loc, l.nome localidade, e.lat, e.lon
from esp e
left join det d on e.det = d.id
left join tax tf on d.fam = tf.oldfam
left join tax tg on d.gen = tg.oldgen
left join tax ts on d.sp = ts.oldsp
left join tax ti on d.inf = ti.oldinf
left join loc l on e.loc = l.id
left join pess p on p.id = d.detby
left join var v on v.esp = e.id and v.key = 265
left join varunit vu on vu.id = v.unit
left join var v1 on v1.esp = e.id and v1.key = 264
left join varunit vu1 on vu1.id = v1.unit
left join pl on pl.id = e.pl
WHERE unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')

Требуется 430 мс, чтобы получить 1129 строк из общего количества 9250 в esp Таблица.

Если я изменю условие поиска с %vicen% в %vicent% (добавляя 't'), для получения тех же 1129 строк требуется 431 мс.

Упорядочив по столбцу поиска, по возрастанию и по убыванию, я вижу, что все 1129 строк имеют одинаковое имя в обоих случаях.

Теперь странно: если я изменю поисковый запрос с %vicent% в %vicenti% (добавив 'i'), теперь требуется невероятные 24,4 секунды, чтобы получить те же 1129 строк!

Искомый термин всегда в первом coalesceт.е. coalesce(p.abrev,''), Я ожидаю, что запрос будет выполняться медленнее или быстрее, в зависимости от размера искомой строки, но не так сильно!! Кто-нибудь имеет представление о том, что происходит?

Результаты EXPLAIN ANALYZE (здесь будет превышено ограничение в 30 тыс. символов):

За %vicen%: http://explain.depesz.com/s/2XF

За %vicenti%: http://explain.depesz.com/s/dEc6

Answer 1

Зачем?

Причина в следующем:

Быстрый запрос:

-> Hash Left Join (стоимость =1378.60..2467.48 строк =15 ширина =79) (фактическое время =41.759..85.037 строк =1129 циклов =1)
      ...
      Фильтр: (unaccent(((((COALESCE(p.abrev, '':: изменение символов))::text || ' ('::text) || (COALESCE(p.prenome, '':: изменение символов)))::text) || ')'::text)) ~~* (...) 

Медленный запрос:

-> Hash Left Join (стоимость =1378.60..2467.48 строк = 1 ширина =79) (фактическое время =35.084..80.209 строк =1129 циклов =1)
      ...
      Фильтр: (unaccent(((((COALESCE(p.abrev, '':: изменение символов))::text || ' ('::text) || (COALESCE(p.prenome, '':: изменение символов)))::text) || ')'::text)) ~~* unacc (...)

Расширение шаблона поиска другим символом приводит к тому, что Postgres принимает еще меньше обращений. (Как правило, это разумная оценка.) У Postgres, очевидно, нет достаточно точной статистики (ни одной, фактически, продолжайте читать), чтобы ожидать того же числа попаданий, которое вы действительно получаете.

Это приводит к переключению на другой план запроса, который еще менее оптимален для фактического количества обращений. rows=1129,

Решение

Предполагая текущий Postgres 9.5, так как он не был объявлен.

Одним из способов улучшить ситуацию является создание индекса выражения для выражения в предикате. Это заставляет Postgres собирать статистику для фактического выражения, что может помочь запросу, даже если сам индекс не используется для запроса. Без индекса статистика по выражению вообще отсутствует. И если все сделано правильно, индекс может быть использован для запроса, это даже намного лучше. Но есть несколько проблем с вашим текущим выражением:

unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')

Рассмотрим этот обновленный запрос, основанный на некоторых предположениях о ваших нераскрытых определениях таблиц:

SELECT e.id
     , (SELECT count(*) FROM imgitem
        WHERE tabid = e.id AND tab = 'esp') AS imgs -- count(*) is faster
     , e.ano, e.mes, e.dia
     , e.ano::text || to_char(e.mes2, 'FM"-"00')
                   || to_char(e.dia,  'FM"-"00') AS data    
     , pl.pltag, e.inpa, e.det, d.ano anodet
     , format('%s (%s)', p.abrev, p.prenome) AS determinador
     , d.tax
     , coalesce(v.val,v.valf)   || ' ' || vu.unit  AS altura
     , coalesce(v1.val,v1.valf) || ' ' || vu1.unit AS dap
     , d.fam, tf.nome família, d.gen, tg.nome AS gênero, d.sp
     , ts.nome AS espécie, d.inf, e.loc, l.nome localidade, e.lat, e.lon
FROM      pess    p                        -- reorder!
JOIN      det     d   ON d.detby   = p.id  -- INNER JOIN !
LEFT JOIN tax     tf  ON tf.oldfam = d.fam
LEFT JOIN tax     tg  ON tg.oldgen = d.gen
LEFT JOIN tax     ts  ON ts.oldsp  = d.sp
LEFT JOIN tax     ti  ON ti.oldinf = d.inf  -- unused, see @joop's comment
LEFT JOIN esp     e   ON e.det     = d.id
LEFT JOIN loc     l   ON l.id      = e.loc
LEFT JOIN var     v   ON v.esp     = e.id AND v.key  = 265
LEFT JOIN varunit vu  ON vu.id     = v.unit
LEFT JOIN var     v1  ON v1.esp    = e.id AND v1.key = 264
LEFT JOIN varunit vu1 ON vu1.id    = v1.unit
LEFT JOIN pl          ON pl.id     = e.pl
WHERE f_unaccent(p.abrev)   ILIKE f_unaccent('%' || 'vicenti' || '%') OR
      f_unaccent(p.prenome) ILIKE f_unaccent('%' || 'vicenti' || '%');

Основные моменты

Зачем f_unaccent()? Так как unaccent() не может быть проиндексировано. Прочитай это:

• Поддерживает ли PostgreSQL сортировку без учета акцента?

Я использовал описанную там функцию, чтобы разрешить следующую (рекомендованную!) Функциональную триграмму индекса GIN:

CREATE INDEX pess_unaccent_nome_trgm_idx ON pess
USING gin (f_unaccent(pess) gin_trgm_ops, f_unaccent(prenome) gin_trgm_ops);

Если вы не знакомы с индексами триграмм, сначала прочтите это:

• Варианты производительности запросов PostgreSQL LIKE

И возможно:

• Сопоставление с шаблоном LIKE, SIMILAR TO или регулярными выражениями в PostgreSQL

Обязательно запустите последнюю версию Postgres (в настоящее время 9.5). Значительно улучшены индексы GIN. И вас заинтересуют улучшения в pg_trgm 1.2, выпуск которого запланирован с выходом Postgres 9.6:

• Поиск триграмм становится намного медленнее, так как строка поиска становится длиннее

---

Подготовленные операторы являются распространенным способом выполнения запросов с параметрами (особенно с текстом из пользовательского ввода). Postgres должен найти план, который лучше всего подходит для любого заданного параметра. Добавьте подстановочные знаки в качестве констант к поисковому запросу, например так:

f_unaccent(p.abrev) ILIKE f_unaccent('%' || 'vicenti' || '%')

('vicenti' будет заменен параметром.) Таким образом, Postgres знает, что мы имеем дело с шаблоном, который не привязан ни к левому, ни к правому - который допускает разные стратегии. Связанный ответ с более подробной информацией:

• Влияние на производительность пустого LIKE в подготовленном утверждении

Или, может быть, перепланировать запрос для каждого поискового запроса (возможно, с использованием динамического SQL в функции). Но убедитесь, что время планирования не съедает возможного прироста производительности.

---

WHERE состояние столбцов в pess противоречит LEFT JOIN, Postgres вынужден преобразовать это в INNER JOIN, Что еще хуже, соединение приходит поздно в дереве соединений. А поскольку Postgres не может изменить порядок ваших соединений (см. Ниже), это может стать очень дорогим. Переместить стол на первую позицию в FROM пункт, чтобы устранить строки рано. Следующий LEFT JOIN s не удаляют строки по определению. Но при таком количестве таблиц важно перемещать объединения, которые могут умножать строки до конца.

---

Вы объединяете 13 таблиц, 12 из которых с LEFT JOIN который оставляет 12! возможные комбинации - или 11! * 2! если мы возьмем один LEFT JOIN во внимание, что это действительно INNER JOIN, Это слишком много для Postgres, чтобы оценить все возможные варианты для лучшего плана запроса. Прочитать о join_collapse_limit:

• Пример запроса для отображения ошибки оценки мощности в PostgreSQL
• SQL INNER JOIN для нескольких таблиц, равных синтаксису WHERE

Настройка по умолчанию для join_collapse_limit это 8, что означает, что Postgres не будет пытаться изменить порядок таблиц в вашем FROM пункт и порядок таблиц является актуальным.

Один из способов обойти это - разделить критичную по производительности часть на CTE, как прокомментировал @joop. Не установлен join_collapse_limit гораздо большее или меньшее время для планирования запросов, включающих много соединенных таблиц, ухудшится.

---

О вашей объединенной дате по имени data:

cast(cast(e.ano as varchar(4))||'-'||right('0'||cast(e.mes as varchar(2)),2)||'-'|| right('0'||cast(e.dia as varchar(2)),2) as varchar(10)) as data

Предполагая, что вы строите из трех числовых столбцов для года, месяца и дня, которые определены NOT NULL используйте это вместо:

e.ano::text || to_char(e.mes2, 'FM"-"00')
            || to_char(e.dia,  'FM"-"00') AS data

О FM Модификатор шаблона шаблона:

• Проверьте целое число в строковом массиве

Но на самом деле, вы должны хранить дату как тип данных date начать с.

---

Также упрощено:

format('%s (%s)', p.abrev, p.prenome) AS determinador

Не сделает запрос быстрее, но он будет чище. Увидеть format(),

---

Перво-наперво, применяются все обычные советы по оптимизации производительности:

• Не позволяйте PostgreSQL иногда выбирать неверный план запроса

---

Если вы все сделаете правильно, вы должны увидеть намного более быстрые запросы для всех шаблонов.

Answer 2

Способ уменьшить размер таблицы диапазонов состоит в том, чтобы выжать тривиальную часть запроса в CTE, например AS:

WITH zzz AS (
        SELECT l.id, l.nome
        , coalesce(v.val,v.valf)||' '||vu.unit as altura
        , coalesce(v1.val,v1.valf)||' '||vu1.unit as DAP
        FROM loc l 
         left join var v on v.esp = l.id and v.key = 265
         left join varunit vu on vu.id = v.unit
         left join var v1 on v1.esp = l.id and v1.key = 264
         left join varunit vu1 on vu1.id = v1.unit
        )
select e.id, (select count(id) from imgitem ii
                where ii.tabid = e.id and ii.tab = 'esp'
                ) as imgs
        , e.ano, e.mes, e.dia
        , cast(cast(e.ano as varchar(4))||'-'||right('0'||cast(e.mes as varchar(2)),2)||'-'|| right('0'||cast(e.dia as varchar(2)),2) as varchar(10)) as data
        , pl.pltag, e.inpa, e.det, d.ano anodet
        , coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')' determinador
        , d.tax

        , zzz.altura as altura
        , zzz.DAP as DAP

        , d.fam, tf.nome família
        , d.gen, tg.nome gênero
        , d.sp , ts.nome espécie
        , d.inf, e.loc
        , zzz.nome AS localidade
        , e.lat, e.lon
from esp e
left join det d on e.det = d.id         -- these could possibly be
left join pess p on p.id = d.detby      -- plain joins
        -- 
left join tax tf on d.fam = tf.oldfam
left join tax tg on d.gen = tg.oldgen
left join tax ts on d.sp = ts.oldsp
 -- ### commented out, since it is never referred
 -- ### left join tax ti on d.inf = ti.oldinf
left join pl on pl.id = e.pl
left JOIN zzz ON zzz.id = e.loc
        -- 
WHERE unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')
        ;

[не проверено, так как у меня нет определений таблиц]