Оптимизация нескольких соединений

Я удалил мой PostgreSQL Server пару дней назад, так что вы, вероятно, придется играть с этим, но, надеюсь, это хорошо для вас.

Ключи:

1. Вам не нужно нужны подзапросы - просто делайте прямые соединения и совокупность
2. , вы должны иметь возможность использовать внутренние соединения, которые обычно являются более исполнительными, чем внешние соединения

, если ничего Иначе я думаю, что запрос ниже немного яснее.

Я использовал таблицу календаря в моем запросе, но вы можете заменить это с помощью Generate_Series, как вы его использовали.

Также, в зависимости от индексации, может быть лучше сравнивать body_date с> = и <вместо того, чтобы вытащить часть даты и сравнения. Я не знаю достаточно о PostgreSQL, чтобы узнать, как он работает за кулисами, поэтому я бы попробую оба подхода к тому, что сервер может оптимизировать лучше. В псевдо-коде вы будете делать: body_date> = дата (время = полночь) и body_date <дата + 1 (время = полночь).

SELECT
    CAL.calendar_date AS period,
    SUM(O.body_size) AS outbound,
    SUM(I.body_size) AS inbound
FROM
    Calendar CAL
INNER JOIN Body OB ON
    OB.body_time::date = CAL.calendar_date
INNER JOIN Envelope OE ON
    OE.message_id = OB.message_id AND
    OE.envelope_command = 1
INNER JOIN Body IB ON
    IB.body_time::date = CAL.calendar_date
INNER JOIN Envelope IE ON
    IE.message_id = IB.message_id AND
    IE.envelope_command = 2
GROUP BY
    CAL.calendar_date

Всегда есть 2 вещи, которые следует учитывать при оптимизации запросов:

• Какие индексы могут быть использованы (вам может потребоваться создать индексы)
• Как запрос написан (вам может понадобиться Измените запрос, чтобы позволить оптимирующему запросу можно найти подходящие индексы, и чтобы не повторно прочитать данные погранично)

несколько наблюдений:

• Вы выполняете манипуляции дата, прежде чем присоединиться к своим датам. Как правило, это предотвратит оптимистирующее запрос на использование индекса, даже если он существует. Вы должны попытаться написать свои выражения таким образом, чтобы индексированные столбцы существуют неизменными на одной стороне выражения.

• Ваши подзапросы фильтруют до того же диапазона дата, что и Generate_Series . Это дублирование, и он ограничивает способность оптимизации выбрать наиболее эффективную оптимизацию. Я подозреваю, что, возможно, было написано для повышения производительности, потому что Оптимер не смог использовать индекс в столбце даты ( body_time )?

• Примечание : На самом деле мы бы очень хотели использовать Индекс в Body.body_time

• порядок по в подзаюте, находится в лучшем состоянии. В худшем случае он может заставить оптимизатор запроса сортировать набор результатов перед присоединением; И это не обязательно хорошо для плана запроса. Скорее только применяйте заказ прямо в конце для окончательного дисплея.

• Использование левого присоединения В ваших подзапрониках неуместно. Предполагая, что вы используете конвенции ANSI для NULL поведения (и вы должны быть), любой внешний включения в конверт вернется Envelope_command = NULL , и эти, следовательно, будут исключены в условиях envelope_command =? .

• подзапросы o и I почти одинаковы сохраняются для значения envelope_command . Это заставляет Optimser отсканировать одинаковые базовые таблицы дважды. Вы можете использовать методику таблицу Pivot для присоединения к данным один раз и разделить значения на 2 столбца.

Попробуйте следующее, в котором используется методика поворота:

SELECT  p.period,
        /*The pivot technique in action...*/
        SUM(
        CASE WHEN envelope_command = 1 THEN body_size
        ELSE 0
        END) AS Outbound,
        SUM(
        CASE WHEN envelope_command = 2 THEN body_size
        ELSE 0
        END) AS Inbound
FROM    (
        SELECT  date '2009-10-01' + s.day AS period
        FROM    generate_series(0, date '2009-10-31' - date '2009-10-01') AS s(day)
        ) AS p 
        /*The left JOIN is justified to ensure ALL generated dates are returned
          Also: it joins to a subquery, else the JOIN to envelope _could_ exclude some generated dates*/
        LEFT OUTER JOIN (
        SELECT  b.body_size,
                b.body_time,
                e.envelope_command
        FROM    body AS b 
                INNER JOIN envelope e 
                  ON e.message_id = b.message_id 
        WHERE   envelope_command IN (1, 2)
        ) d
          /*The expressions below allow the optimser to use an index on body_time if 
            the statistics indicate it would be beneficial*/
          ON d.body_time >= p.period
         AND d.body_time < p.period + INTERVAL '1 DAY'
GROUP BY p.Period
ORDER BY p.Period

Редактировать : добавленный фильтр, предложенные TOM H.