СОРТИРОВКА ~~~~~~~~~~ Создадим БД и таблицу со строками, которые будем сортировать. => create database db18; CREATE DATABASE => \c db18 You are now connected to database "db18" as user "postgres". => create table sortme(n float); CREATE TABLE => insert into sortme select random() from generate_series(1,1000000); INSERT 0 1000000 => vacuum analyze sortme; VACUUM ....................................................................... Значение work_mem по умолчанию: => show work_mem; work_mem ---------- 4MB (1 row) Увеличим его и включим сообщения о временных файлах. => set work_mem = '128MB'; SET => set log_temp_files = 0; SET ....................................................................... Выполним сортировку и проверим результат explain analyze: => explain analyze select * from sortme order by n; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Sort (cost=114082.84..116582.84 rows=1000000 width=8) (actual time=1349.394..1660.563 rows=1000000 loops=1) Sort Key: n Sort Method: quicksort Memory: 55447kB -> Seq Scan on sortme (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.004..207.508 rows=1000000 loops=1) Planning time: 0.067 ms Execution time: 1846.291 ms (6 rows) В данном случае строки поместились в память (Sort Method: quicksort). ....................................................................... А теперь уменьшим work_mem: => set work_mem = '128kB'; SET ....................................................................... Снова выполняем сортировку: => explain analyze select * from sortme order by n; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Sort (cost=155106.34..157606.34 rows=1000000 width=8) (actual time=1846.277..2250.323 rows=1000000 loops=1) Sort Key: n Sort Method: external merge Disk: 17600kB -> Seq Scan on sortme (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.007..213.061 rows=1000000 loops=1) Planning time: 0.036 ms Execution time: 2427.506 ms (6 rows) Теперь строки не помещаются в память и используется внешняя сортировка (Sort Method: external merge). ....................................................................... Также в журнале сообщений мы видим запись о временном файле: tail -n 2 /home/postgres/logfile LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.1", size 18022400 STATEMENT: explain analyze select * from sortme order by n; Запись появляется, когда файл освобождается, поэтому его размер известен. ....................................................................... СОЕДИНЕНИЕ ХЭШИРОВАНИЕМ ~~~~~~~~~~~~~~~~~~~~~~~ Посмотрим теперь на соединение хэшированием. Для этого нам потребуются две таблицы. => create table a(id integer, s text) with (autovacuum_enabled=off); CREATE TABLE => create table b(id integer, s text) with (autovacuum_enabled=off); CREATE TABLE Мы отключили автоочистку для этих таблиц, чтобы иметь возможность управлять моментом сбора статистики. ....................................................................... Добавим значений: => insert into a select s.id, repeat('a',100) from generate_series(0,99999) s(id); INSERT 0 100000 => insert into b select s.id/10, repeat('a',100) from generate_series(0,999999) s(id); INSERT 0 1000000 => vacuum analyze a; VACUUM => vacuum analyze b; VACUUM ....................................................................... Увеличим work_mem: => set work_mem = '128MB'; SET ....................................................................... Выполним соединение: => explain analyze select * from a join b on a.id = b.id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Hash Join (cost=3917.00..44334.00 rows=1000000 width=210) (actual time=79.458..905.312 rows=1000000 loops=1) Hash Cond: (b.id = a.id) -> Seq Scan on b (cost=0.00..26667.00 rows=1000000 width=105) (actual time=0.007..219.401 rows=1000000 loops=1) -> Hash (cost=2667.00..2667.00 rows=100000 width=105) (actual time=79.230..79.230 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 12720kB -> Seq Scan on a (cost=0.00..2667.00 rows=100000 width=105) (actual time=0.008..34.817 rows=100000 loops=1) Planning time: 0.138 ms Execution time: 1068.814 ms (8 rows) Памяти хватило, использовался один пакет (Batches: 1). Параметр Buckets показывает число корзин в хэш-таблице, а Memory Usage - использованную оперативную память. Обратите внимание, что хэш-таблица строилась по меньшему набору строк. ....................................................................... Теперь уменьшим work_mem: => set work_mem = '128kB'; SET ....................................................................... Снова выполним соединение: => explain analyze select * from a join b on a.id = b.id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Hash Join (cost=5529.00..79786.00 rows=1000000 width=210) (actual time=74.169..1487.911 rows=1000000 loops=1) Hash Cond: (b.id = a.id) -> Seq Scan on b (cost=0.00..26667.00 rows=1000000 width=105) (actual time=0.028..288.669 rows=1000000 loops=1) -> Hash (cost=2667.00..2667.00 rows=100000 width=105) (actual time=73.508..73.508 rows=100000 loops=1) Buckets: 1024 Batches: 128 Memory Usage: 106kB -> Seq Scan on a (cost=0.00..2667.00 rows=100000 width=105) (actual time=0.002..21.504 rows=100000 loops=1) Planning time: 0.089 ms Execution time: 1668.625 ms (8 rows) Теперь число корзин хэш-таблицы уменьшилось и для соединения пришлось использовать 128 пакетов. ....................................................................... В журнале сообщений обнаруживаем записи о временных файлах (показаны только несколько последних): tail -n 10 /home/postgres/logfile LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.218", size 1003090 STATEMENT: explain analyze select * from a join b on a.id = b.id; LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.98", size 97526 STATEMENT: explain analyze select * from a join b on a.id = b.id; LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.225", size 975260 STATEMENT: explain analyze select * from a join b on a.id = b.id; LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.124", size 87725 STATEMENT: explain analyze select * from a join b on a.id = b.id; LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp16368.251", size 877250 STATEMENT: explain analyze select * from a join b on a.id = b.id; По размеру видно, что часть файлов относится к первой таблице (они меньше), а часть - ко второй (они больше). ....................................................................... А теперь обманем планировщик: сделаем вид, что во второй таблице мало строк. Для этого удалим почти все строки, соберем статистику, а затем вставим новые строки. Поскольку размер файла не изменится значительно, планировщик не заподозрит подвох: => delete from b where random() < 0.99; DELETE 990061 => vacuum analyze b; VACUUM => insert into b select s.id/10, repeat('a',100) from generate_series(0,999999) s(id); INSERT 0 1000000 ....................................................................... Выполняем запрос: => explain analyze select * from a join b on a.id = b.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=17220.85..23999.24 rows=10038 width=210) (actual time=981.675..2732.130 rows=1009939 loops=1) Hash Cond: (a.id = b.id) -> Seq Scan on a (cost=0.00..2667.00 rows=100000 width=105) (actual time=0.012..69.948 rows=100000 loops=1) -> Hash (cost=16933.38..16933.38 rows=10038 width=105) (actual time=973.985..973.985 rows=1009939 loops=1) Buckets: 1024 (originally 1024) Batches: 2048 (originally 16) Memory Usage: 125kB -> Seq Scan on b (cost=0.00..16933.38 rows=10038 width=105) (actual time=0.005..342.407 rows=1009939 loops=1) Planning time: 0.154 ms Execution time: 2913.618 ms (8 rows) Планировщик начал с большой таблицы, считая, что она маленькая. Ему пришлось динамически увеличивать число пакетов, что негативно сказалась на скорости выполнения запроса. ....................................................................... Конец демонстрации. ....................................................................... => \q