Первое, на что следует обратить внимание перед оптимизацией запроса, - это Планировщик запросов.

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

Анатомия плана запроса PostgreSQL

Прежде чем мы попытаемся прочитать план запроса, важно задать несколько очень простых вопросов:

  • Зачем нам вообще нужен план запроса?
  • Что именно изображено в плане?
  • PostgreSQL недостаточно умен, чтобы автоматически оптимизировать мои запросы? Зачем мне беспокоиться о планировщике?
  • Планировщик - единственное, на что мне нужно смотреть?

Каждый запрос проходит разные этапы, и важно понимать, что каждый этап означает для базы данных.

Первый этап - это подключение к базе данных либо через JDBC / ODBC (API-интерфейсы, созданные Microsoft и Oracle, соответственно, для взаимодействия с базами данных), либо с помощью других средств, таких как PSQL (интерфейс терминала для Postgres).

Второй этап - это перевод запроса в промежуточный формат, известный как дерево синтаксического анализа. Обсуждение внутреннего устройства дерева синтаксического анализа выходит за рамки этой статьи, но вы можете представить, что это похоже на скомпилированную форму SQL-запроса.

Третий этап - это то, что мы называем системой перезаписи / системой правил. Он берет дерево синтаксического анализа, сгенерированное на втором этапе, и переписывает его таким образом, чтобы планировщик / оптимизатор мог начать работать с ним.

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

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

Настройка данных

Давайте создадим фиктивную таблицу с поддельными данными для проведения наших экспериментов.

create table fake_data(id serial, name text, sentence text, company text);

А затем заполните эту таблицу данными. Я использовал приведенный ниже сценарий Python для создания случайных строк.

from faker import Faker

fake = Faker()
# Change this range to whatever value you like
MAX_RANGE = 1000

with open('data.csv', 'w') as f:
    for i in range(0, MAX_RANGE):
        name = fake.name().replace(",", "")
        
        sentence = fake.sentence(
            nb_words=16, variable_nb_words=True
        ).replace(",", "")
        
        company = fake.company().replace(",", "")

        content = "'" + name + "'" + "," + \
                  "'" + sentence + "'" + "," \
                  + "'" + company + "'" + "\n"

        f.write(content)

Скрипт использует библиотеку Faker для генерации поддельных данных. Он сгенерирует csv файл на корневом уровне и может быть импортирован как обычный csv в PostgreSQL с помощью следующей команды.

COPY fake_data(name, sentence, company) 
FROM '/path/to/csv' DELIMITER ','

Поскольку id является серийным, он будет автоматически заполнен самим PostgreSQL. Теперь таблица содержит 1119284 записей.

SELECT COUNT(*) FROM fake_data;

Большинство приведенных ниже примеров основано на приведенной выше таблице. Он намеренно упрощен, чтобы сосредоточиться на процессе, а не на сложности таблицы / данных.

В приведенных ниже примерах используется редактор Arctype. Показанное изображение поста взято из Онлайн-инструмента объяснения Depesz.

Приступаем к этапу планирования

PostgreSQL и многие другие системы баз данных позволяют пользователям увидеть, что на самом деле происходит на этапе планирования. Мы можем сделать это, выполнив так называемую команду EXPLAIN.

PostgreSQL ОБЪЯСНЕНИЕ запроса

EXPLAIN SELECT * FROM fake_data LIMIT 10;

Используя EXPLAIN, вы можете просматривать планы запросов до того, как они будут фактически выполнены базой данных. Мы рассмотрим часть понимания каждого из них в разделе ниже, но сначала давайте взглянем на другую расширенную версию EXPLAIN, называемую EXPLAIN ANALYSE.

Объясняйте анализируйте вместе

EXPLAIN ANALYSE SELECT * FROM fake_data LIMIT 10;

В отличие от EXPLAIN, EXPLAIN ANALYSE фактически выполняет запрос в базе данных. Этот параметр невероятно полезен, чтобы понять, правильно ли выполняет планировщик свою роль, т.е. есть ли огромная разница в плане, созданном с помощью EXPLAIN и EXPLAIN ANALYSE.

PostgreSQL удобен как с ANAYLYZE, так и с ANALYSE

Что такое буферы и кеши в базе данных?

Перейдем к более интересной метрике под названием BUFFERS. Это объясняет, какой объем данных поступил из кеша PostgreSQL и сколько нужно было извлечь с диска.

EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 200

Buffers : shared hit=5 означает, что пять страниц были извлечены из самого кеша PostgreSQL. Давайте настроим запрос для смещения из разных строк.

EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 500

Buffers: shared hit=7 read=5 показывает, что с диска пришло пять страниц. Часть read - это переменная, которая показывает, сколько страниц было получено с диска, а hit, как уже объяснялось, было получено из кеша. Если мы выполним тот же запрос еще раз (помните, что ANALYSE запускает запрос), тогда все данные теперь поступают из кеша.

PostgreSQL использует механизм, называемый кешем наименее недавно использованных (LRU), для хранения часто используемых данных в памяти. Понимание того, как работает кеш и его важность, - тема другого поста, но сейчас мы должны понять, что PostgreSQL имеет надежный механизм кеширования, и мы можем увидеть, как он работает, используя команду EXPLAIN (ANALYSE, BUFFERS).

Аргумент команды VERBOSE

EXPLAIN (ANALYSE,BUFFERS,VERBOSE) SELECT * FROM fake_data LIMIT 10 OFFSET 500

Verbose - еще один аргумент команды, который дает дополнительную информацию.

Обратите внимание, что Output: id, name, sentence, company является дополнительным. В сложном плане запроса будет напечатано множество другой информации. По умолчанию для параметров COSTS и TIMING установлено значение TRUE, и нет необходимости указывать их явно, если вы не хотите установить их как FALSE.

FORMAT в Postgres Explain

PostgreSQL может предоставить план запроса в удобном формате, таком как JSON, чтобы эти планы можно было интерпретировать независящим от языка способом.

EXPLAIN (ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) SELECT * FROM fake_data LIMIT 10 OFFSET 500

Распечатает план запроса в формате JSON. Вы можете просмотреть этот формат в Arctype, скопировав его вывод и вставив в другую таблицу, как показано на GIF-изображении ниже.

Существуют различные другие форматы, например следующие:

  • Текст (по умолчанию)
  • JSON (пример выше)
  • XML
  • YAML

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

Обобщить:

  • EXPLAIN - это тип плана, с которого вы обычно начинаете, и он часто используется в производственных системах.
  • EXPLAIN ANALYSE используется для выполнения запроса вместе с получением плана запроса. Таким образом вы получаете разбивку времени планирования и времени выполнения в плане и сравнение со стоимостью и фактическим временем выполненного запроса.
  • EXPLAIN (ANALYSE, BUFFERS) используется поверх анализа, чтобы узнать, сколько строк / страниц было получено из кеша и диска и как себя ведет кеш.
  • EXPLAIN (ANALYSE, BUFFERS, VERBOSE), чтобы получить подробную и дополнительную информацию по запросам.
  • EXPLAIN(ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) - это то, как вы экспортируете в конкретный формат. В данном случае формат JSON.

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

Элементы плана запроса

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

Узлы запроса

План запроса состоит из узлов:

EXPLAIN SELECT * FROM fake_data LIMIT 10 OFFSET 500;

Узел можно рассматривать как этап выполнения базы данных. Узлы в основном вложены, как показано выше. Seq Scan выполняется до и поверх него, а затем применяется предложение Limit. Давайте добавим предложение Where, чтобы понять дальнейшее вложение.

EXPLAIN SELECT * FROM fake_data where NAME = 'Sandra Smith' LIMIT 10

Казнь происходит изнутри.

  • Фильтровать строки, где name = Sandra Smith
  • Выполните последовательное сканирование с указанным выше фильтром
  • Применить ограничение вверх

Как видите, база данных распознает, что необходимы только десять строк, и не выполняет сканирование после того, как требуемые десять строк будут достигнуты. Обратите внимание, я выключил SET max_parallel_workers_per_gather =0;, чтобы упростить план. Мы рассмотрим распараллеливание в следующей статье.

Стоимость в Планировщике запросов

Затраты являются важной частью плана запроса к базе данных, и их можно легко неправильно понять из-за того, как они представлены. Давайте еще раз посмотрим на простой план с затратами.

Важны следующие вещи:

  • Начальная стоимость предложения LIMIT не равна нулю. Это потому, что начальные затраты суммируются до максимума; вы видите стоимость узлов под ним.
  • Общая стоимость - это произвольная мера, и она более важна для планировщика, чем для пользователя. Вы никогда не получите данные всей таблицы одновременно в любом практическом случае.
  • Последовательное сканирование, как известно, плохо дает оценки, потому что база данных не знает, как их оптимизировать. Индексы могут значительно ускорить запросы с WHERE предложениями.
  • Width важен, потому что чем шире строка, тем больше данных необходимо извлечь с диска. Вот почему очень важно соблюдать нормализацию таблиц базы данных.

Если мы действительно выполним запрос, тогда затраты будут иметь больше смысла.

Планирование и выполнение базы данных

Время планирования и выполнения - это метрики, которые можно получить только с опцией EXPLAIN ANALYSE.

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

В типичной системе OLTP, такой как PostgreSQL, любое объединение планирования и выполнения должно составлять менее 50 мс, если только это не аналитический запрос / огромные записи / известные исключения. Помните, что OLTP расшифровывается как Online Transaction Processing. В типичном бизнесе транзакции обычно составляют от тысяч до миллионов. За этим временем выполнения всегда следует очень внимательно следить, поскольку эти более мелкие и более дорогостоящие запросы могут суммировать и добавлять огромные накладные расходы.

Куда пойти отсюда

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

Вернемся к вопросам, которые мы задавали выше.

В: Зачем нам вообще нужен план запроса?

A: «Дурак с планом лучше, чем гений без плана!» - старая пословица Arctype. План абсолютно необходим, чтобы решить, какой путь выбрать, особенно когда решение принимается на основе статистики.

В: Что именно представлено в плане?

О: План состоит из узлов, затрат, времени планирования и выполнения. Узлы - это фундаментальный строительный блок запроса. Стоимость - это основной атрибут узла. Время планирования и выполнения, чтобы увидеть фактическое время.

В: PostgreSQL недостаточно умен, чтобы автоматически оптимизировать мои запросы? Зачем мне беспокоиться о планировщике?

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

В: Планировщик - единственное, на что мне нужно обратить внимание?

A: Определенно нет. Есть много других вещей - знание предметной области приложения, дизайн таблиц, архитектура базы данных и т. Д. - которые очень важны. Но как разработчику / администратору баз данных понимание и улучшение этих абстрактных навыков чрезвычайно важно для нашей карьеры.

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

Ресурсы

  1. Клиент Arctype SQL
  2. Depesz Explain Query Site
  3. Библиотека Faker Python