Несколько небольших запросов быстрее, чем один большой запрос
«Большой» запрос ниже выполняется более 24 часов:
SELECT *
FROM VIEW
WHERE COL_A IN (a, b, c, ...)
AND COL_B IN (d, e, f, ...)
Напротив, разделение этого «большого» запроса на несколько «маленьких» запросов и их одновременное выполнение завершается через 30 минут:
SELECT *
FROM VIEW
WHERE COL_A IN (a) AND COL_B IN (d)
SELECT *
FROM VIEW
WHERE COL_A IN (a) AND COL_B IN (e)
...
SELECT *
FROM VIEW
WHERE COL_A IN (b) AND COL_B IN (d)
SELECT *
FROM VIEW
WHERE COL_A IN (b) AND COL_B IN (e)
...
Если это уместно, каждый оператор IN в «большом» запросе содержит около 30 элементов. Следовательно, 900 уникальных «маленьких» запросов = 1 «большой» запрос.
Обратите внимание, что VIEW определяется как запрос от VIEW_1, который сам является запросом от VIEW_2, который сам является запросом от VIEW_3. Каждый VIEW состоит из миллионов строк.
Я бы перепроектировал представления и написал свой собственный единственный запрос именно для того, что я хочу. Однако у меня нет доступа SELECT к базовой TABLE.
Кажется нелогичным, что программная запись и параллельное выполнение 900 различных запросов (с использованием Python) выполняется быстрее, чем один большой запрос, когда возвращаемая таблица одинакова. Почему это?
@JacobH Спасибо за подробный ответ! Учитывая, что я не могу реконструировать представления и написать свой собственный запрос (нет доступа SELECT к базовой таблице), что мне здесь лучше всего, кроме как заставить команду БД создать новое представление? Просто продолжать использовать Python для программного написания сотен запросов, выполнять их одновременно, а затем объединять результаты?
Комментарий был слишком длинным, но вот хорошая ссылка для начала на вложенные представления и здесь и этот пост о производительности IN
Если вам действительно нужна помощь в этом, вам нужно опубликовать вывод EXPLAIN ANALYZE в своем запросе. Может быть что-то очень очевидное, например, отсутствие work_mem в вашем клиентском соединении. Использование 30 UNION ALL не является хорошим решением.
@Рамфьорд Спасибо. Я прикрепил фрагмент плана объяснения. Есть ли очевидные исправления?
В опубликованном вами плане объяснения нет компонента ANALYZE, который мне нужен. Если вы можете запустить EXPLAIN ANALYZE в консоли psql, вставьте результаты в файл объяснения.depesz.com. Таким образом, вы можете фактически сказать, сколько времени было потрачено на любой данный компонент. Одна вещь, которую я подозреваю, поможет вашему запросу (пока нет доказательств), это увеличение work_mem в соединении. Вывод EXPLAIN ANALYZE точно скажет вам, сколько вам нужно (как вы можете видеть, в вашем изображении нет информации об использовании памяти).
Ответы 1
Во-первых, вы можете запустить их как один запрос:
SELECT * FROM VIEW WHERE COL_A IN (a) AND COL_B IN (d)
UNION ALL
SELECT * FROM VIEW WHERE COL_A IN (a) AND COL_B IN (e)
UNION ALL
. . .
Похоже, что базовые таблицы имеют индексы в представлениях. Вероятно, вы также можете упростить запросы:
SELECT * FROM VIEW WHERE COL_A = a AND COL_B IN (d, e, . . .)
или:
SELECT * FROM VIEW WHERE COL_A IN (a, b, . . .) AND COL_B = d
Спасибо! Я проверю завтра и сообщу время для этого метода - выполнение одного запроса с 30 операторами UNION ALL.
@Разъем . . . Сначала протестируйте одну из них, чтобы увидеть, какая версия работает быстрее.
Много причин. Подобное вложение представлений является известным узким местом в производительности. У оптимизатора есть ограниченное количество миллисекунд для создания плана. По истечении времени ожидания он использует «лучший». Что, возможно, вовсе не является хорошим планом. Если ваш запрос содержит миллионы строк, это не лучший план. Наличие огромного количества операторов
INрасширяется оптимизатором доOR, что снова способствует плохому плану, когда у вас их 900. Здесь множество мелких «упсов», наложенных друг на друга, чтобы создать гигантскую черную дыру производительности.