Блог

У меня проблемы с производительностью этого запроса. Если я удалю порядок по разделам, все будет работать хорошо. Но я действительно этого хочу. Я пытался использовать много индексов, но не получил никаких результатов. Можете ли вы мне помочь, пожалуйста?

 SELECT *
     FROM "refuel_request" AS "refuel_request"
     LEFT OUTER JOIN "user" AS "user" ON "refuel_request"."user_id" = "user"."user_id"
     LEFT OUTER JOIN "bill_qr" AS "bill_qr" ON "refuel_request"."bill_qr_id" = "bill_qr"."bill_qr_id"
     LEFT OUTER JOIN "car" AS "order.car" ON "refuel_request"."car_id" = "order.car"."car_id" 
     LEFT OUTER JOIN "refuel_request_status" AS "refuel_request_status" ON "refuel_request"."refuel_request_status_id" = "refuel_request_status"."refuel_request_status_id"
     WHERE
      refuel_request."refuel_request_status_id" IN ( '1', '2', '3') 
     ORDER BY "refuel_request".created_at desc
     LIMIT 10 
 

Существует объяснение этого запроса

ОБЪЯСНИТЕ (АНАЛИЗ, БУФЕРЫ)

Первичные ключи и / или внешние ключи

1. pk_refuel_request_id
2. refuel_request_bill_qr_id_fkey
3. refuel_request_user_id_fkey

Все внешние таблицы объединения связаны с 1:n refuel_request . Это означает, что ваш запрос ищет последние десять созданных запросов на заправку со статусом от 1 до 3.

Вы выполняете внешнее соединение таблиц, потому что не каждая reful_request связана с a user , a bill_qr , a car и a status . Или вы ошибочно подключаетесь к внешнему соединению. В любом случае, ни одно из объединений не изменяет количество извлекаемых строк; это все еще одна строка на запрос на дозаправку. Для объединения строк других таблиц СУБД просто нужны их индексы первичного ключа. Беспокоиться не о чем.

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

Используйте частичный индекс, который содержит только данные для рассматриваемых статусов. Столбец, который вы индексируете, является created_at столбцом, чтобы сразу получить 10 лучших.

 CREATE INDEX idx ON refuel_request (created_at DESC)
                 WHERE refuel_request_status_id IN (1, 2, 3);
 

Здесь объясняются частичные индексы: https://www.postgresql.org/docs/current/indexes-partial.html

У вас не может быть индекса, который поддерживает как WHERE условие, так и ORDER BY , потому что вы используете IN , а не = .

Самый быстрый вариант — разделить запрос на три части, чтобы каждая часть сравнивалась refuel_request.refuel_request_status_id с = . Объедините эти три запроса с UNION ALL помощью . В каждом из запросов есть ORDER BY и LIMIT 10 , и вы оборачиваете все это во внешний запрос, в котором есть другой ORDER BY и LIMIT 10 .

Тогда вам нужны эти индексы:

 CREATE INDEX ON refuel_request (refuel_request_status_id, created_at);
CREATE INDEX ON "user" (user_id);
CREATE INDEX ON bill_qr (bill_qr_id);
CREATE INDEX ON car (car_id);
CREATE INDEX ON refuel_request_status (refuel_request_status_id);
 

Вам нужны хотя бы индексы для соединений (вам действительно нужны левые соединения?)

  LEFT OUTER JOIN "user" AS "user" ON "refuel_request"."user_id" = "user"."user_id"
 

Итак, refuel_request.user_id должен быть в индексе

  LEFT OUTER JOIN "bill_qr" AS "bill_qr" ON "refuel_request"."bill_qr_id" = 
 LEFT OUTER JOIN "car" AS "order.car" ON "refuel_request"."car_id" = 
 

bill_qr_id и car_id тоже

  LEFT OUTER JOIN "refuel_request_status" AS "refuel_request_status" ON "refuel_request"."refuel_request_status_id" =
 

и refuel_request_status_id

  WHERE
  refuel_request."refuel_request_status_id" IN ( '1', '2', '3') 
 

refuel_request_status_id должен быть первым ключом в индексе, так как он нам нужен в WHERE

  ORDER BY "refuel_request".created_at desc
 

а затем created_at, поскольку он находится в ORDER предложении. Это не улучшит производительность как таковую, но позволит выполнить ORDER BY, не требуя доступа к данным таблицы, по той же причине, по которой мы помещаем туда другие столбцы, отличные от WHERE. Конечно, частичный индекс еще лучше, мы сдвигаем WHERE в предложении partiality и используем created_at для остальных (ОГРАНИЧЕНИЕ 10 теперь означает, что мы можем обойтись без дополнительных столбцов в индексе, поскольку извлечение трех строк 1: N стоит очень мало; в другой ситуации мы могли бы найти это полезнымчтобы сохранить эти дополнительные столбцы).

Итак, один индекс, который содержит, в таком порядке:

  refuel_request_status_id, created_at, bill_qr_id, car_id too, user_id
 ^ WHERE                   ^ ORDER     ^ used by the JOINS
 

Однако вам действительно нужен SELECT * ? Я считаю, что вы получили бы лучшую производительность, если бы включили только те поля, которые вы действительно собираетесь использовать.

Наиболее эффективным индексом для этого запроса будет refuel_request (refuel_request_status_id, created_at DESC), чтобы с помощью индекса можно было выполнять как основную фильтрацию, так и упорядочение. Вам также нужны индексы для столбцов, к которым вы присоединяетесь, но на данный момент эти таблицы маленькие и несущественные. В любом случае, индекс, который я предлагаю, на самом деле не сильно поможет с проблемами производительности, с которыми вы сталкиваетесь прямо сейчас. Вот несколько предложений:

1. Не используйте SELECT * , если вам действительно не нужны все столбцы из всех таблиц, к которым вы присоединяетесь. Указание только необходимых столбцов означает, что postgres может загружать меньше данных в память и работать с ними быстрее.
2. Postgres тратит много времени на объединения, каждый раз объединяя около миллиона строк, когда вас действительно интересуют только десять из этих строк. Мы можем побудить его сначала выполнить порядок / ограничение, несколько изменив запрос:

 WITH refuel_request_subset AS MATERIALIZED (
    SELECT *
    FROM refuel_request
    WHERE refuel_request_status_id IN ('1', '2', '3')
    ORDER BY created_at DESC
    LIMIT 10
)
SELECT *
FROM refuel_request_subset AS refuel_request
LEFT OUTER JOIN user ON refuel_request.user_id = user.user_id
LEFT OUTER JOIN bill_qr ON refuel_request.bill_qr_id = bill_qr.bill_qr_id
LEFT OUTER JOIN car AS "order.car" ON refuel_request.car_id = "order.car".car_id
LEFT OUTER JOIN refuel_request_status ON refuel_request.refuel_request_status_id = refuel_request_status.refuel_request_status_id;
 

Примечание: Предполагается, что LEFT JOINS он не будет добавлять строки в результирующий набор, как в случае с вашим текущим набором данных.

3. Этот трюк действительно работает, только если у вас есть фиксированное количество идентификаторов, но вы можете выполнить refuel_request_subset запрос отдельно для каждого идентификатора, а затем UNION результаты, в отличие от использования IN оператора. Это позволило бы postgres полностью использовать индекс, упомянутый выше.