
Хэш-соединение
~~~~~~~~~~~~~~


=> create database db18;
CREATE DATABASE

=> \c db18
You are now connected to database "db18" as user "postgres".

=> create table b(id integer, s text);
CREATE TABLE

=> insert into b select s.id/10, repeat('a',100) from generate_series(0,999999) s(id);
INSERT 0 1000000

=> vacuum analyze b;
VACUUM

Увеличим work_mem и выполним соединение:

=> set work_mem = '128MB';
SET

=> explain analyze
=> with a(id,s) as (
=>   select s.id, repeat('a',100) from generate_series(0,99999) s(id)
=> )
=> select * from a join b on a.id = b.id;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=55291.00..71795.58 rows=10208 width=141) (actual time=669.752..1131.370 rows=1000000 loops=1)
   Hash Cond: (a.id = b.id)
   CTE a
     ->  Function Scan on generate_series s  (cost=0.00..10.00 rows=1000 width=4) (actual time=8.920..36.847 rows=100000 loops=1)
   ->  CTE Scan on a  (cost=0.00..20.00 rows=1000 width=36) (actual time=8.929..106.009 rows=100000 loops=1)
   ->  Hash  (cost=26667.00..26667.00 rows=1000000 width=105) (actual time=659.594..659.594 rows=1000000 loops=1)
         Buckets: 1048576  Batches: 1  Memory Usage: 126167kB
         ->  Seq Scan on b  (cost=0.00..26667.00 rows=1000000 width=105) (actual time=0.030..239.250 rows=1000000 loops=1)
 Planning time: 0.229 ms
 Execution time: 1313.618 ms
(10 rows)


Из-за того, что число строк в CTE неизвестно, планировщик решает строить хэш-таблицу не по той выборке.
При наличии корректной статистики расход памяти был 14403kB.


Битовая карта
~~~~~~~~~~~~~


=> create table bm(n float);
CREATE TABLE

=> insert into bm select random() from generate_series(1,1000000);
INSERT 0 1000000

=> create index on bm(n);
CREATE INDEX

Запретим полное сканирование, чтобы доступ к таблице был с помощью индекса:

=> set enable_seqscan = off;
SET

=> explain analyze select * from bm;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bm  (cost=16230.42..30655.42 rows=1000000 width=8) (actual time=99.725..328.019 rows=1000000 loops=1)
   Heap Blocks: exact=4425
   ->  Bitmap Index Scan on bm_n_idx  (cost=0.00..15980.42 rows=1000000 width=0) (actual time=98.341..98.341 rows=1000000 loops=1)
 Planning time: 0.102 ms
 Execution time: 495.552 ms
(5 rows)


Строка "Heap Blocks: exact=4425" показывает, что все 4425 страниц были охвачены битовой картой без потери точности.

Уменьшим work_mem:

=> set work_mem = '128kB';
SET

=> explain analyze select * from bm;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bm  (cost=16230.42..30655.42 rows=1000000 width=8) (actual time=86.681..304.139 rows=1000000 loops=1)
   Heap Blocks: exact=1323 lossy=3102
   ->  Bitmap Index Scan on bm_n_idx  (cost=0.00..15980.42 rows=1000000 width=0) (actual time=86.471..86.471 rows=1000000 loops=1)
 Planning time: 0.042 ms
 Execution time: 467.624 ms
(5 rows)


Теперь точная битовая карта не поместилась в памяти. Примерно четверть страниц по-прежнему охвачены с точностью до строк, а еще три четверти - с потерей точности.

=> set enable_seqscan = on;
SET



Сортировка с ограничением
~~~~~~~~~~~~~~~~~~~~~~~~~


=> create table sortlimit(n float);
CREATE TABLE

=> insert into sortlimit select random() from generate_series(1,1000000);
INSERT 0 1000000

=> explain analyze select * from sortlimit order by n limit 10;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=36036.22..36036.25 rows=10 width=8) (actual time=473.868..473.874 rows=10 loops=1)
   ->  Sort  (cost=36036.22..38536.35 rows=1000050 width=8) (actual time=473.866..473.867 rows=10 loops=1)
         Sort Key: n
         Sort Method: top-N heapsort  Memory: 17kB
         ->  Seq Scan on sortlimit  (cost=0.00..14425.50 rows=1000050 width=8) (actual time=0.014..227.568 rows=1000000 loops=1)
 Planning time: 0.079 ms
 Execution time: 473.892 ms
(7 rows)


Здесь используется метод сортировки top-N heapsort - оптимизация достигается за счет того, что проще несколько раз найти наибольшее значение, чем полностью отсортировать все строки.


Использование временных файлов
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


=> set log_temp_files = 0;
SET

Функция generate_series:

=> create table rnd(n integer);
CREATE TABLE

=> insert into rnd(n) select (random()*2)::integer from generate_series(1,1000000);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.2", size 14000000
STATEMENT:  insert into rnd(n) select (random()*2)::integer from generate_series(1,1000000);
INSERT 0 1000000

Для выполнения потребовалось 14000000 байт (14 байт на строку).

Использование CTE:

=> explain analyze with r as (select * from rnd) select * from r;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.3", size 14000000
STATEMENT:  explain analyze with r as (select * from rnd) select * from r;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 CTE Scan on r  (cost=13923.10..33925.30 rows=1000110 width=4) (actual time=0.016..741.553 rows=1000000 loops=1)
   CTE r
     ->  Seq Scan on rnd  (cost=0.00..13923.10 rows=1000110 width=4) (actual time=0.014..233.778 rows=1000000 loops=1)
 Planning time: 0.056 ms
 Execution time: 916.855 ms
(5 rows)


Потребовался один временный файл для материализации CTE.

Оконная функция:

=> explain analyze select count(*) over (partition by n) from rnd;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.5", size 3503220
STATEMENT:  explain analyze select count(*) over (partition by n) from rnd;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.6", size 7002030
STATEMENT:  explain analyze select count(*) over (partition by n) from rnd;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.7", size 3494750
STATEMENT:  explain analyze select count(*) over (partition by n) from rnd;
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.4", size 14016512
STATEMENT:  explain analyze select count(*) over (partition by n) from rnd;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=149492.20..166994.12 rows=1000110 width=4) (actual time=1343.955..2263.232 rows=1000000 loops=1)
   ->  Sort  (cost=149492.20..151992.47 rows=1000110 width=4) (actual time=1121.228..1453.993 rows=1000000 loops=1)
         Sort Key: n
         Sort Method: external merge  Disk: 13688kB
         ->  Seq Scan on rnd  (cost=0.00..13923.10 rows=1000110 width=4) (actual time=0.021..228.541 rows=1000000 loops=1)
 Planning time: 0.042 ms
 Execution time: 2455.591 ms
(7 rows)


Здесь оконная функция выполнила группировку (partition by) с помощью сортировки, так что потребовалось несколько временных файлов.


Создание индекса
~~~~~~~~~~~~~~~~


=> set maintenance_work_mem = '1MB';
SET

=> \timing on
Timing is on.

=> create index rnd_n_idx on rnd(n);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25218.8", size 15998976
STATEMENT:  create index rnd_n_idx on rnd(n);
CREATE INDEX
Time: 1267,412 ms

=> \timing off
Timing is off.

=> drop index rnd_n_idx;
DROP INDEX

=> set maintenance_work_mem = '128MB';
SET

=> \timing on
Timing is on.

=> create index rnd_n_idx on rnd(n);
CREATE INDEX
Time: 774,179 ms

=> \timing off
Timing is off.

=> \q
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
waiting for server to shut down....LOG:  database system is shut down
 done
server stopped
waiting for server to start.... done
server started
