Оптимизация запросов. Основы EXPLAIN в PostgreSQL (часть 3)

Подолжаю публиковать авторскую переработку Understanding EXPLAIN от Guillaume Lelarge.
Ещё раз обращу внимание, что часть информации для краткости опущено, так что настоятельно рекомендую ознакомиться с оригиналом.

Предыдущие части:

Часть 1
Часть 2

ORDER BY

DROP INDEX foo_c1_idx;
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=571.591..651.524 rows=1000010 loops=1)
Sort Key: c1
Sort Method: external merge Disk: 45952kB
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.007..62.041 rows=1000010 loops=1)
Total runtime: 690.984 ms
(5 rows)

Сначала производится Seq Scan таблицы foo. Затем сортировка Sort. В выводе команды EXPLAIN знак -> указывает на иерархию действий (node). Чем раньше выполняется действие, тем с большим отступом оно отображается.
Sort Key — условие сортировки.
Sort Method: external merge Disk — при сортировке используется временный файл на диске объёмом 45952kB.

Прошу разбирающихся в теме разъяснить различия между external merge и external sort.

Проверим с опцией BUFFERS:

EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=568.412..652.308 rows=1000010 loops=1)
Sort Key: c1
Sort Method: external merge Disk: 45952kB
Buffers: shared hit=8334, temp read=5745 written=5745
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.010..68.203 rows=1000010 loops=1)
Buffers: shared hit=8334
Total runtime: 698.032 ms
(7 rows)

Действительно, temp read=5745 written=5745 — во временный файл было записано и прочитано 5745 блоков по 8Kb = 45960Kb. Операции с 8334 блоками были произведены в кэше.

Операции с файловой системой более медленные, чем операции в оперативной памяти.
Попробуем увеличить объём используемой памяти work_mem:

SET work_mem TO '200MB';
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=265.301..296.777 rows=1000010 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 102702kB
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.006..57.836 rows=1000010 loops=1)
Total runtime: 328.746 ms
(5 rows)

Sort Method: quicksort Memory: 102702kB — сортировка целиком проведена в оперативной памяти.

Индекс:

CREATE INDEX ON foo(c1);
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.023..126.076 rows=1000010 loops=1)
Total runtime: 153.452 ms
(2 rows)

Из действий осталось только Index Scan, что заметно отразилось на скорости выполнения запроса.

LIMIT

Удалим ранее созданный индекс.

DROP INDEX foo_c2_idx1;
EXPLAIN (ANALYZE,BUFFERS)
  SELECT * FROM foo WHERE c2 LIKE 'ab%';

QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.033..94.757 rows=3824 loops=1)
Filter: (c2 ~~ 'ab%'::text)
Rows Removed by Filter: 996186
Buffers: shared hit=8334
Total runtime: 94.924 ms
(5 rows)

Ожидаемо, используются Seq Scan и Filter.

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM foo WHERE c2 LIKE 'ab%' LIMIT 10;

QUERY PLAN
— Limit (cost=0.00..2083.41 rows=10 width=37) (actual time=0.037..0.607 rows=10 loops=1)
Buffers: shared hit=26
-> Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.031..0.599 rows=10 loops=1)
Filter: (c2 ~~ 'ab%'::text)
Rows Removed by Filter: 3053
Buffers: shared hit=26
Total runtime: 0.628 ms
(7 rows)

Производится сканирование Seq Scan строк таблицы и сравнение Filter их с условием. Как только наберётся 10 записей, удовлетворяющих условию, сканирование закончится. В нашем случае для того, чтобы получить 10 строк результата пришлось прочитать не всю таблицу, а только 3063 записи, из них 3053 были отвергнуты (Rows Removed by Filter).
То же происходит и при Index Scan.

JOIN

Создадим новую таблицу, соберём для неё статистику.

CREATE TABLE bar (c1 integer, c2 boolean);
INSERT INTO bar
  SELECT i, i%2=1
  FROM generate_series(1, 500000) AS i;
ANALYZE bar;

Запрос по двум таблицам

EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Hash Join (cost=13463.00..49297.22 rows=500000 width=42) (actual time=87.441..907.555 rows=500010 loops=1)
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..67.951 rows=1000010 loops=1)
-> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=87.352..87.352 rows=500000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 18067kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.233 rows=500000 loops=1)
Total runtime: 920.967 ms
(7 rows)

Сначала просматривается (Seq Scan) таблица bar. Для каждой её строки вычисляется хэш (Hash).
Затем сканируется Seq Scan таблица foo, и для каждой строки этой таблицы вычисляется хэш, который сравнивается (Hash Join) с хэшем таблицы bar по условию Hash Cond. Если соответствие найдено, выводится результирующая строка, иначе строка будет пропущена.
Использовано 18067kB в памяти для размещения хэшей таблицы bar.

Добавим индекс

CREATE INDEX ON bar(c1);
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Join (cost=1.69..39879.71 rows=500000 width=42) (actual time=0.037..263.357 rows=500010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.019..58.920 rows=500011 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..71.719 rows=500010 loops=1)
Total runtime: 283.549 ms
(5 rows)

Hash уже не используется. Merge Join и Index Scan по индексам обеих таблиц дают впечатляющий прирост производительности.

LEFT JOIN:

EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Hash Left Join (cost=13463.00..49297.22 rows=1000010 width=42) (actual time=82.682..926.331 rows=1000010 loops=1)
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.004..68.763 rows=1000010 loops=1)
-> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=82.625..82.625 rows=500000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 18067kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.003..31.890 rows=500000 loops=1)
Total runtime: 950.625 ms
(7 rows)

Seq Scan?
Посмотрим, какие результаты будут, если запретить Seq Scan.

SET enable_seqscan TO off; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.024..353.819 rows=1000010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.011..112.095 rows=1000010 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..63.125 rows=500010 loops=1)
Total runtime: 378.603 ms
(5 rows)

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

SET work_mem TO '15MB';
SET enable_seqscan TO ON; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.014..376.395 rows=1000010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx1 on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.005..124.698 rows=1000010 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.006..66.813 rows=500010 loops=1)
Total runtime: 401.990 ms
(5 rows)

А как будет выглядеть вывод EXPLAIN при запрещённом Index Scan?

SET work_mem TO '15MB';
SET enable_indexscan TO off; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Hash Left Join (cost=15417.00..63831.18 rows=1000010 width=42) (actual time=93.440..712.056 rows=1000010 loops=1)
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..65.901 rows=1000010 loops=1)
-> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=93.308..93.308 rows=500000 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 9045kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.718 rows=500000 loops=1)
Total runtime: 736.726 ms
(7 rows)

cost явно увеличился. Причина в Batches: 2. Весь хэш не поместился в память, его пришлось разбить на 2 пакета по 9045kB.

Здесь опять прошу помощи гуру. Объясните, почему при LEFT JOIN и достаточном work_mem, использование Merge Left Join более затратно, чем Hash Left Join?

На этом сегодня остановлюсь.

UPD.
Много полезного про индексы PostgreSQL рассказали Олег Бартунов и Александр Коротков.

Внесу сюда ссылку на свежую статьи от PostgreSQL Индексы в PostgreSQL, часть 2, часть 3. Там многое разъяснено.