Можно ли использовать псевдонимы и вложенные запросы?

Я работаю с хранилищем данных, создающим отчеты. Как следует из названия, у меня много данных. Один из запросов, который вытягивает много данных, занимает больше времени, чем мне нравится (они не выполняются ad-hoc, эти запросы выполняются каждую ночь и перестраивают таблицы для кэширования отчетов).

Я смотрю на оптимизацию, но я немного ограничен в том, что я могу сделать. У меня есть один запрос, который написан вдоль строк…

SELECT column1, column2,... columnN, (subQuery1), (subquery2)... and so on.

Проблема в том, что подзапросы повторяются достаточно часто, потому что каждая инструкция имеет регистр вокруг них, например…

SELECT
    column1
    , column2
    , columnN
    , (SELECT 
        CASE
            WHEN (subQuery1) > 0 AND (subquery2) > 0 
            THEN CAST((subQuery1)/(subquery2) AS decimal)*100
            ELSE 0 
        END) AS "longWastefulQueryResults"

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

Я хотел бы что-то вроде того, что вы видите ниже, но я получаю ошибки о необходимости sq1 и sq2 в моем предложении group by. Я бы предоставил точный образец, но это было бы мучительно утомительно.

SELECT
    column1
    , column2
    , columnN
    , (subQuery1) as sq1
    , (subquery2) as sq2
    , (SELECT 
        CASE
            WHEN (sq1) > 0 AND (sq2) > 0 
                THEN CAST((sq1)/(sq2) AS decimal)*100
            ELSE 0 
        END) AS "lessWastefulQueryResults"

Я использую Postgres 9.3, но еще не смог пройти успешный тест. Есть ли что-то, что я могу сделать, чтобы оптимизировать свой запрос?

3 ответа

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

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

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

    SELECT column1,
           column2,
           columnn,
           subquery1.sq1,
           subquery2.sq2,
           (SELECT CASE
               WHEN (subquery1.sq1) > 0 AND (subquery2.sq2) > 0 THEN
                  CAST ( (subquery1.sq1) / (subquery2.sq2) AS DECIMAL) * 100
               ELSE
                  0
            END)
              AS "lessWastefulQueryResults"
    FROM   some_table
           JOIN (SELECT   *
                 FROM     other_table
                 GROUP BY some_columns) subquery1
              ON some_table.some_columns = subquery1.some_columns
           JOIN (SELECT   *
                 FROM     yet_another_table
                 GROUP BY more_columns) subquery1
              ON some_table.more_columns = subquery1.more_columns
    
  2. Я не уверен, насколько хорош оптимизатор Postgres, поэтому я не уверен, что оптимизация таким образом принесет какую-то пользу. (На мой взгляд, это не должно, потому что СУБД должна заботиться о такого рода вещах; но это совсем не удивительно, если это не так.) OTOH если в текущей форме вы повторяете логику запроса, то вы можете сделать что-то другое, независимо от того, помогает ли это производительности…

    Вы можете поместить подзапросы в withпредложения спереди, и это может помочь.

      with subauery1 as (select ...)
         , subquery2 as (select ...)
    select ...
    

    Это похоже на размещение вложенных запросов в FROMпредложении, как предлагает Аллен, но может предложить большую гибкость, если ваши запросы сложны.

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

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