
СОРТИРОВКА
~~~~~~~~~~

Создадим БД и таблицу со строками, которые будем сортировать.

        => 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
