Запрос PostgreSQL занимает слишком много времени

Наконец удачная попытка

Моя другая идея - согласно комментарию:
Что произойдет, если вы удалите предложение LIMIT для случая, когда роль не найдена? У меня есть подозрение, что это приведет к быстрому плану - сделать LIMIT виновником.

Возможно, вы сможете решить свою проблему, поместив свой запрос в подзапрос и применив LIMIT только к внешнему запросу (не проверено):

SELECT *
FROM  (
   SELECT *
   FROM   "Roles"         AS r  
   JOIN   "Users"         AS u  ON u."RoleId" = r."Id"
   JOIN   "PaymentOrders" AS po ON po."UserId" = u."Id"
   JOIN   "Payments"      AS p  ON p."PaymentOrderId" = po."Id"
   WHERE  r."Name" = 'Moses'
  ) x
LIMIT  1000;

Согласно комментарию: @Davita проверил и исключил этот обходной путь. ответ @Kevin позже разъяснил, почему обходной путь не удался: используйте CTE вместо подзапроса.< br> Или проверьте наличие роли, прежде чем использовать большой запрос, чтобы исключить плохой случай.

Это оставляет вопросы к PostgreSQL относительно оптимизации запросов с LIMIT.

Появилось несколько недавних отчетов об ошибках, касающихся планов запросов с LIMIT. Я цитирую Саймона Риггса, комментирующего один из этих отчетов здесь:

Часто встречаются очень плохие планы с LIMIT. Это плохо для нас, потому что добавление LIMIT обычно/должно делать запросы быстрее, а не медленнее.

Нам нужно что-то сделать.

Первая попытка без успеха

Я пропустил, что @Craig уже упоминал join_collapse_limit в комментариях. Так что это было ограниченное использование:

Повлияет ли изменение порядка предложений JOIN?

SELECT *
FROM   "Roles"         AS r  
JOIN   "Users"         AS u  ON u."RoleId" = r."Id"
JOIN   "PaymentOrders" AS po ON po."UserId" = u."Id"
JOIN   "Payments"      AS p  ON p."PaymentOrderId" = po."Id"
WHERE  r."Name" = 'Moses'
LIMIT  1000

Связано: вы случайно не испортили настройку join_collapse_limit или geqo_threshold? Очень низкое значение может помешать планировщику изменить порядок ваших предложений JOIN, что может объяснить вашу проблему.

Если это не решит проблему, я бы попытался создать индекс для "Roles"(Name). Не то, чтобы это имело какой-либо смысл только с 15 строками, но я попытался бы устранить подозрение, что неверная статистика или параметры стоимости (или даже ошибка) заставляют планировщика полагать, что последовательное сканирование «Ролей» будет дороже, чем оно есть на самом деле.

Как было предложено пару раз в потоке в списке производительности сообщества PostgreSQL, вы можете обойти эту проблему, установив барьер оптимизации с помощью CTE, например:

WITH x AS
(
SELECT *
  FROM "Payments" AS p
  JOIN "PaymentOrders" AS po ON po."Id" = p."PaymentOrderId"
  JOIN "Users" as u ON u."Id" = po."UserId"
  JOIN "Roles" as r ON u."RoleId" = r."Id"
  WHERE r."Name" = 'Moses'
)
SELECT * FROM x
  LIMIT 1000;

Вы также можете получить хороший план для своего исходного запроса, если установите более высокую цель статистики для «Роли». «Имя», а затем АНАЛИЗ. Например:

ALTER TABLE "Roles"
  ALTER COLUMN "Name" SET STATISTICS 1000;
ANALYZE "Roles";

Если он ожидает, что в таблице будет меньше совпадающих строк, как это, вероятно, произойдет с более мелкозернистой статистикой, он предположит, что ему нужно прочитать больший процент таблицы, чтобы найти их при последовательном сканировании. Это может привести к тому, что он предпочтет использовать индекс вместо последовательного сканирования таблицы.

Вы также можете получить лучший план для исходного запроса, изменив некоторые константы стоимости планировщика и предположения о кэшировании. Что можно попробовать за один сеанс с помощью команды SET:

• Уменьшить random_page_cost. Это во многом зависит от того, насколько сильно кэшированы ваши данные. Учитывая таблицу с сотнями миллионов строк, вы, вероятно, не захотите опускаться ниже 2; хотя, если активный набор данных в вашей базе данных сильно закэширован, вы можете уменьшить его до значения seq_page_cost, и вы можете уменьшить их оба на порядок.

• Убедитесь, что для параметра Effective_cache_size установлено значение суммы shared_buffers и того, что кэширует ваша ОС. Это не выделяет никакой памяти; он просто сообщает оптимизатору, насколько велика вероятность того, что индексные страницы останутся в кеше при активном доступе. При более высоком значении индексы выглядят лучше по сравнению с последовательным сканированием.

• Увеличьте cpu_tuple_cost где-то в диапазоне от 0,03 до 0,05. Я обнаружил, что значение по умолчанию 0,01 слишком низкое. Я часто получаю лучшие планы, увеличивая его, и никогда не видел, чтобы значение в предложенном мной диапазоне вызывало выбор худших планов.

• Убедитесь, что ваша настройка work_mem разумна. В большинстве сред, в которых я запускал PostgreSQL, это было в диапазоне от 16 МБ до 64 МБ. Это позволит лучше использовать хеш-таблицы, сканирование растровых индексов, сортировку и т. д. и может полностью изменить ваши планы; почти всегда в лучшую сторону. Остерегайтесь устанавливать этот уровень на уровне, который дает хорошие планы, если у вас большое количество соединений - вы должны учитывать тот факт, что каждое соединение может выделять столько памяти для каждого узла выполняемого запроса. «Практическое правило» состоит в том, чтобы рассчитать, что вы достигнете пика примерно в это время настройки max_connections. Это одна из причин, по которой разумно ограничить фактическое количество подключений к базе данных с помощью пула подключений.

Если вы найдете для них подходящую комбинацию настроек, вы можете внести эти изменения в свой файл postgresql.conf. Если вы это сделаете, внимательно следите за снижением производительности и будьте готовы настроить параметры для наилучшей производительности вашей общей нагрузки.

Я согласен с тем, что нам нужно что-то сделать, чтобы оттолкнуть оптимизатора от «рискованных» планов, даже если они выглядят так, как будто в среднем они будут выполняться быстрее; но я буду немного удивлен, если настройка вашей конфигурации таким образом, чтобы оптимизатор лучше моделировал фактические затраты каждой альтернативы, не заставит его использовать эффективный план.