Современная разработка программного обеспечения немыслима без использования систем управления базами данных (СУБД), которые выступают в роли надежного и производительного хранилища информации. Одним из ключевых факторов, определяющим эффективность работы всего приложения, является скорость выполнения сложных запросов к данным. При этом идентичные по своей бизнес-логике запросы могут демонстрировать кардинально разное время выполнения в различных СУБД. Это различие обусловлено работой одного из наиболее сложных и интеллектуальных компонентов любой СУБД — планировщика (оптимизатора) запросов, чья задача заключается в поиске оптимального плана выполнения на основе анализа статистики и стоимости операций.
В рамках данной работы объектом исследования выступают планировщики запросов современных реляционных СУБД. Предметом исследования являются алгоритмы и стратегии планирования, а так же их эффективность при выполнении запросов с точки зрения скорости выполнения и потребления ресурсов.
Целью настоящей работы является проведение сравнительного анализа эффективности планировщиков различных СУБД и выявление их сильных и слабых сторон при выполнении специфичной нагрузки.
Основной гипотезой исследования является предположение о том, что различные СУБД демонстрируют существенно разную эффективность на одних и тех же запросах из-за фундаментальных различий в заложенных в них алгоритмах планирования, и что это различие может быть систематизировано и объяснено.
Планировщик запросов как ключевой компонент СУБД
Эффективность выполнения операций в реляционных системах управления базами данных в значительной степени определяется работой специализированного подсистемного компонента – планировщика запросов. Как отмечается в документации PostgreSQL, задача планировщика — «принять дерево запроса и создать план запроса, который может быть передан исполнителю для обработки» . Данный компонент выполняет критически важную трансформацию декларативного SQL-запроса, сформулированного пользователем, в низкоуровневый и эффективный процедурный план выполнения. Важность планировщика заключается в том, что он позволяет пользователю и разработчику оперировать высокоуровневыми абстракциями, не задумываясь о физическом расположении информации, оставляя задачу поиска оптимального пути к данным за самой системой.
Основной задачей планировщика является выбор такого плана выполнения из множества потенциально возможных, который минимизирует общую стоимость операции. Под стоимостью, как правило, понимается интегральный показатель, включающий время отклика, загрузку центрального процессора, объем оперативной памяти и количество операций ввода-вывода. В документации SQLite прямо указывается, что «запрос может быть выполнен при помощи разных алгоритмов. Задача планировщика — выбрать один алгоритм из многих альтернативных».
Подавляющее большинство современных реляционных СУБД, включая PostgreSQL и MySQL, реализуют подход к оптимизации, основанный на оценке стоимости (cost-based optimization). В его основе лежит принцип генерации множества альтернативных планов выполнения с последующей оценкой стоимости каждого из них на основе статистической информации о данных. Статистика включает в себя такие метрики, как количество записей в таблицах, количество уникальных значений в столбцах, распределение данных и глубина индексов. Данный подход противопоставляется устаревшему эвристическому (rule-based optimization), где выбор плана определялся заранее заданными правилами без учета конкретного объема и характера данных.
Процесс планирования запроса является многоэтапным и может быть представлен в виде следующей последовательности действий:
Синтаксический анализ и построение дерева разбора. На данном этапе текст запроса проверяется на синтаксическую корректность и преобразуется в древовидную структуру, отражающую его логику.
Логическая оптимизация. Дерево запроса подвергается ряду эквивалентных преобразований, направленных на его упрощение без изменения семантики. К типичным операциям на этом этапе относятся: устранение избыточных условий, подстановка представлений, преобразование предикатов, исключение бесполезных соединений.
Генерация физических планов и оценка стоимости. Логически оптимизированное дерево служит основой для генерации множества физических планов. Для каждого узла дерева (например, операции соединения) рассматриваются все доступные алгоритмы его реализации (Nested Loop Join, Hash Join, Merge Join). На основе статистики о данных вычисляется предполагаемая стоимость каждого варианта.
Выбор оптимального плана. План с наименьшей совокупной расчетной стоимостью выбирается для последующего выполнения. Планировщик может использовать как нисходящий, так и восходящий подход (напр., динамическое программирование) для эффективного перебора пространства возможных планов.
Важно отметить, что эффективность работы планировщика всецело зависит от актуальности статистики о данных. Устаревшая статистика приводит к некорректной оценке стоимости и, как следствие, к выбору неоптимального плана выполнения. Поэтому современные СУБД предусматривают автоматические и ручные механизмы обновления статистических данных.
Обзор алгоритмов соединения и методов доступа к данным, используемых современными оптимизаторами
Эффективность плана выполнения запроса, сгенерированного оптимизатором, всецело зависит от корректного выбора двух ключевых элементов: метода доступа к данным (access method) и алгоритма соединения (join algorithm). Их стоимость и применимость в различных условиях формируют основу для построения и оценки планов выполнения современными cost-based оптимизаторами.
Методы доступа к данным определяют способ извлечения записей из таблицы и напрямую влияют на производительность операций выборки. К основным методам относятся:
Последовательное сканирование (Sequential Scan, Table Scan). Полное чтение всех страниц таблицы. Стоимость операции пропорциональна размеру таблицы. Данный метод является наиболее затратным для больших таблиц, но может быть оптимальным при необходимости чтения большой доли записей так как позволяет избежать накладных расходов на работу с индексами.
-
Сканирование по индексу (Index Scan). Доступ к данным через вспомогательную структуру (индекс), которая позволяет быстро найти строки, удовлетворяющие условию поиска. Эффективность зависит от типа индекса (B-дерево, хэш, GiST, SP-GiST, GIN, BRIN) и селективности условия. Внутри данного метода выделяют несколько стратегий:
Индексное сканирование (Index Scan): Индекс используется для определения местоположения нужных строк, после чего происходит обращение к основной таблице (heap) для извлечения полной строки.
Только индексное сканирование (Index-Only Scan): Если индекс содержит все столбцы, запрашиваемые в SELECT, необходимость в обращении к основной таблице отпадает, что значительно ускоряет выполнение запроса.
Битовое сканирование (Bitmap Scan): Промежуточный метод, при котором сначала с помощью индекса создается битовая карта страниц, содержащих подходящие строки. Эти страницы затем читаются последовательно, но уже выборочно. Этот подход эффективен для условий с низкой селективностью, где обычное индексное сканирование порождает много случайных обращений к диску.
Алгоритмы соединения являются краеугольным камнем обработки реляционных запросов. Выбор оптимального алгоритма соединения зависит от размера таблиц, наличия индексов, доступной памяти и предварительной отсортированности данных. Основные алгоритмы включают:
Вложенные циклы (Nested Loops Join). Базовый алгоритм, заключающийся в организации двух вложенных циклов: для каждой строки внешней таблицы происходит полное сканирование внутренней таблицы для поиска совпадающих строк. Его обобщенная стоимость оценивается как O(N M), где N и M — размеры таблиц. Несмотря на кажущуюся неэффективность, этот алгоритм может быть оптимальным, если внутренняя таблица мала или для нее существует эффективный индекс по ключу соединения, позволяющий избежать полного сканирования (Index Nested Loops Join). В этом случае стоимость снижается до O(N log(M)).
Слияние с сортировкой (Sort-Merge Join). Алгоритм состоит из двух этапов: сначала каждая из таблиц сортируется по ключу соединения, затем происходит однократное параллельное сканирование обеих отсортированных таблиц и слияние совпадающих строк. Стоимость алгоритма в основном определяется стоимостью операций сортировки: O(N log(N) + M log(M)). Данный метод эффективен, если одна или обе таблицы уже отсортированы по ключу соединения, или если результат соединения требуется передать операции, требующей отсортированных данных (например, ORDER BY).
Хэш-соединение (Hash Join). Алгоритм, также состоящий из двух фаз. На этапе построения в памяти создается хэш-таблица для меньшей из таблиц (внешней) по ключу соединения. На этапе зондирования происходит последовательное чтение второй таблицы (внутренней), хэширование ее ключа соединения и поиск совпадений в созданной хэш-таблице. Общая стоимость оценивается как O(N + M), что делает данный алгоритм чрезвычайно эффективным для соединения больших несортированных таблиц при наличии достаточного объема оперативной памяти для размещения хэш-таблицы.
Современные оптимизаторы, такие как в PostgreSQL и MySQL, реализуют все вышеперечисленные алгоритмы. Их задача — на основе статистики (размеров таблиц, селективности предикатов) корректно оценить стоимость каждого метода и выбрать наименее затратный.
Методология проведения исследования: описание тестового стенда, набора данных и критериев оценки
Целью экспериментального исследования являлась практическая проверка гипотезы о существенном различии в эффективности планировщиков различных СУБД на идентичной сложной нагрузке. Для достижения этой цели был разработан и реализован комплексный тестовый стенд.
Все эксперименты проводились на изолированной рабочей станции со следующей конфигурацией:
Аппаратное обеспечение: Процессор Apple M4 (10 ядер, 10 потоков), 16 ГБ оперативной памяти LPDDR5X, накопитель SSD.
Программное обеспечение: MacOS Sequoia 15.6.1.
Версии СУБД: PostgreSQL 17.5, MySQL 9.4.0_3, SQLite 3.50.4.
Конфигурация СУБД: Для обеспечения чистоты эксперимента все СУБД были использованы с настройками по умолчанию. Это позволяет оценить эффективность «из коробки» без дополнительной тонкой настройки, что релевантно для большинства типовых сценариев развертывания.
Для создания репрезентативной нагрузки был сгенерирован специализированный набор данных, имитирующий предметную область интернет-магазина. Набор включает сущности users, products, categories, orders, order_items (их структура отражена в Приложениях 2, 3, 4).
Для генерации данных был разработан скрипт на языке Python, который создал CSV-файлы с тестовыми данными, учитывающими необходимые связи и распределения (см. Приложение 1).
Далее были выполнены скрипты, создающие схемы таблиц, первичные и внешние ключи, а также индексы, оптимальные для тестовых запросов, в каждой из исследуемых СУБД (см. Приложение 2, 3, 4). После чего данные были загружены в соответствующие таблицы.
Для проведения нагрузочного тестирования был разработан набор из трех запросов (см. Приложение 5), охватывающих различные классы задач:
Запрос 1: Многотабличное соединение с агрегацией и фильтрацией.
Запрос 2: Подзапрос с оконной функцией.
Запрос 3: Рекурсивный CTE с последующим соединением и агрегацией.
Каждый запрос выполнялся в каждой СУБД последовательно 10 раз. Перед каждым прогоном данные заносились в кэш, чтобы планировщики могли выбирать самые производительные сценарии выполнения. Фиксировались следующие метрики:
Время планирования (Planning Time): Время, затраченное планировщиком на построение и выбор плана выполнения. Замерялось для PostgreSQL.
Время выполнения (Execution Time): Время, затраченное на непосредственное выполнение запроса.
Для замера времени использовались встроенные инструменты СУБД: EXPLAIN ANALYZE в PostgreSQL, EXPLAIN ANALYZE в MySQL и EXPLAIN QUERY PLAN в SQLite. После сбора сырых данных для каждого запроса и каждой СУБД вычислялось среднее значение времени выполнения.
Эффективность планировщиков оценивалась по следующим ключевым критериям:
Общее время отклика: Суммарное время, затраченное на планирование и выполнение запроса.
Адекватность выбранного плана: Анализ логичности и оптимальности плана выполнения, полученного с помощью инструментов EXPLAIN, на основе знаний об алгоритмах и метриках, изложенных в главе 1.
Ресурсоемкость: Косвенная оценка потребления ресурсов на основе анализа планов выполнения (например, наличие операций дискового ввода-вывода, использование временных файлов, объем памяти под хэш-таблицы).
Таким образом, разработанная методология обеспечивает повторяемость эксперимента и позволяет провести всестороннее сравнение эффективности планировщиков исследуемых СУБД.
Анализ выполнения запроса с многотабличным соединением
Первый тестовый запрос представляет собой сложный аналитический запрос, характерный для систем отчетности. Он включает многотабличное соединение, агрегацию данных, фильтрацию по нескольким полям и сортировку с ограничением количества результатов (LIMIT). Результаты его выполнения наиболее наглядно демонстрируют разницу в подходах планировщиков к обработке сложных соединений. С текстом запроса можно ознакомиться в приложении 5 (см. запрос 1).
Сводные результаты производительности выполнения запроса можно увидеть на таблице 1.
Таблица 1. Сводные результаты производительности выполнения запроса с многотабличным соединением
СУБД |
Среднее время выполнения (ms) |
Стандартное отклонение (ms) |
postgresql |
825,804 |
24,581 |
mysql |
5073,8 |
246,476 |
sqlite |
8417 |
221,33 |
Проведем анализ плана выполнения PostgreSQL (см. Приложение 6 – «план выполнения многотабличного соединения в PostgreSQL»).
Общую схему выполнения запроса, представленную в виде дерева запроса можно увидеть на рисунке 1:
Как можно ��аметить из плана выполнения запроса, планировщик использует параллелизм и хэширование для обработки больших объемов данных.
Отметим основные этапы выполнения запроса:
Доступ к таблице users происходил через Bitmap Index Scan по idx_users_city, что дает нам очень эффективное нахождение пользователей из Москвы.
В первом соединении (users + orders) используется Parallel Hash Join. Для достаточно большой таблицы orders выполняется Parallel Seq Scan с фильтром по дате и статусу, после чего происходит соединение с отфильтрованными пользователями через хэш-таблицу, построенную в памяти.
Во втором соединении планировщик выбрал Nested Loop. Для каждого заказа выполняется быстрый Index Scan по idx_order_items_order_id. Такой подход оптимально, так как для каждого заказа есть лишь несколько позиций.
Третье соединение происходит через Parallel Hash Join. При этом для более быстрого соединения строится хэш-таблица по всей (т.к. нет фильтра) таблице product.
Агрегация также проводится параллельно с помощью хэширования. В каждом из воркеров проходит первичная агрегация и быстрая сортировка по user_id. Далее происходит слияние данных от воркеров.
Финальная сортировка для limit делается эффективным методом top-N heapsort.
Далее рассмотрим анализ плана выполнения MySQL (см. Приложение 6 – «план выполнения многотабличного соединения в MySQL»).
Общую схему запроса в виде дерева можно увидеть на рисунке 2:
Планировщик MySQL использует стратегию построения глубокого конвейера из Nested Loop Joins, полагаясь на покрывающие индексы для минимизации обращений к таблицам.
Отметим главные этапы построения плана запроса:
Для доступа к пользователям используется поиск по покрывающему индексу idx_users_city_id_email, что дает нам быстрое получение нужных колонок без обращения к таблице.
При первом соединении для каждого пользователя из Москвы ищутся заказы через Covering index lookup по индексу idx_orders_user_status_date. Индекс отлично покрывает условия выборки по user_id, status и фильтр по order_date.
Во втором соединении для каждого найденного заказа через покрывающий индекс idx_order_items_order_product_qty находятся позиции каждого заказа.
Во время третьего соединения (с products) для каждой товарной позиции выполняется Single-row index lookup по первичному ключу, что дает высокий уровень производительности.
Агрегация и сортировка происходят в памяти после завершения всех вложенных циклов.
Запрос выполняется значительно дольше аналогичного запроса в PostgreSQL за счет отсутствия параллелизма и большой вложенности циклов (~1.76e+6 соединений для последнего), что создает огромное количество итераций, высокую CPU-нагрузку и не оптимально для больших объемов данных. В результате стратегия последовательно вложенных циклов, хоть и может быть эффективна в OLTP-запросах с маленькими выборками, значительно проигрывает в аналитических запросах с большими данными.
Далее проанализируем план выполнения запроса с многотабличным соединением в SQLite. (см. Приложение 6 - «план выполнения многотабличного соединения в SQLite»).
Общая схема запроса показана на рисунке 3:
SQLite в своей стратегии выполнения создает простой последовательный план. Мы можем увидеть полное отсутствие параллелизма и продвинутых алгоритмов соединения.
Рассмотрим ключевые этапы плана запроса в SQLite:
Оптимизатор начал с фильтрации заказов. Использует поиск по индексу idx_orders_status_date.
Далее для каждого отфильтрованного заказа находится пользователь по первичному ключу.
Для присоединения позиций заказов используется поиск по покрывающему индексу idx_order_items_order_product_qty.
Поиск товаров для присоединения проводится по первичному ключу.
Для агрегации и сортировки SQLite создает временные структуры, что не оптимально при больших данных.
В плане запроса мы можем увидеть отсутствие параллелизма, продвинутых алгоритмов присоединения и работу со временными данными, что дает наихудшее время выполнения в тестах.
Таким образом, планировщик PostgreSQL продемонстрировал превосходство в обработке сложных аналитических запросов с большими объемами данных. Планировщик MySQL, хоть и идеально использует индексы, не смог отказаться от стратегии Nested Loop, что привело к плохому результату на большом объеме данных. Планировщик SQLiteне обладает параллелизмом и эффективными алгоритмами соединения, а так же производит некоторую работу с данными на диске, из-за чего запрос оказался наиболее долгим.
Анализ выполнения запроса с подзапросом и оконной функцией
Второй тестовый запрос предназначен для оценки эффективности планировщиков при обработке сложных конструкций, сочетающих коррелированные подзапросы и оконные функции. Текст запроса приведен в Приложении 5 (Текст запроса 2).
Сводные результаты производительности выполнения запроса можно увидеть на таблице 2
Таблица 2. Сводные результаты производительности выполнения запроса с позапросом и оконной функцией
СУБД |
Среднее время выполнения (ms) |
Стандартное отклонение (ms) |
postgresql |
285,4522 |
74,305 |
mysql |
491,8 |
13,02 |
sqlite |
156,5 |
80,512 |
Данные результаты демонстрируют принципиально иной порядок эффективности по сравнению с первым запросом. SQLite показал наилучшее время выполнения, более чем в 3 раза опередив MySQL.
Проведем анализ плана выполнения PostgreSQL (см. Приложение 6 – «План выполнения запроса c подзапросом и оконной функцией в PostgreSQL»).
Общая схема построенного запроса представлена на рисунке 4:
Детальнее рассмотрим важные этапы плана запроса:
Поиск категорий осуществляется через Index Scan по первичному ключу
Для соединения товаров с категориями проводится битовое сканирования индекса товаров idx_products_category_id с последующим битовым сканированием кучи.
Коррелированный подзапрос SubPlan 1 выполняется для каждой из 19999 строк основного результата. Производится Index Only Scan по idx_order_items_product_id. Это означает, что данные для count(*) берутся прямо из индекса, без обращения к таблице.
Оконная функция выполняется после подготовки всех данных.
Сортировка выполняется в памяти методом быстрой сортировки.
В результате выполнение запроса мы видим хорошую производительность за счет эффективного доступа к данным через индексы на всех этапах выполнения.
Перейдем к анализу плана выполнения MySQL (см. Приложение 6 – «план выполнения запроса c подзапросом и оконной функцией MySQL»).
Общая схема запроса представлена на рисунке 5:
Интересной особенностью запроса является то, что планировщик решил материализовать промежуточный результат во временную таблицу перед вычислением оконной функции.
Рассмотрим основные этапы построенного плана запроса:
Доступ к основным данным осуществляется аналогично PG: Nested loop между categories (через сканирование в покрывающем индексе idx_parent_category) и products (через сканирование по индексу idx_products_category_id).
Сортировка по p.category_id необходима для последующего вычисления оконной функции «partition by p.category_id».
При выполнении оконной функции данные буферезируются и обрабатываются.
Далее MySQL явно создает временную таблицу для хранения результата перед финальной сортировкой. Это добавляет накладные расходы
В плане не видно коррелированного подзапроса! Судя по времени выполнения, MySQL, скорее всего, выполняет его для каждой строки после формирования основного набора данных.
Проанализировав план выполнения запроса и время его выполнения можно сказать, что стратегия MySQL оказалась неэффективной для этого запроса, т.к. время выполнения является наихудшим. Это связано с созданием временной таблицы, дополнительной сортировкой для оконной функции и возможной неоптимальной реализацией коррелированного подзапроса.
Теперь проанализируем план, составленный оптимизатором SQLite (см. Приложение 6 – «план выполнения запроса c подзапросом и оконной функцией SQLite»).
Общая схема для плана выглядит следующим образом:
Отметим основные особенности выполнения запроса:
Co-routine: вместо того чтобы выполнять вложенный запрос в where (select category_id from categories...) отдельно, SQLite превращает его в ко-рутину. Это означает, что он становится частью основного конвейера обработки данных, а не отдельным предварительным шагом
SQLite активно использует фильтр Блума - создает вероятностную структуру данных для быстрой проверки принадлежности category_id к результату подзапроса. Это позволяет очень быстро отфильтровывать ненужные строки на раннем этапе.
Сканирование товаров происходит с учетом ко-рутины и фильтра Блума.
В коррелированном подзапросе используется покрывающий индекс, что делает его эффективным несмотря на выполнение для каждой отдельной строки.
Планировщик SQLite выстроил запрос с учетом таких методов оптимизации, как ко-рутины для плавного и эффективного чтения данных и фильтры Блума для оптимизации фильтрации, за счет чего выигрывает по производительности у PostgreSQL и MySQL.
Таким образом, во втором запросе три оптимизатора продемонстрировали разные подходы к планированию. PostgreSQL выстроил прямой и эффективный план выполнения, основанный на подзапросе, идеально использовав индексы, что сделало запрос достаточно эффективным. MySQL выбрал затратную стратегию с материализацией, которая не подошла для данного случая, из-за чего запрос получился относительно медленным. SQLite применил экзотические и крайне эффективные оптимизации, которые позволили ему обойти даже мощный оптимизатор PostgreSQL.
Анализ выполнения запроса с рекурсивным CTE
Третий тестовый запрос предназначен для оценки эффективности планировщиков при обработке рекурсивных запросов и их последующей интеграции в более сложные операции соединения и агрегации. Текст запроса приведен в Приложении 5 (Текст запроса 3). Данный тип нагрузки наиболее сложен для оптимизации, так как требует работы с иерархическими данными и их эффективного соединения с большими таблицами.
Сводные данные по времени выполнения запроса можно увидеть в таблице 3.
Таблица 3. Сводные результаты производительности выполнения запроса с рекурсивным cte
СУБД |
Среднее время выполнения (ms) |
Стандартное отклонение (ms) |
postgresql |
2148,256 |
7,023 |
mysql |
1689,6 |
12,536 |
sqlite |
991 |
6,616 |
Результаты демонстрируют следующую картину: SQLite уверенно лидирует, а PostgreSQL, несмотря на всю свою мощь, показывает наихудший результат.
Проведем анализ плана выполнения PostgreSQL (см. Приложение 6 – «План выполнения запроса с рекурсивным cte в PostgreSQL»).
Общая схема построенного запроса представлена на рисунке 7:
На схеме можно увидеть, что оптимизатор решил построить весь результирующий набор и отсортировать его, прежде чем применить limit.
Рассмотрим основные этапы выполнения запроса:
CTE выполняется молниеносно (0.088 ms, в конечном наборе 11 строк). Использует Hash Join между рабочим набором (WorkTable) и всей таблицей categories.
Выполняет Index Only Scan по order_items, который уже отсортирован по product_id.
Материализуется и сортируется результат соединения товаров с CTE.
Для соединения товаров с позициями заказов планировщик применяет Merge Join.
Мы наблюдаем провал стратегии планировщика. Оптимизатор отдал выбор Merge Join, который идеален для двух больших отсортированных наборов. Но в плане наблюдается огромная диспропорция: с одной стороны 22047 продукта, с другой – 34993781 позиций в заказах. Merge Join вынужден "протаскивать" весь огромный набор order_items через соединение, что и занимает основное время (rows=3852847 loops=1 — это количество совпадений). В итоге планировщик не смог выбрать оптимальный вариант соединения.
Таким образом, неэффективная стратегия оптимизатора PostgreSQL дала наихудшее время выполнения запроса среди всех рассматриваемых СУБД.
Далее рассмотрим план выполнения MySQL (см. Приложение 6 – «План выполнения запроса с рекурсивным cte в MySQL»).
Общая схема запроса представлена на рисунке 8:
На схеме можно увидеть, что оптимизатор использует «классический» для MySQL подход с использованием вложенных Nested Loop, который в данном случае оказался эффективнее алгоритмов PG.
Отметим ключевые аспекты плана запроса:
Планировщик материализует результат выполнения рекурсивного CTE (11 строк) во временную таблиц.
В серии Nested Loop для каждой из 11 категорий в CTE находится ~2004 товара через Index lookup (idx_products_category_id). Как итог - ~22047 товаров. Далее для каждого из товаров находится ~175 позиций в заказах через поиск в покрывающем индексе (idx_order_items_product_id_quantity).
Хотя количество итераций большое (~3.85e+6), каждая итерация невероятно быстрая за счет поиска в покрывающем индексе. Кроме того, агрегация и сортировка, судя по всему, делаются более эффективно. Как итог, «простая» стратегия MySQL оказалась эффективнее «сложной» стратегии PG в данном сценарии. Как результат – относительно хорошее время выполнения запроса.
Перейдем к анализу плана запроса, который составил оптимизатор SQLite. (см. Приложение 6 – «План выполнения запроса с рекурсивным cte в SQLite»).
Общая схема построенного запроса представлена на рисунке 9.:
Из рисунка 9. можно сделать вывод, что оптимизатор SQLite вновь применяет специфические методы для избежания ненужной работы.
Отметим основные этапы построения запроса:
Как и в прошлом запросе, SQLite интегрирует CTE в конвейер обработки и использует фильтр Блума для эффективной фильтрации товаров по категориям.
Судя по плану, в основном соединении SQLite проходит по товарам и для каждого эффективно ищет позиции заказов через индекс.
SQLite автоматически создает покрывающий индекс в памяти для результата CTE чтобы максимально ускорить соединение с товарами. Этого не сделали ни PG, ни MySQL.
В результате анализа плана запроса можно сделать вывод о том, почему он оказался наиболее эффективным. Оптимизатор SQLite использовал автоматическое создание индексов для промежуточных результатов, интегрировал в запрос ко-рутины и фильтр Блума.
Таким образом, в третьем запросе мы увидели, что даже самые продвинутые планировщики могут выбрать неэффективную стратегию. Оптимизатор PostgreSQL совершил ошибку и выбрал неэффективный метод соединения, тогда как оптимизатор SQLite вновь показал продвинутые алгоритмы сканирования и сортировки, за счет чего вновь показал отличную производительность.
Обобщение результатов и систематизация сильных и слабых сторон планировщиков
Проведенное экспериментальное исследование позволяет сделать однозначный вывод о кардинальном различии в эффективности планировщиков исследуемых СУБД при выполнении разных типов запросов. Для наглядности все результаты сведены в единую таблицу (см. таблица 4.):
Таблица 4. Общая таблица результатов тестируемых СУБД на разных запросах
Тип запроса |
PostgreSQL |
MySQL |
SQLite |
Многотабличный join |
~ 825,804 ms |
~ 5073,8 ms |
~ 8417 ms |
Запрос с подзапросом и оконной функцией |
~ 285,452 ms |
~ 491,8 ms |
~ 156,5 ms |
Запрос с рекурсивным CTE |
~ 2148,256 ms |
~ 1689,6 ms |
~ 991 ms |
Данные результаты позволяют систематизировать сильные и слабые стороны каждого из исследуемых планировщиков, выявленные в ходе тестирования.
PostgreSQL имеет оптимизатор, который силен в сложных аналитических запросах с большими соединениями благодаря использованию хэш-соединений и агрессивному параллелизму. Слаб в ситуациях, где требуется ранняя остановка и где его сложные алгоритмы могут ошибиться в выборе плана.
MySQL обладаем оптимизатором с предсказуемым поведением в OLTP. Силен в точечных запросах, отлично использует покрывающие индексы при их наличии. Его стратегия Nested Loop предсказуема и часто эффективна. Слаб в обработке больших данных, где требуются алгоритмы типа Hash Join, и в сложных аналитических запросах.
Оптимизатор SQLite обладает удивительно продвинутыми и уникальными оптимизациями (ко-рутины, фильтры Блума, автоматические индексы для промежуточных результатов), которые позволяют ему побеждать в специфичных сценариях. Его слабость — отсутствие параллелизма и продвинутых алгоритмов работы с большими данными.
Заключение
Основная цель работы — провести сравнительный анализ и выявить сильные и слабые стороны планировщиков различных СУБД на специфичной нагрузке — была достигнута.
Выдвинутая в начале исследования гипотеза получила подтверждение. Экспериментально установлено, что различные СУБД действительно демонстрируют существенно разную эффективность на одних и тех же сложных запросах, и это различие носит системный характер, обусловленный фундаментальными различиями в алгоритмах планирования
В заключение, можно утверждать, что оптимальный выбор системы управления базами данных должен в первую очередь зависеть от характера предполагаемой нагрузки и специфики выполняемых запросов. Для сложных аналитических задач с большими соединениями и агрегацией рекомендуется использовать PostgreSQL, мощный планировщик которого эффективно использует параллелизм и богатый набор алгоритмов. MySQL является надежным выбором для высокопроизводительных OLTP-нагрузок, где преобладают короткие транзакции и операции точечного обновления, идеально использующие индексы. SQLite, в свою очередь, представляет собой уникальное решение для встраиваемых систем и приложений, а его архитектурные преимущества в виде нулевых сетевых издержек и инновационных оптимизаций оказываются решающими.
Приложения
Приложение 1
Скрипт генерации тестовых данных на python:
import csv
from faker import Faker
import random
from datetime import datetime, timedelta
import pandas as pd
from sqlalchemy import create_engine, text
import os
NUM_USERS = 100000
NUM_PRODUCTS = 200000
NUM_CATEGORIES = 100
NUM_ORDERS = 10000000
MAX_ITEMS_PER_ORDER = 6
fake = Faker('ru_RU')
users = []
products = []
categories = []
orders = []
order_items = []
print("Генерация категорий...")
for i in range(1, NUM_CATEGORIES + 1):
parent_id = random.randint(1, 10) if i > 10 else None
categories.append({
'category_id': i,
'name': fake.word().capitalize() + ' ' + fake.word().capitalize(),
'parent_category_id': parent_id
})
print("Генерация пользователей...")
cities = ['Москва', 'Санкт-Петербург', 'Новосибирск', 'Екатеринбург', 'Казань']
for i in range(1, NUM_USERS + 1):
users.append({
'user_id': i,
'email': fake.email(),
'registration_date': fake.date_between(start_date='-5y', end_date='today'),
'city': random.choice(cities)
})
print("Генерация товаров...")
product_names = ['Смартфон', 'Ноутбук', 'Наушники', 'Телевизор', 'Планшет',
'Часы', 'Фотоаппарат', 'Игровая консоль', 'Монитор', 'Клавиатура']
for i in range(1, NUM_PRODUCTS + 1):
products.append({
'product_id': i,
'name': f"{random.choice(product_names)} {fake.word().capitalize()} {random.randint(1, 1000)}",
'price': round(random.uniform(100, 50000), 2),
'category_id': random.randint(1, NUM_CATEGORIES)
})
print("Генерация заказов...")
statuses = ['created', 'paid', 'delivered', 'cancelled']
start_date = datetime(2023, 1, 1)
end_date = datetime(2023, 12, 31)
for i in range(1, NUM_ORDERS + 1):
orders.append({
'order_id': i,
'user_id': random.randint(1, NUM_USERS),
'status': random.choice(statuses),
'order_date': fake.date_time_between_dates(
datetime_start=start_date,
datetime_end=end_date
)
})
print("Генерация позиций заказов...")
order_item_id = 1
for order in orders:
num_items = random.randint(1, MAX_ITEMS_PER_ORDER)
for _ in range(num_items):
product = random.choice(products)
order_items.append({
'order_item_id': order_item_id,
'order_id': order['order_id'],
'product_id': product['product_id'],
'quantity': random.randint(1, 3)
})
order_item_id += 1
print("Сохранение в CSV файлы...")
def save_to_csv(data, filename):
if data:
df = pd.DataFrame(data)
df.to_csv(filename, index=False, encoding='utf-8')
print(f"Создан файл: {filename} ({len(data)} записей)")
os.makedirs('test_data', exist_ok=True)
save_to_csv(users, 'test_data/users.csv')
save_to_csv(products, 'test_data/products.csv')
save_to_csv(categories, 'test_data/categories.csv')
save_to_csv(orders, 'test_data/orders.csv')
save_to_csv(order_items, 'test_data/order_items.csv')
print("\nГенерация завершена!")
print(f"Всего сгенерировано:")
print(f" Пользователей: {NUM_USERS}")
print(f" Товаров: {NUM_PRODUCTS}")
print(f" Категорий: {NUM_CATEGORIES}")
print(f" Заказов: {NUM_ORDERS}")
print(f" Позиций в заказах: {len(order_items)}")Приложение 2
Скрипт создания и заполнения таблиц в PostgreSQL:
import pandas as pd
from sqlalchemy import create_engine, text
import os
DB_CONFIG = {
'host': '',
'port': '',
'database': '',
'user': '',
'password': '',
'schema': ''
}
def load_data_to_db():
connection_string = (
f"postgresql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@"
f"{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
)
engine = create_engine(
connection_string,
connect_args={"options": f"-c search_path={DB_CONFIG['schema']}"}
)
with engine.connect() as conn:
conn.execute(text("""
CREATE TABLE IF NOT EXISTS categories (
category_id SERIAL PRIMARY KEY,
name VARCHAR(255),
parent_category_id INTEGER
);
CREATE TABLE IF NOT EXISTS users (
user_id SERIAL PRIMARY KEY,
email VARCHAR(255),
registration_date DATE,
city VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10,2),
category_id INTEGER REFERENCES categories(category_id)
);
CREATE TABLE IF NOT EXISTS orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
status VARCHAR(50),
order_date TIMESTAMP
);
CREATE TABLE IF NOT EXISTS order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id),
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER
);
"""))
conn.commit()
tables = ['categories', 'users', 'products', 'orders', 'order_items']
for table in tables:
df = pd.read_csv(f'test_data/{table}.csv')
df.to_sql(table, engine, if_exists='append', index=False)
print(f"Загружена таблица: {table} ({len(df)} записей)")
print("Все данные успешно загружены в PostgreSQL!")
if __name__ == "__main__":
load_data_to_db()Приложение 3
Скрипт создания и заполнения таблиц в MySQL:
import pandas as pd
from sqlalchemy import create_engine, text
import os
from tqdm import tqdm
DB_CONFIG = {
'host': '',
'port': '',
'database': '',
'user': '',
}
def load_data_to_db():
connection_string = (
f"mysql+mysqlconnector://{DB_CONFIG['user']}@"
f"{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
)
engine = create_engine(connection_string)
with engine.connect() as conn:
conn.execute(text("SET GLOBAL max_allowed_packet = 1073741824;"))
conn.commit()
with engine.connect() as conn:
conn.execute(text("""
CREATE TABLE IF NOT EXISTS categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
parent_category_id INT,
INDEX idx_parent_category (parent_category_id)
) ENGINE=InnoDB;
"""))
conn.execute(text("""
CREATE TABLE IF NOT EXISTS users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
registration_date DATE,
city VARCHAR(100),
INDEX idx_city (city)
) ENGINE=InnoDB;
"""))
conn.execute(text("""
CREATE TABLE IF NOT EXISTS products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10,2),
category_id INT,
INDEX idx_category (category_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;
"""))
conn.execute(text("""
CREATE TABLE IF NOT EXISTS orders (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
status VARCHAR(50),
order_date TIMESTAMP,
INDEX idx_user (user_id),
INDEX idx_status_date (status, order_date),
FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
"""))
conn.execute(text("""
CREATE TABLE IF NOT EXISTS order_items (
order_item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT,
product_id INT,
quantity INT,
INDEX idx_order (order_id),
INDEX idx_product (product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
"""))
conn.commit()
tables_load_order = ['categories', 'users', 'products', 'orders', 'order_items']
for table in tables_load_order:
csv_file = f'test_data/{table}.csv'
file_size = os.path.getsize(csv_file)
print(f"Загрузка {table} (размер файла: {file_size / (1024 ** 3):.2f} GB)")
if table in ['orders', 'order_items'] and file_size > 100 * 1024 * 1024: # >100MB
load_large_table_in_chunks(engine, table, csv_file)
else:
load_small_table(engine, table, csv_file)
def load_small_table(engine, table_name, csv_file):
"""Загрузка небольших таблиц целиком"""
df = pd.read_csv(csv_file)
id_columns = ['id', f'{table_name}_id']
for id_col in id_columns:
if id_col in df.columns:
df = df.drop(columns=[id_col])
break
df.to_sql(table_name, engine, if_exists='append', index=False, method='multi', chunksize=1000)
print(f"Загружена таблица: {table_name} ({len(df)} записей)")
def load_large_table_in_chunks(engine, table_name, csv_file, chunk_size=50000):
"""Потоковая загрузка больших таблиц частями"""
total_rows = count_rows_in_csv(csv_file)
print(f"Начинаем потоковую загрузку {table_name} (~{total_rows} строк)...")
chunk_iter = pd.read_csv(csv_file, chunksize=chunk_size)
for chunk_number, df_chunk in enumerate(tqdm(chunk_iter, total=total_rows // chunk_size + 1, desc=table_name)):
id_columns = ['id', f'{table_name}_id']
for id_col in id_columns:
if id_col in df_chunk.columns:
df_chunk = df_chunk.drop(columns=[id_col])
break
df_chunk.to_sql(
table_name,
engine,
if_exists='append',
index=False,
method='multi'
)
print(f"Завершена загрузка таблицы: {table_name}")
def count_rows_in_csv(csv_file):
"""Подсчет количества строк в CSV файле (быстрый способ)"""
with open(csv_file, 'r', encoding='utf-8') as f:
return sum(1 for _ in f) - 1 # minus header
if __name__ == "__main__":
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)
try:
load_data_to_db()
print("Все данные успешно загружены в MySQL!")
except Exception as e:
print(f"Ошибка при загрузке данных: {e}")
raiseПриложение 4
Скрипт создания и заполнения таблиц в SQLite:
import pandas as pd
import sqlite3
import os
def load_data_to_db():
db_path = './testdb.db'
conn = sqlite3.connect(db_path)
conn.execute("PRAGMA foreign_keys = ON")
with conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
parent_category_id INTEGER
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT,
registration_date TEXT, -- SQLite использует TEXT для дат
city TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
price REAL, -- SQLite использует REAL для десятичных чисел
category_id INTEGER,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
status TEXT,
order_date TEXT, -- SQLite использует TEXT для временных меток
FOREIGN KEY (user_id) REFERENCES users(user_id)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS order_items (
order_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
)
""")
tables = ['categories', 'users', 'products', 'orders', 'order_items']
for table in tables:
csv_file = f'test_data/{table}.csv'
if os.path.exists(csv_file):
df = pd.read_csv(csv_file)
if 'id' in df.columns or f'{table}_id' in df.columns:
id_column = 'id' if 'id' in df.columns else f'{table}_id'
df = df.drop(columns=[id_column])
df.to_sql(table, conn, if_exists='append', index=False)
print(f"Загружена таблица: {table} ({len(df)} записей)")
else:
print(f"Файл {csv_file} не найден")
with conn:
conn.execute("CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_category_id)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_users_city ON users(city)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_status_date ON orders(status, order_date)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id)")
conn.close()
print("Все данные успешно загружены в SQLite!")
print(f"База данных создана: {db_path}")
if __name__ == "__main__":
load_data_to_db()Приложение 5
Текст запроса 1:
select
u.user_id,
u.email,
count(o.order_id) AS total_orders,
sum(oi.quantity * p.price) AS total_revenue
from nir_7_semestr.users u
join nir_7_semestr.orders o ON u.user_id = o.user_id
join nir_7_semestr.order_items oi ON o.order_id = oi.order_id
join nir_7_semestr.products p ON oi.product_id = p.product_id
where o.status = 'delivered'
and o.order_date between '2023-01-01' and '2023-12-31'
and u.city = 'Москва'
group by u.user_id, u.email
having sum(oi.quantity * p.price) > 10000
order by total_revenue desc
limit 100;Текст запроса 2:
SELECT
p.product_id,
p.name,
p.price,
(SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.product_id) AS times_ordered,
AVG(p.price) OVER (PARTITION BY p.category_id) AS avg_price_in_category
FROM products p
WHERE p.category_id IN (
SELECT category_id
FROM categories
WHERE parent_category_id = :parent_category_id
)
ORDER BY times_ordered DESC;Текст запроса 3:
with recursive subcategories as (
select category_id, name
from nir_7_semestr.categories
where category_id = 5
union all
select c.category_id, c.name
from nir_7_semestr.categories c
join subcategories s on c.parent_category_id = s.category_id
)
select
p.product_id,
p.name,
sum(oi.quantity) as total_quantity_sold
from nir_7_semestr.products p
join nir_7_semestr.order_items oi on p.product_id = oi.product_id
join subcategories s on s.category_id = p.category_id
group by p.product_id, p.name
order by total_quantity_sold desc
limit 10;Приложение 6
План выполнения многотабличного соединения в PostgreSQL:
Limit (cost=463257.80..463258.05 rows=100 width=68) (actual time=789.807..791.682 rows=100 loops=1)
-> Sort (cost=463257.80..463274.76 rows=6782 width=68) (actual time=789.805..791.676 rows=100 loops=1)
Sort Key: (sum(((oi.quantity)::numeric * p.price))) DESC
Sort Method: top-N heapsort Memory: 47kB
-> Finalize GroupAggregate (cost=457538.49..462998.60 rows=6782 width=68) (actual time=771.902..789.932 rows=20118 loops=1)
Group Key: u.user_id
Filter: (sum(((oi.quantity)::numeric * p.price)) > '10000'::numeric)
-> Gather Merge (cost=457538.49..462286.45 rows=40694 width=68) (actual time=771.896..777.789 rows=60338 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=456538.46..456589.33 rows=20347 width=68) (actual time=765.931..766.654 rows=20113 loops=3)
Sort Key: u.user_id
Sort Method: quicksort Memory: 3287kB
Worker 0: Sort Method: quicksort Memory: 3286kB
Worker 1: Sort Method: quicksort Memory: 3286kB
-> Partial HashAggregate (cost=454828.04..455082.38 rows=20347 width=68) (actual time=759.249..763.721 rows=20113 loops=3)
Group Key: u.user_id
Batches: 1 Memory Usage: 11025kB
Worker 0: Batches: 1 Memory Usage: 11025kB
Worker 1: Batches: 1 Memory Usage: 11025kB
-> Parallel Hash Join (cost=6366.70..445596.79 rows=738500 width=44) (actual time=24.064..644.168 rows=585742 loops=3)
Hash Cond: (oi.product_id = p.product_id)
-> Nested Loop (cost=1605.64..438897.13 rows=738500 width=40) (actual time=4.306..518.280 rows=585742 loops=3)
-> Parallel Hash Join (cost=1605.20..146021.33 rows=211026 width=32) (actual time=4.229..239.329 rows=167491 loops=3)
Hash Cond: (o.user_id = u.user_id)
-> Parallel Seq Scan on orders o (cost=0.00..141693.53 rows=1037138 width=8) (actual time=0.059..181.108 rows=832465 loops=3)
Filter: ((order_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (order_date <= '2023-12-31 00:00:00'::timestamp without time zone) AND ((status)::text = 'delivered'::text))
Rows Removed by Filter: 2500868
-> Parallel Hash (cost=1455.59..1455.59 rows=11969 width=28) (actual time=4.130..4.130 rows=6706 loops=3)
Buckets: 32768 Batches: 1 Memory Usage: 1568kB
-> Parallel Bitmap Heap Scan on users u (cost=233.98..1455.59 rows=11969 width=28) (actual time=0.649..3.107 rows=6706 loops=3)
Recheck Cond: ((city)::text = 'Москва'::text)
Heap Blocks: exact=395
-> Bitmap Index Scan on idx_users_city (cost=0.00..228.89 rows=20347 width=0) (actual time=0.486..0.486 rows=20118 loops=1)
Index Cond: ((city)::text = 'Москва'::text)
-> Index Scan using idx_order_items_order_id on order_items oi (cost=0.44..1.19 rows=20 width=12) (actual time=0.001..0.001 rows=3 loops=502473)
Index Cond: (order_id = o.order_id)
-> Parallel Hash (cost=3290.47..3290.47 rows=117647 width=12) (actual time=19.426..19.426 rows=66667 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 11488kB
-> Parallel Seq Scan on products p (cost=0.00..3290.47 rows=117647 width=12) (actual time=0.064..9.382 rows=66667 loops=3)
План выполнения многотабличного соединения в MySQL:
-> Limit: 100 row(s) (actual time=5096..5096 rows=100 loops=1)
-> Sort: total_revenue DESC (actual time=5096..5096 rows=100 loops=1)
-> Filter: (`sum((oi.quantity * p.price))` > 10000) (actual time=1.2..5088 rows=20118 loops=1)
-> Stream results (cost=445947 rows=88713) (actual time=1.2..5086 rows=20118 loops=1)
-> Group aggregate: sum((oi.quantity * p.price)), count(o.order_id), sum((oi.quantity * p.price)) (cost=445947 rows=88713) (actual time=1.19..5082 rows=20118 loops=1)
-> Nested loop inner join (cost=373458 rows=314604) (actual time=0.184..4690 rows=1.76e+6 loops=1)
-> Nested loop inner join (cost=263347 rows=314604) (actual time=0.172..3298 rows=1.76e+6 loops=1)
-> Nested loop inner join (cost=140619 rows=90530) (actual time=0.139..360 rows=502473 loops=1)
-> Covering index lookup on u using idx_users_city_id_email (city = 'Москва') (cost=4817 rows=33762) (actual time=0.0707..6.99 rows=20118 loops=1)
-> Filter: (o.order_date between '2023-01-01' and '2023-12-31') (cost=1.61 rows=2.68) (actual time=0.0067..0.0167 rows=25 loops=20118)
-> Covering index lookup on o using idx_orders_user_status_date (user_id = u.user_id, status = 'delivered') (cost=1.61 rows=24.1) (actual time=0.00632..0.0089 rows=25 loops=20118)
-> Filter: (oi.product_id is not null) (cost=1.01 rows=3.48) (actual time=0.00532..0.0057 rows=3.5 loops=502473)
-> Covering index lookup on oi using idx_order_items_order_product_qty (order_id = o.order_id) (cost=1.01 rows=3.48) (actual time=0.00525..0.00553 rows=3.5 loops=502473)
-> Single-row index lookup on p using PRIMARY (product_id = oi.product_id) (cost=0.25 rows=1) (actual time=702e-6..717e-6 rows=1 loops=1.76e+6)
План выполнения многотабличного соединения в SQLite:
12 0 50 SEARCH o USING INDEX idx_orders_status_date (status=? AND order_date>? AND order_date<?)
19 0 45 SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
24 0 56 SEARCH oi USING COVERING INDEX idx_order_items_order_product_qty (order_id=?)
28 0 45 SEARCH p USING INTEGER PRIMARY KEY (rowid=?)
31 0 0 USE TEMP B-TREE FOR GROUP BY
88 0 0 USE TEMP B-TREE FOR ORDER BYПлан выполнения запроса c подзапросом и оконной функцией в PostgreSQL:
Sort (cost=246692.55..246742.55 rows=20000 width=93) (actual time=228.673..229.145 rows=19999 loops=1)
Sort Key: ((SubPlan 1)) DESC
Sort Method: quicksort Memory: 2759kB
-> WindowAgg (cost=97.57..245263.78 rows=20000 width=93) (actual time=4.453..226.421 rows=19999 loops=1)
-> Nested Loop (cost=23.54..7163.78 rows=20000 width=53) (actual time=0.298..10.912 rows=19999 loops=1)
-> Index Scan using categories_pkey on categories (cost=0.14..13.89 rows=10 width=4) (actual time=0.010..0.029 rows=10 loops=1)
Filter: (parent_category_id = 5)
Rows Removed by Filter: 90
-> Bitmap Heap Scan on products p (cost=23.39..694.99 rows=2000 width=53) (actual time=0.192..0.950 rows=2000 loops=10)
Recheck Cond: (category_id = categories.category_id)
Heap Blocks: exact=13001
-> Bitmap Index Scan on idx_products_category_id (cost=0.00..22.89 rows=2000 width=0) (actual time=0.103..0.103 rows=2000 loops=10)
Index Cond: (category_id = categories.category_id)
SubPlan 1
-> Aggregate (cost=11.88..11.89 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=19999)
-> Index Only Scan using idx_order_items_product_id on order_items oi (cost=0.44..11.45 rows=172 width=0) (actual time=0.002..0.007 rows=175 loops=19999)
Index Cond: (product_id = p.product_id)
Heap Fetches: 241
План выполнения запроса c подзапросом и оконной функцией в MySQL:
-> Sort: times_ordered DESC (actual time=490..491 rows=19999 loops=1)
-> Table scan on <temporary> (cost=2.5..2.5 rows=0) (actual time=485..487 rows=19999 loops=1)
-> Temporary table (cost=0..0 rows=0) (actual time=485..485 rows=19999 loops=1)
-> Window aggregate with buffering: avg(p.price) OVER (PARTITION BY p.category_id ) (actual time=468..484 rows=19999 loops=1)
-> Sort: p.category_id (actual time=467..468 rows=19999 loops=1)
-> Stream results (cost=6919 rows=19764) (actual time=0.691..463 rows=19999 loops=1)
-> Nested loop inner join (cost=6919 rows=19764) (actual time=0.532..18.9 rows=19999 loops=1)
-> Covering index lookup on categories using idx_parent_category (parent_category_id = 5) (cost=1.25 rows=10) (actual time=0.0282..0.0357 rows=10 loops=1)
-> Index lookup on p using idx_products_category_id (category_id = categories.category_id) (cost=514 rows=1976) (actual time=0.137..1.82 rows=2000 loops=10)
План выполнения запроса c подзапросом и оконной функцией в SQLite:
3 0 0 CO-ROUTINE (subquery-4)
7 3 107 SEARCH p USING INDEX idx_products_category (category_id=?)
11 3 0 LIST SUBQUERY 2
14 11 53 SEARCH categories USING COVERING INDEX idx_categories_parent (parent_category_id=?)
22 11 0 CREATE BLOOM FILTER
50 0 95 SCAN (subquery-4)
106 0 0 CORRELATED SCALAR SUBQUERY 1
111 106 54 SEARCH oi USING COVERING INDEX idx_order_items_product (product_id=?)
127 0 0 USE TEMP B-TREE FOR ORDER BYПлан выполнения запроса с рекурсивным cte в PostgreSQL:
Limit (cost=4279539.30..4279539.32 rows=10 width=49) (actual time=2151.867..2151.870 rows=10 loops=1)
CTE subcategories
-> Recursive Union (cost=0.00..47.26 rows=901 width=34) (actual time=0.021..0.088 rows=11 loops=1)
-> Seq Scan on categories (cost=0.00..2.25 rows=1 width=34) (actual time=0.020..0.025 rows=1 loops=1)
Filter: (category_id = 5)
Rows Removed by Filter: 99
-> Hash Join (cost=0.33..3.60 rows=90 width=34) (actual time=0.022..0.026 rows=5 loops=2)
Hash Cond: (c.parent_category_id = s_1.category_id)
-> Seq Scan on categories c (cost=0.00..2.00 rows=100 width=38) (actual time=0.009..0.012 rows=100 loops=2)
-> Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.004..0.005 rows=6 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> WorkTable Scan on subcategories s_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.002 rows=6 loops=2)
-> Sort (cost=4279492.04..4279992.04 rows=200000 width=49) (actual time=2151.866..2151.867 rows=10 loops=1)
Sort Key: (sum(oi.quantity)) DESC
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=166259.40..4275170.11 rows=200000 width=49) (actual time=72.631..2150.107 rows=22047 loops=1)
Group Key: p.product_id
-> Merge Join (cost=166259.40..3484887.01 rows=157656620 width=45) (actual time=72.402..2061.097 rows=3852847 loops=1)
Merge Cond: (oi.product_id = p.product_id)
-> Index Only Scan using idx_order_items_product_id_quantity on order_items oi (cost=0.56..909371.36 rows=34995920 width=8) (actual time=0.012..1114.922 rows=34993781 loops=1)
Heap Fetches: 2590
-> Materialize (cost=166230.35..170735.35 rows=901000 width=41) (actual time=72.263..140.282 rows=3852655 loops=1)
-> Sort (cost=166230.35..168482.85 rows=901000 width=41) (actual time=72.261..72.971 rows=22047 loops=1)
Sort Key: p.product_id
Sort Method: quicksort Memory: 2075kB
-> Hash Join (cost=8372.00..21683.19 rows=901000 width=41) (actual time=55.263..69.686 rows=22047 loops=1)
Hash Cond: (s.category_id = p.category_id)
-> CTE Scan on subcategories s (cost=0.00..18.02 rows=901 width=4) (actual time=0.023..0.092 rows=11 loops=1)
-> Hash (cost=4114.00..4114.00 rows=200000 width=45) (actual time=54.774..54.774 rows=200000 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 4399kB
-> Seq Scan on products p (cost=0.00..4114.00 rows=200000 width=45) (actual time=0.022..21.435 rows=200000 loops=1)
План выполнения запроса с рекурсивным cte в MySQL:
-> Limit: 10 row(s) (actual time=1833..1833 rows=10 loops=1)
-> Sort: total_quantity_sold DESC, limit input to 10 row(s) per chunk (actual time=1833..1833 rows=10 loops=1)
-> Table scan on <temporary> (actual time=1828..1831 rows=22047 loops=1)
-> Aggregate using temporary table (actual time=1828..1828 rows=22047 loops=1)
-> Nested loop inner join (cost=716460 rows=6.52e+6) (actual time=0.946..696 rows=3.85e+6 loops=1)
-> Nested loop inner join (cost=13147 rows=37551) (actual time=0.834..20.1 rows=22047 loops=1)
-> Filter: (s.category_id is not null) (cost=4.64 rows=19) (actual time=0.0706..0.0907 rows=11 loops=1)
-> Table scan on s (cost=10.6..13.2 rows=19.2) (actual time=0.0699..0.0825 rows=11 loops=1)
-> Materialize recursive CTE subcategories (cost=10.5..10.5 rows=19.2) (actual time=0.069..0.069 rows=11 loops=1)
-> Rows fetched before execution (cost=0..0 rows=1) (actual time=83e-6..125e-6 rows=1 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=6.04 rows=18.2) (actual time=0.0147..0.0239 rows=5 loops=2)
-> Filter: (s.category_id is not null) (cost=2.73 rows=2) (actual time=0.00123..0.00179 rows=5.5 loops=2)
-> Scan new records on s (cost=2.73 rows=2) (actual time=625e-6..938e-6 rows=5.5 loops=2)
-> Index lookup on c using idx_parent_category (parent_category_id = s.category_id) (cost=1.2 rows=9.09) (actual time=0.00311..0.00381 rows=0.909 loops=11)
-> Index lookup on p using idx_products_category_id (category_id = s.category_id) (cost=504 rows=1976) (actual time=0.155..1.76 rows=2004 loops=11)
-> Covering index lookup on oi using idx_order_items_product_id_quantity (product_id = p.product_id) (cost=1.38 rows=174) (actual time=0.0194..0.0255 rows=175 loops=22047)
План выполнения запроса с рекурсивным cte в SQLite:
2 0 0 CO-ROUTINE subcategories
5 2 0 SETUP
7 5 33 SEARCH categories USING INTEGER PRIMARY KEY (rowid=?)
22 2 0 RECURSIVE STEP
25 22 216 SCAN s
26 22 61 SEARCH c USING INDEX idx_categories_parent (parent_category_id=?)
49 0 216 SCAN p
51 0 55 SEARCH oi USING COVERING INDEX idx_order_items_product_quantity (product_id=?)
57 0 0 BLOOM FILTER ON s (category_id=?)
68 0 54 SEARCH s USING AUTOMATIC COVERING INDEX (category_id=?)
112 0 0 USE TEMP B-TREE FOR ORDER BYКомментарии (7)
Yuvitch
05.11.2025 20:29Ну вот... Вот так должны выглядеть статьи на Хабре.
Все по-научному: Объект, Цель, Постановка задачи, Методика, Материалы и Компоненты, Выполненные процедуры, Анализ, Заключение. Все основное -- в тексте, все детали -- в приложениях.
Не удивлюсь, если это материал к некоторой Конференции и/или Диссертации.
Shado_vi
05.11.2025 20:29в заголовке "при выполнении различных запросов" - по факту же только select запросы и только в НЕ фрагментированой таблице. в "вакуумах" условиях? (навевает на мысли об пропаганде/маркетинге pg, в этих условиях его лидерство понятно и так)
есть более качественные и глубокие статьи на хабре про планировщики.
pg_expecto
05.11.2025 20:29все СУБД были использованы с настройками по умолчанию
На эти грабли наступают все , кто начинает делать как бы бенчмарки разных СУБД.
что релевантно для большинства типовых сценариев развертывания.
Для PostgreSQL наоборот абсолютно нерелевантно.
Но, тем не менее , с академической точкой зрения - да интересно. Спасибо, в закладках.
А теперь , вопрос по существу
Каждый запрос выполнялся в каждой СУБД последовательно 10 раз.
Почему вы делайте выводы на основании столь малой выборки ?
Очень важное замечание по методологии
После сбора сырых данных для каждого запроса и каждой СУБД вычислялось среднее значение времени выполнения.
Почему делаются выводы о эффективности сложного алгоритма(планировщик это сложный алгоритм) на основании оценки не обладающей свойством робастности (устойчивости к выбросам) ?
Akina
05.11.2025 20:29На эти грабли наступают все , кто начинает делать как бы бенчмарки разных СУБД.
Ой, да настройки по умолчанию - вообще БСК. Вон у MySQL их три разных - для разработчика, выделенного сервера и невыделенного сервера. И какие из них, спрашивается, автор посчитал "ну совсем по умолчанию" - поди угадай.
Akina
Даже на первый взгляд - совершенно непонятно, зачем потребовался python, что мешало сделать всё на чистом SQL. Особенно генерацию тестовых данных - setseed() в PostgreSQL или rand(N) в MySQL позволяют любому читателю гарантированно воспроизвести одни и те же данные, причём именно те же, что использованы автором. А в SQLite их можно перегнать через CSV или linked server.
astentx
Скорее всего потому, что это курсовая/дипломная работа, судя по структуре и
nir_7_semestr. Часто наблюдаемое явление: изпитона по простому импорту CSVпушки по воробьям. В целом, если бы модели были объявлены через классы orm и менялся только энджин в алхимии, прилагался код-запускатор запросов и измеритель времени выполнения, сбрасыватель кешей/буферов и т.п (короче говоря, готовая настройка тестов), можно было бы сказать, что для простоты автоматизации и измерений. Но тут он просто потому что. Учитывая ещё то, что файлы локальные, и все названные СУБД умеют импортировать CSV нативной, без внешнего pandas.