[Перевод] Оптимизация запросов SQL Server V/S PostgreSQL: есть куда расти?
Много лет мы изучали Oracle, чтобы сделать СУБД PostgreSQL более привлекательной для миграции. Изучив позитивный опыт SQL profile и SQL plan baseline мы реализовали похожие инструменты в PostgreSQL в виде расширений AQO и sr_plan. Судя по отзывам, в некоторых аспектах, например в автоматической реоптимизации, решения PostgreSQL даже превзошли аналоги Oracle.
Несмотря на насыщенность СУБД Oracle различными дополнениями и оптимизациями, переход с неё на PostgreSQL оказывается довольно плавным, и большинство миграций, с точки зрения производительности, проходит без проблем. Для упрощения процесса мы даже разработали расширение клиентских переменных сессий. Хотя многие подобные решения доступны только по корпоративной лицензии, популярный код в мире PostgreSQL в итоге часто попадает в ядро.
Однако при тестировании миграции с SQL Server на PostgreSQL встречаются отзывы о «значительном замедлении запросов». Проблемные запросы разнообразны, а среда и объемы баз данных варьируются от гигабайт до десятков терабайт. Известен случай, когда план запроса, построенный Postgres, был настолько неэффективен, что его выполнение не завершилось даже после двух недель загрузки сервера, что резко контрастировало со временем выполнения того же запроса в SQL Server — всего 20 мс. Такие отличия наводят на мысль о некоем технологическом превосходстве данной СУБД. Это было интересно расследовать и побудило меня глубже проанализировать проблемные случаи. Давайте посмотрим, что они собой представляют.
Временные таблицы и параллельные воркеры
Первый случай обнаружился в момент миграции с SQL Server, когда один из запросов ощутимо (в 7–9 раз) замедлился на казалось бы небольших таблицах. Тем более удивительно, что запрос представляет собой всего один JOIN с группировкой. Кейс достаточно сложен в синтетическом воспроизведении, поскольку вызван функциональными зависимостями в данных. Поэтому в данном примере придется довольствоваться схемой запроса и его плана:
SELECT sum(t1.x * t2.count) FROM t1,t2
WHERE t1.x3 = t2.x3 AND t1.x4 = t2.x4 GROUP BY t1.x1,t1.x2,t1.x3,t1.x4;
HashAggregate (width=1002) (actual time=4000s rows=2.1E3)
Group Key: t1.x1, t1.x2, t1.x3, t1.x4
-> Nested Loop (width=662) (actual time=500s rows=1.5E9)
-> Seq Scan on t1 (actual time=0.3s rows=2.4E5)
-> Memoize (actual time=0.003..0.260 rows=6100 loops=240000)
Cache Key: t1.x1, t1.x2, t1.x3, t1.x4
Cache Mode: logical
Hits: 200000 Misses: 21000
-> Index Scan using t2_idx on t2 (actual time=0.3E-4 rows=11)
Index Cond: t2.x1 = t1.x1 AND t2.x2 = t1.x2
Filter: t2.x3 = t1.x3 AND t2.x4 = t1.x4
Rows Removed by Filter: 0Здесь t1 и t2 — две временные таблицы, каждая из которых содержит около 200 тыс. строк (кортежей).
Имеем один JOIN между двумя не очень большими таблицами и агрегацию cверху. Можно заметить, что сам JOIN генерирует огромное количество кортежей — более одного миллиарда и работает около 500с. Но что действительно интересно, так это тривиальная операция GROUP-BY, которая занимает около часа! Выглядит странно, особенно с учётом того, что SQL Server выполняет аналогичный запрос всего за 300 секунд.
Чтобы понять причину, посмотрим на схему плана запроса в SQL Server:
HashAggregate (parallel 8 streams)
Hash Join
Index Scan t1
Index Scan t2Планы SQL Server и PostgreSQL отличаются. Во-первых, HashJoin кажется более удачным выбором, но замена NestedLoop на HashJoin дает лишь незначительное ускорение (300 секунд вместо 500) и не сильно влияет на общее время выполнения. Второе отличие — параллельное выполнение. Почему это так важно? Чтобы понять это, посмотрим на flamegraph:
Flamegraph, построенный по результатам профайлинга бэкенда, выполнявшего запрос
На нём видно, что Postgres тратит много времени на создание хеш-значений — процедуру hashtext() и сравнение кортежей — процедуру texteq().
Запрос группирует миллиард входящих кортежей, сгенерированных операцией JOIN в 21 тысячу групп. Значит, в каждой группе около 70 тыс. кортежей -, а это немалое количество дубликатов! Вторая часть этой загадки кроется в типе столбцов — все четыре столбца текстового типа.
Посмотрим статистику по этим столбцам:
SELECT a.attname,s.stadistinct,s.stanullfrac,s.stawidth
FROM pg_statistic s, pg_attribute a
WHERE
starelid=16395 AND
starelid=attrelid AND
s.staattnum=a.attnum AND
a.attname IN ('x1', 'x2', 'x3', 'x4');
attname | stadistinct | stanullfrac | stawidth
---------------+-------------+---------------+----------
x1 | 7 | 0 | 72
x2 | 3574 | 0 | 72
x3 | 6 | 0.00033333333 | 72
x4 | 3 | 0 | 50Поскольку количество уникальных значений мягко скажем невелико, то почти все сравнения в первом столбце x1 требуют второго сравнения в x2. Более того, каждое значение в столбце — это довольно длинная строка, и чтобы создать хеш или идентифицировать дубликат, executor'у достаточно часто нужно обработать порядка 300 байт. Помним, что хеш-агрегация выполняет как минимум две вычислительные операции для каждого входящего кортежа — генерацию хеша и сравнение строк. Учитывая миллиард входящих кортежей, это может быть огромная работа и причина столь долгого времени для группировки! В отсутствие другого очевидного способа ускорить операцию группировки, моя основная гипотеза относительно впечатляющего времени выполнения SQL Server заключается в использовании параллелизма.
Параллелизм
Согласно документации, SQL Server использует многопоточность для ускорения группировок. Можно предположить, что имплементировать параллельный режим для произвольного (особенно кастомного) агрегата — непростая задача, но тривиальную группировку по хешу несложно реализовать параллельно. В данном примере, когда большая часть строк генерируется уже в оперативной памяти, это выглядит практически идеальным решением.
В PostgreSQL для таких случаев предусмотрены параллельные воркеры. Однако, реализованные как процессы они становятся тяжеловесным инструментом. Обычно PostgreSQL консервативно использует 2–3 процесса для одного запроса, помятуя о соседних бэкендах, которым также требуется процессорное время. При этом параллелится выполнение не одной конкретной ноды плана, а целое поддерево запроса, обязательно включая операторы сканирования. Более того, модель процессов и особенности реализации временных таблиц не позволяют использовать параллельные воркеры в текущем ванильном мастере постгреса.
Чтобы оценить, насколько полезными могут быть параллельные воркеры, сделаем таблицы постоянными и принудительно разрешим большое количество параллельных воркеров. Для этого также нужно не забыть уменьшить стоимость инициализации параллельного выполнения. Также следует уменьшить параметр min_parallel_table_scan_size и помнить про параметр max_parallel_workers в GUC. Финальная конфигурация выглядит так:
SET max_parallel_workers = 32; -- Устанавливаем перед стартом инстанса
SET max_parallel_workers_per_gather = 16;
SET parallel_setup_cost = 0.001;
SET parallel_tuple_cost = 0.0001;
SET min_parallel_table_scan_size = 0;После прогона теста в PostgreSQL на той же, что и SQL Server аппаратной платформе, мы получаем уже сопоставимые результаты:
Finalize HashAggregate (actual time=416s)
Group Key: t1.x1, t1.x2, t1.x3, t1.x4
-> Gather (actual time=416s)
Workers Launched: 9
-> Partial HashAggregate (actual time=416s)
Group Key: t1.x1, t1.x2, t1.x3, t1.x4
-> Nested Loop (actual time=68s)
-> Parallel Seq Scan on t1 (actual time=0.08s)
-> Index Scan using t2_idx on t2 (actual time=0.04)
Index Cond: t2.x1 = t1.x1 AND t2.x2 = t1.x2
Filter: t2.x3 = t1.x3 AND t2.x4 = t1.x4)
Rows Removed by Filter: 0
Execution Time: 416.5sКак видите, техника параллельных воркеров полезна и в данной задаче имеет решающее значение. Но есть у нее и недостаток — тяжеловесность модели параллельных воркеров в PostgreSQL. Нужно решить проблему видимости временных таблиц и, возможно, устранить недоработки параллельной модели (например, использование подзапросов в фильтрах выборки), которые могут привести к отказу от использования параллельных воркеров в конкретном запросе. Модель потоков, используемая локально в узле группировки, выглядит более гибкой. Возможно, стоит подумать о расширении агрегатных функций, которые бы позволили использовать многопоточную модель. Выглядит неплохо как идея для проекта GSoC в следующем году!
Оценка селективности multi-clause expressions
Этот случай представляет ещё одну частую проблему: JOIN (или фильтр) с множественными условиями. Вот, например, выражение с четырьмя условиями:
t1.x1 = t2.x1 AND t1.x2 = t2.x2 AND t1.x3 = t2.x3 AND t1.x4 = t2.x4PostgreSQL оценивает селективность всего выражения через оценку селективности si каждого подвыражения (clause):
![s_1, s_2, s_3\ and\ s_4, s_i \in (0..1]](https://habrastorage.org/getpro/habr/upload_files/d31/ecd/88e/d31ecd88e674457079e6a71c9a2e8f52.svg)
С помощью таких частных оценок планнер рассчитывает общее количество строк, произведённых JOIN«ом:
В большинстве случаев данная формула недооценивает количество кортежей, полученных в результате соединения. Достаточно редко на практике приходится видеть, что данные в соседних колонках распределены равномерно как «по вертикали» (внутри колонки), так и абсолютно независимо «по горизонтали» — между колонками. Другими словами, планнер PostgreSQL предоставляет хорошую оценку для одного условия соединения, но недооценивает результат, если запрос требует соединения таблиц по множеству условий.
Дабы разбавить сухое повествование, давайте рассмотрим простой пример.
CREATE TABLE t1 (x1 integer, x2 integer,x3 integer,x4 integer);
CREATE TABLE t2 (x1 integer, x2 integer,x3 integer,x4 integer);
INSERT INTO t1 (x1,x2,x3,x4) SELECT value%2,value%10,value%20,value%100
FROM generate_series(1,1000) AS value;
INSERT INTO t2 (x1,x2,x3,x4) SELECT value%2,value%10,value%20,value%100
FROM generate_series(1,1000) AS value;
CREATE INDEX t1_idx ON t1 (x1,x2,x3,x4);
CREATE INDEX t2_idx ON t2 (x1,x2,x3,x4);
VACUUM ANALYZE t1,t2;
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF)
SELECT * FROM t1 WHERE x1=1 AND x2=1 AND x3=1 AND x4=1;
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF)
SELECT * FROM t1 JOIN t2
ON (t1.x1=t2.x1 AND t1.x2=t2.x2 AND t1.x3=t2.x3 AND t1.x4=t2.x4);Здесь мы имеем очевидную неявную функциональную зависимость между всеми колонками в таблице. Попробуем, как отработает оценка селективности для оператора сканирования и JOIN. Запустив этот скрипт на PostgreSQL получим:
-- Простой SELECT:
Index Only Scan using t1_idx on t1 (cost=0.28..4.30 rows=1 width=16)
(actual time=0.067..0.072 rows=10 loops=1)
...
-- JOIN двух таблиц:
Nested Loop (cost=0.29..72.88 rows=25 width=32)
(actual time=0.110..10.203 rows=10000 loops=1)
-> Seq Scan on t1 (cost=0.00..16.00 rows=1000 width=16)
(actual time=0.019..0.225 rows=1000 loops=1)
-> Memoize (cost=0.29..0.33 rows=1 width=16)
(actual time=0.002..0.005 rows=10 loops=1000)
Cache Key: t1.x1, t1.x2, t1.x3, t1.x4
...Видно, что СУБД не имея никакого представления о взаимном распределении значений в соседних колонках оценивает количество результирующих кортежей просто перемножая селективности для каждой из колонок, сильно занижая оценку. В результате оптимизатор сваливается к выбору NestLoop, что является достаточно опасным выбором, пусть и смягченным использованием оператора кэширования промежуточных результатов Memoize. А как с этой задачей справляется SQL Server? Давайте посмотрим на скриншоты планов. Первая картинка для простого SELECT:
А вторая для JOIN:
SQL Server справился и даже слишком идеально. В чём хитрость? — если убрать из примера индексы, то можно заметить, что результат сильно изменится. Это говорит о том, что СУБД собирает статистику по совместному распределению данных в колонках во время построения индекса.
Изучив документацию SQL Server и исследования, выяснилось, что для решения проблемы неравномерного распределения значений эта СУБД собирает множество данных: статистику по индексу, условию WHERE, пользовательские статистики — аналог функции CREATE STATISTICS в Postgres, но с добавлением множества опций. Самые интересные — использование гистограмм для оценки селективности оператора JOIN и условие WHERE в определении статистики, которое позволяет сканировать только ту часть таблицы, которая удовлетворяет предикату. SQL Server поддерживает столько статистических данных о БД, что разработчикам пришлось изобрести нетривиальные методы для их асинхронного (и даже частичного) обновления.
В нашем случае, при построении индексов SQL Server строит EXTENDED STATISTICS (моя гипотеза) по столбцам x1, x2, x3, x4 таблиц t1 и t2. Имея знание о количестве уникальных (distinct) значений и гистограммы по t1(x1, x2, x3, x4) и t2(x1, x2, x3, x4) SQL Server с хорошей точностью оценивает кардинальность JOIN. Расширенная статистика в PostgreSQL сейчас применяется весьма ограниченно, только в фильтрах оператора сканирования, и не может быть использована для оценки кардинальности JOIN. К счастью, сообщество PostgreSQL уже осознало эту проблему и работает над решением прямо сейчас.
Любопытно, что в компании Postgres Professional достаточно давно изобрели нетривиальный способ частично компенсировать существующий недостаток. Если нужно совместно оценить набор выражений вида A.x = N AND A.y = M, то можно просто представить это выражение в другом виде:
для такого выражения можно определить новый композитный тип. Имея такой тип можно стандартными средствами PostgreSQL посчитать и сохранить стандартную статистику по выражению ROW(A.x,A.y) и использовать гистограмму, MCV и знание о количестве distinct-значений по колонке такого типа для определения селективности выражения не прибегая к расширенной статистике! Как это работает можно оценить, посмотрев в оценки, которые предлагает оптимизатор для запросов, манипулируя значением параметра enable_compound_index_stats.
Следующим преимуществом, часто помогающим SQL Server’у строить оптимальные планы является развитая система внутренней параметризации и кэширование значений параметров.
Кэш значений параметров
Чтобы объяснить этот аспект оптимизации, потребуется соорудить достаточно нетривиальный пример соединения трёх больших таблиц:
CREATE TEMP TABLE t1 (x numeric PRIMARY KEY, payload text);
CREATE TEMP TABLE t2 (x numeric, y numeric PRIMARY KEY);
CREATE TEMP TABLE t3 (x numeric, payload text);
INSERT INTO t1 (x, payload)
(SELECT gs, 'long line of text'
FROM generate_series(1,1E5) AS gs);
INSERT INTO t2 (x,y)
(SELECT gs%10, gs FROM generate_series(1,1E6) AS gs);
INSERT INTO t3 (x, payload)
(SELECT -(gs%10)+2, 'long line of text'
FROM generate_series(1,1E5) AS gs);
VACUUM ANALYZE t1,t2,t3;
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF)
SELECT * FROM t3 WHERE x IN (
SELECT y FROM t2 WHERE x IN (
SELECT x FROM t1)
);Для того, чтобы упростить демонстрацию проблемы, просто отключим HashJoin. В итоге в PostgreSQL имеем план запроса:
Nested Loop Semi Join (actual rows=20000 loops=1)
-> Seq Scan on t3 (actual rows=100000 loops=1)
-> Nested Loop (actual rows=0 loops=100000)
-> Index Scan using t2_pkey on t2 (actual rows=0 loops=100000)
Index Cond: (y = t3.x)
-> Index Only Scan using t1_pkey on t1 (actual rows=1 loops=20000)
Index Cond: (x = t2.x)
Heap Fetches: 0
Planning Time: 1.099 ms
Execution Time: 675.855 msЗдесь имеется три большие таблицы. Соединение их было бы достаточно тяжелой операцией, поэтому Postgres прибегает к так называемой внутренней параметризации — это метод, суть которого в том, что NestLoop JOIN не сканирует всю inner-таблицу, а сначала запрашивает кортеж из outer’a и затем выполняет сканирование inner’a по индексу с параметром, уменьшая таким образом количество возвращаемых из таблицы строк. В данном примере, сканирование с параметром выполняется как по таблице t1, так и по целому поддереву, включающему в себя JOIN t1 и t2.
В этом плане запроса есть один большой недостаток. Таблица t3 содержит много дубликатов — всего 10 уникальных значений на 100 тыс. строк. В таком случае совершенно точно дешевле кэшировать результат нижележащего JOIN(t1,t2), поскольку он может вернуть всего 10 различных результатов, а не выполнять его ReScan 100 тыс. раз. Пример достаточно прост, но ведь поддерево могло бы быть и сильно сложнее! Для таких случаев в Postgres присутствует нода Memoize, которая умеет кэшировать значение параметров и результат сканирования query subtree. Однако, как показало исследование, она не работает для SEMI JOIN’ов и не может быть использована непосредственно над оператором JOIN. Более того, всплыл и ещё один более тонкий нюанс: даже устранив вышеуказанные ограничения, мы не сможем добиться того, чтобы Postgres ставил оператор Memoize над индексным сканированием таблицы t1 — ведь при планировании оператора JOIN (t1,t2) количество повторных сканирований таблицы t1 оценивается в 1, поскольку соединение c t3 - JOIN(JOIN(t1,t2),t3), вызывающее большое количество ресканирований, будет выполнено позже — оптимизатор ведь работает по принципу bottom-up!
А теперь давайте посмотрим, как в этой ситуации поведёт себя SQL Server. Аналогичный запрос на языке Transact-SQL будет выглядеть следующим образом:
CREATE TABLE t1 (x numeric PRIMARY KEY, payload text);
CREATE TABLE t2 (x numeric, y numeric PRIMARY KEY);
CREATE TABLE t3 (x numeric, payload text);
INSERT INTO t1 (x, payload)
(SELECT value, 'long line of text'
FROM generate_series(1,100000));
INSERT INTO t2 (x,y)
(SELECT value % 10, value FROM generate_series(1,1000000));
INSERT INTO t3 (x, payload)
(SELECT -(value % 10) + 2, 'long line of text'
FROM generate_series(1,100000));
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
SELECT * FROM t3 WHERE x IN (
SELECT y FROM t2 WHERE x IN (
SELECT t1.x FROM t1)
)
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'), LOOP JOIN);а план запроса можно увидеть на изображении ниже:
В данной ситуации SQL Server справляется за 138 мс — план запроса в общих чертах выглядит также, как и в PostgreSQL, однако результаты подзапроса кэшируются при помощи оператора Row Count Spool — техника кэширования параметров в SQL Server оказывается проработана настолько хорошо, что оптимизатор не только определяет необходимость кэширования параметров и результатов, но и способен вставлять такие кэши как над оператором индексного сканирования таблицы t1, так и над целым поддеревом JOIN (t1,t2). Четырёхкратное ускорение на ровном месте. Достаточно хороший результат, не правда ли?
Но это ещё не всё. Cамые эффектные примеры ускорения запросов в SQL Server, которые мне приходилось наблюдать появляются как результат следующей техники оптимизации.
Внутренняя параметризация плана запроса
В примере выше можно было заметить, что оптимизатор Postgres умеет параметризовать операторы сканирования и джойны. Как оказалось, он не умеет параметризовывать другие ноды плана запроса — например, пробрасывать параметризацию в подзапросы (subquery), что ограничивает пространство возможных планов. Где это может выстрелить? Рассмотрим ещё один пример:
CREATE TABLE t1 (x1 integer, x2 text);
CREATE TABLE t2 (y1 integer, y2 text);
CREATE INDEX t1_idx0 ON t1 (x1);
CREATE INDEX t2_idx0 ON t2 (y1);
INSERT INTO t1 (x1,x2)
SELECT value % 100, 'long line of text number 1'
FROM GENERATE_SERIES(1, 10000) AS value;
INSERT INTO t2 (y1,y2)
SELECT value % 10000, 'long line of text number 2'
FROM GENERATE_SERIES(1, 30000) AS value;
VACUUM ANALYZE t1, t2;Сначала изучим поведение PostgreSQL. Попробуем сджойнить таблицы t1 и t2. При этом в сканирование каждой таблицы добавим группировку.
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF)
SELECT * FROM
(SELECT x1 FROM t1 GROUP BY x1) AS q1(x1)
JOIN
(SELECT y1 FROM t2 GROUP BY y1) AS q2(y1)
ON q2.y1 = q1.x1;
/*
Hash Join (actual rows=100 loops=1)
Hash Cond: (t2.y1 = t1.x1)
-> HashAggregate (actual rows=10000 loops=1)
Group Key: t2.y1
-> Seq Scan on t2 (actual rows=30000 loops=1)
-> Hash (actual rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> HashAggregate (actual rows=100 loops=1)
Group Key: t1.x1
-> Seq Scan on t1 (actual rows=10000 loops=1)
Execution Time: 25.311 ms
*/25 мс, соединение хэшированием — ничего особенного. А теперь в целях наглядной демонстрации форсируем использование NestLoop'a:
SET enable_material = f;
SET enable_hashjoin = f;
SET enable_mergejoin = f;
/*
Nested Loop (actual rows=100 loops=1)
Join Filter: (t1.x1 = t2.y1)
Rows Removed by Join Filter: 490197
-> Group (actual rows=100 loops=1)
Group Key: t1.x1
-> Index Only Scan using t1_idx0 on t1 (actual rows=10000 loops=1)
-> HashAggregate (actual rows=4903 loops=100)
Group Key: t2.y1
-> Seq Scan on t2 (actual rows=30000 loops=1)
Execution Time: 145.355 ms
*/NestLoop увеличил время выполнения запроса в 5 раз, до 145 мс. Тоже ничего особенного, скажете вы — это же вложенные циклы. Он и должен быть медленным на больших таблицах не так ли? — давайте трансформируем запрос в эквивалентную форму, задействуя латеральные ссылки:
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF)
SELECT * FROM
(SELECT x1 FROM t1 GROUP BY x1) AS q1(x1),
LATERAL (SELECT y1 FROM t2 WHERE t2.y1=q1.x1 GROUP BY y1) AS q2(y1);
/*
Nested Loop (actual rows=100 loops=1)
-> HashAggregate (actual rows=100 loops=1)
Group Key: t1.x1
-> Seq Scan on t1 (actual rows=10000 loops=1)
-> Group (actual rows=1 loops=100)
-> Index Only Scan using t2_idx0 on t2 (actual rows=3 loops=100)
Index Cond: (y1 = t1.x1)
Planning Time: 1.418 ms
Execution Time: 7.456 ms
*/Это тоже NestLoop, возвращающий ровно тот же результат. Однако он выполняется существенно быстрее, превосходя в три раза даже HashJoin! Причина здесь в том, что объявив латеральную ссылку мы явно добавили параметр, с помощью которого джойну удаётся таргетированно обращаться к таблице t2 не трогая большую часть строк таблицы t2. А в примере выше группировка в подзапросе блокировала создание параметризованного сканирования, поскольку внутренняя параметризация для операторов кроме JOIN и Scan в PostgreSQL не предусмотрена.
Давайте теперь посмотрим, что на это предложит SQL Server:
Видно, что SQL Server добился примерно того же времени выполнения запроса, что и лучший вариант PostgreSQL с латеральными ссылками. Однако сделал он это за счёт немного другой, хоть и не менее интересной оптимизации, откладывая выполнение оператора группировки в дереве плана на более позднюю стадию. Такой приём даёт возможность выполнить параметризованное сканирование в операторе JOIN. Также, рассчитывая на уменьшение количества кортежей после соединения таблиц оптимизатор пытается минимизировать объём работы по группировке строк.
Однако, это не наш объет исследования. Поэтому, чтобы избежать дополнительных приемов оптимизации, сузим пространство поиска для обоих оптимизаторов, заменив в запросе JOIN на LEFT JOIN:
EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT * FROM
(SELECT x1 FROM t1 GROUP BY x1) AS q1(x1)
LEFT JOIN
(SELECT y1 FROM t2 GROUP BY y1) AS q2(y1)
ON q2.y1 = q1.x1;В Transact-SQL запрос выглядит аналогичным образом — нужно только добавить хинт OPTION (LOOP JOIN), чтобы исключить появление HashJoin. В итоге имеем в PostgreSQL:
Nested Loop Left Join (actual rows=100 loops=1)
Join Filter: (t2.y1 = t1.x1)
Rows Removed by Join Filter: 490197
-> Group (actual rows=100 loops=1)
Group Key: t1.x1
-> Index Only Scan using t1_idx0 on t1 (actual rows=10000 loops=1)
Heap Fetches: 0
-> HashAggregate (actual rows=4903 loops=100)
Group Key: t2.y1
Batches: 1 Memory Usage: 913kB
-> Seq Scan on t2 (actual rows=30000 loops=1)
Planning Time: 0.412 ms
Execution Time: 75.476 msА в SQL Server:
1 мс против 75 мс. Ускорение почти на два порядка! А всё из-за того, что SQL Server позволяет пробрасывать параметризацию в достаточно сложных случаях. Например здесь параметр, используемый NestLoop, проходит через StreamAggregate и позволяет выполнить таргетированный IndexScan.
В итоге, изучив эти (и другие, менее впечатляющие) кейсы, должен сказать, что существует ещё обширный набор техник оптимизации запросов, которые отсутствуют в текущей версии PostgreSQL и даже не обсуждаются в hackers mailing list. Разработчики SQL Server хорошо постарались, чтобы сгладить краевые случаи, такие как перекосы по данным или экстремальное количество дубликатов в данных, сделать выполнение запросов более адаптивным, а время выполнения приемлемым для пользователя.
Естественно, что и PostgreSQL есть чем похвастаться. Например, для каждой агрегатной функции можно определить индивидуальные правила упорядочения поступающих кортежей; оптимизатор имеет свободу в определении порядка следования колонок в условиях GROUP BY или DISTINCT ON и наверняка много чего ещё если хорошенько покопаться…
Однако, сравнительное исследование близких по архитектуре СУБД — это прекрасная возможность найти новые пути для развития кода поскольку имеется готовый образец пусть и не кода, но плана запроса! Технологию можно восстановить или даже спроектировать на новом уровне, пользуясь особенностями целевой СУБД и относительной простотой кодовой базы. А вы встречались со случаями, когда миграция между СУБД давала значительный провал/ускорение за счёт именно новых оптимальных планов запросов? Расскажите в комментариях!
THE END.
29 Декабря 2024, Паттайя, Тайланд.
Habrahabr.ru прочитано 4796 раз
