Как оптимизировать этот выбор

#sql #postgresql #optimization

#sql #postgresql #оптимизация

Вопрос:

 SELECT *
FROM   trips,
       dates
WHERE  places_number_on_flag > 0
       AND places_number > 0
       AND ( places_number - ( (SELECT Count(id)
                                FROM   resrv_customers
                                WHERE  trip = trips.id
                                       AND ow > 0)
                                 (SELECT Count(id)
                                  FROM   resrv_customers
                                  WHERE  trip = trips.id
                                         AND extra_seat = 1
                                         AND ow > 0) ) < 20 )
       AND dates.id = trips.trip_date
       AND dates.from_date > 2458553;

Он возвращает 5 строк примерно через минуту и 50 секунд.

Я хочу, чтобы это было быстрее.

1. Вы ознакомились с планом выполнения?

2. Пожалуйста, опубликуйте соответствующие инструкции CREATE TABLE и CREATE INDEX , а также план. Вам также следует переработать запрос, используя явный синтаксис join для лучшей читаемости.

3. @dodekja . . . Вы должны объяснить, какую логику реализует запрос.

4. Вредные привычки, от которых следует отказаться: использование объединений в старом стиле — этот стиль списка таблиц, разделенных запятыми , в старом стиле был заменен на правильный синтаксис ANSI JOIN в стандарте ANSI- 92 SQL ( 25 лет назад), и его использование не рекомендуется

Ответ №1:

Одной из оптимизаций было бы запустить подзапрос только один раз и использовать условную агрегацию для подсчета мест:

 SELECT *
FROM   trips
  JOIN dates on dates.id = trips.trip_date
WHERE  places_number_on_flag > 0
       AND places_number > 0
       AND ( places_number - ( (SELECT Count(id)   count(id) filter (where extra_seat = 1)
                                FROM   resrv_customers
                                WHERE  trip = trips.id
                                       AND ow > 0)) < 20 )
       AND dates.from_date > 2458553;

Обратите внимание, что я заменил ваши древние и хрупкие неявные объединения в предложении WHERE на явный JOIN оператор. Это не оказывает никакого влияния на производительность, это просто лучший стиль кодирования.

Ответ №2:

Мы можем попробовать переписать ваш запрос, используя соединение с resrv_customers таблицей, вместо использования дорогостоящих коррелированных подзапросов:

 WITH cte AS (
    SELECT
        trip,
        COUNT(CASE WHEN ow > 0 THEN 1 END) AS cnt1,
        COUNT(CASE WHEN extra_seat = 1 AND ow > 0 THEN 1 END) AS cnt2
    FROM resrv_customers
    GROUP BY trip
)

SELECT *
FROM trips t
INNER JOIN dates d
    ON t.trip_date = d.id
LEFT JOIN cte r
    ON r.trip = t.id
WHERE
    places_number > 0 AND
    places_number - (r.cnt1   r.cnt2) < 20 AND
    d.from_date > 2458553;

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

1. Просто комментарий к оптимизации, что CTE, скорее всего, будет узким местом процесса, поскольку ему нужно будет сгруппировать и подсчитать всю resrv_customers таблицу, когда вам нужно только сопоставить trip.id . Также ow > 0 может быть в предложении where, поскольку этого требуют оба параметра, плюс использование filter предложения ( postgresql.org/docs/current / … ) обычно выполняется быстрее, чем case внутри count.

Ответ №3:

Попробуйте это:

 with rc_count as
(
    select trip, count(*) as ow_count, sum( (extra_seat = 1)::int ) as ow_count_with_extra_seat
    from resrv_customers
    where ow > 0
    group by trip    
)

SELECT *
FROM   trips
join   dates on dates.id = trips.trip_date
join   rc_count rc on trips.id = trip 
WHERE  places_number_on_flag > 0
       AND places_number > 0
       AND dates.from_date > 2458553
       and places_number - (ow_count   ow_count_with_extra_seat) < 20

1. Примечание: Этот ответ почти идентичен тому, что я опубликовал ранее.

2. @TimBiegeleisen этого не заметил. Я сосредоточился на написании собственного ответа, а также перенес общее условие ow > 0 в предложение where