lesson ── postgres-labs ── ~24 мин ── 5 шагов
Один и тот же JOIN выполним всеми тремя алгоритмами: hash по умолчанию,
nested loop через тумблеры и индекс, и посмотрим, как нехватка work_mem
заставляет hash проливаться на диск. Запусти psql во вкладке client.
интерактивный sandbox
Поднимется контейнер postgreslab/postgres-base с PostgreSQL 17 и psql. В браузере откроется терминал, база lab уже настроена. Каждый шаг проверяется автоматически. Сеть air-gapped, наружу контейнер не ходит.
stack ── PostgreSQL 17 · psql · 1 GB RAM · air-gapped · самоуничтожается через 45 мин простоя
CREATE TABLE a AS SELECT g AS id FROM generate_series(1, 100000) g;
CREATE TABLE b AS SELECT g AS id FROM generate_series(1, 100000) g;
ANALYZE a; ANALYZE b;
Без индексов и на больших входах планировщик предпочтёт hash join.
✓ Обе таблицы готовы.
EXPLAIN SELECT * FROM a JOIN b USING(id);
Большие входы, соединение по равенству, индексов нет - hash join.
Hash join читает оба входа один раз, строит хеш по меньшему.
✓ Hash Join, как и ожидалось для больших входов.
Тумблеры enable_* штрафуют алгоритм. Выполни в одной сессии:
SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN SELECT * FROM a JOIN b USING(id);
Останется Nested Loop. Это только план (без ANALYZE), выполнять его на больших таблицах не нужно.
enable_* - инструмент для учёбы и диагностики, не для прода.
✓ С выключенными hash и merge остался Nested Loop.
CREATE INDEX ON b(id);
ANALYZE b;
EXPLAIN SELECT * FROM a JOIN b USING(id) WHERE a.id < 5;
Мало внешних строк и индекс на b - планировщик сам выбирает Nested Loop с Index Scan по b.
Nested loop хорош, когда внешний вход мал, а на внутренней есть индекс.
✓ Маленький внешний вход + индекс - Nested Loop по выбору планировщика.
SET work_mem = '64kB';
EXPLAIN ANALYZE SELECT * FROM a JOIN b USING(id);
У узла Hash смотри Batches. С крошечным work_mem хеш не влезает в память и делится на много пакетов (Batches > 1) с проливом на диск.
Batches: 1 - всё в памяти; больше - был пролив на диск.
✓ Batches > 1 - hash join пролился на диск из-за малого work_mem.
Три алгоритма соединения: hash (большие входы по равенству, нужен work_mem), nested loop (мал внешний вход + индекс внутри), merge (отсортированные входы). Тумблеры enable_* показывают каждый. Нехватка work_mem заставляет hash делиться на batches и проливаться на диск.
команды
EXPLAIN SELECT * FROM a JOIN b USING(id);посмотреть алгоритм соединенияSET enable_hashjoin = off;увести план на другой алгоритм (для учёбы)SET work_mem = '64MB';больше памяти - меньше пролива hash на дискконцепции