Полнотекстовый поиск PostgreSQL — выбор неэффективного плана выполнения

Предположим, следующий запрос - таблицы, столбцы и ключи должны быть довольно очевидными (в противном случае, пожалуйста, спросите).

SELECT DISTINCT p.IDProduct
FROM Catalog.Catalog c
INNER JOIN Catalog.Product p ON (
    p.FKIDCatalog=c.IDCatalog
)
INNER JOIN Catalog.ProductLanguage pl ON (
    pl.FKIDProduct=p.IDProduct
    AND (
        pl.FKIDLanguage='de_DE'
        OR pl.FKIDLanguage=c.FKIDLanguage
    )
)
WHERE to_tsvector(SearchConfig, COALESCE(pl.DescriptionShort, '') || ' ' || COALESCE(pl.DescriptionLong, '') || ' ' || COALESCE(pl.KeywordList, '')) @@ to_tsquery('''vorschlaghammer'':*') 
AND c.IDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23)

Предложение IN определяется разрешением пользователя и создает пространство поиска из ~ 1,3 млн продуктов (из 2 млн) со 181 совпадением — довольно типичный вариант использования. К сожалению, для возврата результата требуется 49 секунд. EXPLAIN (analyze, buffers, format text) показывает следующий план запроса:

 Unique  (cost=59887.83..59887.89 rows=13 width=4) (actual time=48934.329..48972.548 rows=181 loops=1)
   Buffers: shared hit=5386635
   ->  Sort  (cost=59887.83..59887.86 rows=13 width=4) (actual time=48934.328..48972.520 rows=181 loops=1)
         Sort Key: p.idproduct
         Sort Method: quicksort  Memory: 33kB
         Buffers: shared hit=5386635
         ->  Gather  (cost=1045.52..59887.59 rows=13 width=4) (actual time=908.689..48972.460 rows=181 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=5386635
               ->  Nested Loop  (cost=45.52..58886.29 rows=5 width=4) (actual time=3215.182..48926.270 rows=60 loops=3)
                     Join Filter: (((pl.fkidlanguage)::text = 'de_DE'::text) OR ((pl.fkidlanguage)::text = (c.fkidlanguage)::text))
                     Buffers: shared hit=5386635
                     ->  Hash Join  (cost=45.09..57038.74 rows=1319 width=10) (actual time=0.167..249.085 rows=438115 loops=3)
                           Hash Cond: (p.fkidcatalog = c.idcatalog)
                           Buffers: shared hit=44799
                           ->  Parallel Seq Scan on product p  (cost=0.00..54420.03 rows=979803 width=8) (actual time=0.015..66.259 rows=783365 loops=3)
                                 Buffers: shared hit=44622
                           ->  Hash  (cost=44.98..44.98 rows=9 width=10) (actual time=0.075..0.076 rows=9 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 Buffers: shared hit=77
                                 ->  Index Scan using catalog_pkey on catalog c  (cost=0.28..44.98 rows=9 width=10) (actual time=0.033..0.068 rows=9 loops=3)
                                       Index Cond: (idcatalog = ANY ('{5,24,6,7,11,12,8,1,23}'::integer[]))
                                       Buffers: shared hit=77
                     ->  Index Scan using productlanguage_pkey on productlanguage pl  (cost=0.43..1.39 rows=1 width=10) (actual time=0.111..0.111 rows=0 loops=1314345)
                           Index Cond: (fkidproduct = p.idproduct)
                           Filter: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=5341836
 Planning:
   Buffers: shared hit=65
 Planning Time: 1.905 ms
 Execution Time: 48972.635 ms
(33 rows)

Я не совсем знаком с планами выполнения, но я бы сказал, что неразумно сначала получать 1,3 миллиона продуктов, а затем перебирать их все, чтобы проверить полнотекстовое условие; конечно, время запроса сокращается, если я сужаю набор каталогов и наоборот. Но, если заменить предложение IN, например, на AND c.IDCatalog<29 (который выбирает все основные каталоги), оптимизатор запросов делает то, что я ожидал от него в первую очередь (вероятно, потому что он все равно должен учитывать «почти все» продукты):

 Unique  (cost=63069.02..63073.42 rows=37 width=4) (actual time=36.778..39.404 rows=265 loops=1)
   Buffers: shared hit=1395
   ->  Gather Merge  (cost=63069.02..63073.33 rows=37 width=4) (actual time=36.777..39.360 rows=265 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1395
         ->  Sort  (cost=62068.99..62069.03 rows=15 width=4) (actual time=1.269..1.277 rows=88 loops=3)
               Sort Key: p.idproduct
               Sort Method: quicksort  Memory: 37kB
               Buffers: shared hit=1395
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Hash Join  (cost=320.56..62068.70 rows=15 width=4) (actual time=0.926..1.229 rows=88 loops=3)
                     Hash Cond: (p.fkidcatalog = c.idcatalog)
                     Join Filter: (((pl.fkidlanguage)::text = 'de_DE'::text) OR ((pl.fkidlanguage)::text = (c.fkidlanguage)::text))
                     Buffers: shared hit=1381
                     ->  Nested Loop  (cost=294.26..62031.43 rows=4171 width=14) (actual time=0.761..1.039 rows=88 loops=3)
                           Buffers: shared hit=1240
                           ->  Parallel Bitmap Heap Scan on productlanguage pl  (cost=293.83..35768.94 rows=4171 width=10) (actual time=0.756..0.819 rows=88 loops=3)
                                 Recheck Cond: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                                 Heap Blocks: exact=133
                                 Buffers: shared hit=180
                                 ->  Bitmap Index Scan on productlanguage_descriptionshort_descriptionlong_keywordlist  (cost=0.00..291.33 rows=10010 width=0) (actual time=2.208..2.209 rows=265 loops=1)
                                       Index Cond: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                                       Buffers: shared hit=47
                           ->  Index Scan using product_pkey on product p  (cost=0.43..6.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=265)
                                 Index Cond: (idproduct = pl.fkidproduct)
                                 Buffers: shared hit=1060
                     ->  Hash  (cost=25.99..25.99 rows=25 width=10) (actual time=0.097..0.098 rows=21 loops=3)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           Buffers: shared hit=41
                           ->  Index Scan using catalog_pkey on catalog c  (cost=0.28..25.99 rows=25 width=10) (actual time=0.036..0.085 rows=21 loops=3)
                                 Index Cond: (idcatalog < 29)
                                 Buffers: shared hit=41
 Planning:
   Buffers: shared hit=68
 Planning Time: 1.903 ms
 Execution Time: 39.517 ms
(38 rows)

Это на 3 величины быстрее, и я ожидаю, что PostgreSQL сможет отфильтровать 265 результирующих строк еще за несколько миллисекунд, чтобы добавить исходное предложение IN.

Конечно, PostgreSQL может только догадываться, в какую сторону идти, но очень неудовлетворительно, если он принимает столь неверное решение. На самом деле время отклика в 49 секунд совершенно неприемлемо для моих пользователей, тогда как 40 мс были бы едва заметны. Я никогда не сталкивался с чем-то подобным с неполнотекстовыми запросами.

Так что вопросов может быть два: а) как исправить/обойти этот конкретный вариант использования б) как вообще работать с fulltext-запросами с точки зрения производительности?

Если вам достаточно простого временного интервала, SET enable_nestloop = off; на время запроса должен улучшить производительность.

Laurenz Albe 22.04.2022 23:01

@LaurenzAlbe: Спасибо, это именно то исправление, которое я искал. Посмотрим, придумает ли кто-нибудь более общий подход к этой теме, но на данный момент это действительно полезно.

SlowFox 22.04.2022 23:35

Что произойдет, если вы сравните p.FKIDCatalog, а не c.IDCatalog со списком IN?

jjanes 23.04.2022 00:33
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
1
3
39
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Основной корень проблемы, по-видимому, заключается в том, что ваше хэш-соединение «продукта» с «каталогом» неверно оценивается более чем в 300 раз. К ФНС это не имеет никакого отношения. Поэтому я бы сказал, что, возможно, вам просто повезло, что вы столкнулись с этой проблемой с запросом FTS, а не с каким-либо другим запросом.

PostgreSQL согласен с тем, что не стоит сначала получать 1,3 млн продуктов, но считает, что потребуется около 4000 (1319*3) продуктов.

Так почему же? Это сводится к p.FKIDCatalog=c.IDCatalog and c.IDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23). Он оценивает это, взяв, сколько строк p соответствует в среднем каждому значению FKIDCatalog, умноженному на 9. Но 9 конкретных значений, которые вы перечисляете, не являются средними, а являются чрезвычайно распространенными. Если вместо этого вы запишете это как p.FKIDCatalog=c.IDCatalog and p.FKIDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23), то он оценит строки, которые он ожидает найти для каждого из этих 9 конкретных значений, и суммирует их.

Обычно PostgreSQL правильно оценивает транзитивное свойство равенства, то есть, если вы записали его как p.FKIDCatalog=c.IDCatalog and c.IDCatalog=5, он знает, что может получить конкретную оценку для p.FKIDCatalog=5 и использовать ее. Но это не делает то же самое для транзитивного свойства IN-списка (за исключением случаев, когда IN-список состоит только из одного элемента, тогда он переписывается как простое равенство и действительно применяет переходный закон), хотя концептуально это возможно.

Я также хотел бы отметить, что оценка полнотекстового индекса, видимого в вашем другом плане, также довольно плоха, ожидая 4171 строк, но находя только 88. Я не знаю, почему это так плохо, в моих руках тв@@ tq обычно оценивается лучше, чем это (по крайней мере, когда tq состоит из одного термина). Была ли таблица недавно проанализирована? По крайней мере, с тех пор, как был добавлен экспрессионный индекс?

Одного исправления любого из них может быть достаточно, чтобы изменить план на более быстрый.

Да, черт возьми. Мне никогда не удавалось получить более 10% информации из этих планов запросов, но с этим объяснением причина вполне очевидна. К счастью, это созданный вручную запрос, и замена c.IDCatalog на p.FKIDCatalog сокращает 49 секунд до 12 мс. Ух ты. (Кажется, есть место для улучшения с IN, так как я не думаю, что такое различие должно быть оставлено на усмотрение пользователя, но, по крайней мере, есть способ обойти это. Существует 7000 каталогов, со всеми, кроме 20, содержащие не более 2-х продуктов - не идеальная ситуация, я полагаю, но так оно и есть)

SlowFox 23.04.2022 09:50

Что касается полнотекстового индекса: я analyzed непосредственно перед публикацией и использую реальные данные. Это то, что я должен исследовать (в отдельном вопросе), чтобы избежать проблем в будущем?

SlowFox 23.04.2022 09:51

Какая версия и сколько строк в productlanguage? Возможно, степень детализации текущей оценки ограничена параметром default_statistics_target. Если это так, выполнение alter index productlanguage_descriptionshort_descriptionlong_keywordlist alter column 1 SET STATISTICS 1000 (или даже выше), а затем повторение АНАЛИЗА таблицы может улучшить ситуацию.

jjanes 25.04.2022 03:17

По умолчанию (100): Bitmap Index Scan on productlanguage_descriptionshort_descriptionlong_keywordlist (cost=0.00..808.87 rows=10749 width=0) 1000: Bitmap Index Scan on productlanguage_descriptionshort_descriptionlong_keywordlist (cost=0.00..721.26 rows=668 width=0) 10000: Bitmap Index Scan on productlanguage_descriptionshort_descriptionlong_keywordlist (cost=0.00..718.24 rows=265 width=0)

SlowFox 25.04.2022 13:46

Я забыл: PostgreSQL 15.3 (Debian). Кажется, стоит немного больше узнать об этих деталях для меня.

SlowFox 25.04.2022 13:58

Другие вопросы по теме