(В статье использованы примеры и пояснения из книги Nouveaulites de PostgreSQL 10. (с) Dalibo, перевод с французского Игоря Лёвшина, редактор Егор Рогов (оригинал). Примеры проверены, иногда изменены для большей наглядности)

Конечно, мы уже ждем не дождемся появления 11-й версии PostgreSQL. Но уже сейчас ясно, что некоторые довольно радикальные улучшения производительности появились уже в версии 10. Определенно есть смысл разобраться сначала с ними.

Производительность "десятки" улучшилась сразу в нескольких направлениях. В этой статье речь пойдет об ускорении за счет:

• распараллеливания сканирования таблиц и индексов,
• более эффективного агрегирования,
• быстрых переходных таблиц,
• ускорения запросов за счет многоколоночной статистики.

Мы начнем с параллелизма.

Параллелизм в PostgreSQL 10

В версии 9.6 уже работало распараллеливание последовательного чтения таблиц, соединения и агрегации. Это касалось запросов на чтение, но не пишущих запросов. Ни INSERT/UPDATE/DELETE, ни пишущие CTE-запросы (Common Table Expressions, общее табличные выражения), ни обслуживающие операции (CREATE INDEX, VACUUM, ANALYZE) не поддерживали распараллеливание.

Версия 10 дает возможность распараллеливать :

• сканирование индекса (Index Scan и Index Only Scan)
• соединение слиянием (Merge Join)
• сбор результатов с сохранением порядка сортировки (Gather Merge)
• исполнение подготовленных запросов
• исполнение некоррелирующих подзапросов

При соединении слиянием (Merge Join) левая и правая таблицы упорядочиваются и после этого параллельно сравниваются.

Узел плана Gather, появившийся в версии 9.6, собирает результаты всех фоновых процессов в произвольном порядке. Gather Merge применяется, если каждый фоновый процесс возвращает отсортированные результаты. Узел сохраняет порядок.

Чтобы больше узнать о параллелизме, обратитесь к статье Parallel Query v2 Роберта Хааса.

Параметры

Соответственно, в postgresql.config появились параметры:
min_parallel_table_scan_size определяет минимальный объем данных таблицы, выше которого может рассматриваться возможность распараллеливания сканирования.

min_parallel_index_scan_size определяет минимальный объем данных индекса, выше которого может рассматриваться возможность распараллеливания сканирования.

max_parallel_workers определяет максимальное число фоновых процессов, которое СУБД может выделить на обработку параллельных запросов. По умолчанию этот параметр равен 8.

Когда вы увеличиваете или уменьшаете этот параметр, не забудьте рассмотреть и параметр max_parallel_workers_per_gather

max_parallel_workers_per_gather определяет максимальное число параллельных процессов, которые могут быть выделены на один узел плана Gather. По умолчанию параметр равен 2. Значение 0 деактивирует параллелизм запроса.

Подготовка

Создадим таблицу t1 в PostgreSQL 10 :

habr_10=# CREATE TABLE t1 AS
   SELECT row_number() OVER() AS id, generate_series%100 AS c_100,
          generate_series%500 AS c_500 FROM generate_series(1,20000000); 
SELECT 20000000 
habr_10=# ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (id); 
ALTER TABLE 
habr_10=# CREATE INDEX idx_t1 ON t1 (c_100); 
CREATE INDEX

Изменим параметр max_parallel_workers_per_gather :

postgres=# ALTER SYSTEM SET max_parallel_workers_per_gather TO 3;
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Повтором то же с PostgreSQL 9.6.

Parallel Bitmap Heap Scan

В PostgreSQL 9.6 при чтении распараллеливать можно было только последовательное сканирование таблиц (parallel sequential scan), но не индексный доступ. Планировщику оставалось выбирать между распараллеливанием и использованием индекса.

Благодаря тому, что в PostgreSQL 10 доступен parallel bitmap heap scan, процессы сканирования создают структуры данных в памяти, показывающие, которые из страниц данных следует читать. Фоновые процессы после этого смогут читать свои порции страниц параллельно.

habr_9_6=# EXPLAIN ANALYSE VERBOSE SELECT count(*), c_100 FROM t1
    WHERE c_100 <10 GROUP BY c_100;
                                                                 QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=180449.79..180450.79 rows=100 width=12) (actual time=12663.666..12663.667 rows=10 loops=1)
   Output: count(*), c_100
   Group Key: t1.c_100
   ->  Bitmap Heap Scan on public.t1  (cost=37387.68..170463.19 rows=1997321 width=4) (actual time=231.350..12097.624 rows=2000000 loops=1)
         Output: id, c_100, c_500
         Recheck Cond: (t1.c_100 < 10)
         Rows Removed by Index Recheck: 13162468
         Heap Blocks: exact=29054 lossy=79055
         ->  Bitmap Index Scan on idx_t1  (cost=0.00..36888.35 rows=1997321 width=0) (actual time=226.889..226.889 rows=2000000 loops=1)
               Index Cond: (t1.c_100 < 10)
 Planning time: 0.093 ms
 Execution time: 12663.698 ms
(12 rows)

habr_10=# EXPLAIN ANALYSE VERBOSE SELECT count(*), c_100 FROM t1
    WHERE c_100 <10 GROUP BY c_100;
                                                                 QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=158320.22..158323.47 rows=100 width=12) (actual time=9450.053..9450.060 rows=10 loops=1)
   Output: count(*), c_100
   Group Key: t1.c_100
   ->  Sort  (cost=158320.22..158320.97 rows=300 width=12) (actual time=9450.050..9450.052 rows=40 loops=1)
         Output: c_100, (PARTIAL count(*))
         Sort Key: t1.c_100
         Sort Method: quicksort  Memory: 26kB
         ->  Gather  (cost=158276.87..158307.87 rows=300 width=12) (actual time=9449.733..9450.036 rows=40 loops=1)
               Output: c_100, (PARTIAL count(*))
               Workers Planned: 3
               Workers Launched: 3
               ->  Partial HashAggregate  (cost=157276.87..157277.87 rows=100 width=12) (actual time=9380.225..9380.227 rows=10 loops=4)
                     Output: c_100, PARTIAL count(*)
                     Group Key: t1.c_100
                     Worker 0: actual time=9357.189..9357.191 rows=10 loops=1
                     Worker 1: actual time=9357.320..9357.322 rows=10 loops=1
                     Worker 2: actual time=9356.856..9356.858 rows=10 loops=1
                     ->  Parallel Bitmap Heap Scan on public.t1  (cost=37775.94..154022.03 rows=650968 width=4) (actual time=181.108..9084.536 rows=500000 loops=4)
                           Output: c_100
                           Recheck Cond: (t1.c_100 < 10)
                           Rows Removed by Index Recheck: 2743963
                           Heap Blocks: exact=10792 lossy=16877
                           Worker 0: actual time=155.190..9113.397 rows=494347 loops=1
                           Worker 1: actual time=154.130..9053.253 rows=499488 loops=1
                           Worker 2: actual time=154.988..9021.038 rows=494091 loops=1
                           ->  Bitmap Index Scan on idx_t1  (cost=0.00..37271.44 rows=2018000 width=0) (actual time=239.332..239.332 rows=2000000 loops=1)
                                 Index Cond: (t1.c_100 < 10)
 Planning time: 0.129 ms
 Execution time: 9455.530 ms
(29 rows)

Parallel Index-Only Scan и Parallel Index Scan

Parallel Index-Only Scan

Сканирование индекса теперь можно делать параллельно. Рассмотрим план исполнения, возвращенный следующим запросом, обратив внимание на присутствие узла Gather :

habr_9_6=# EXPLAIN ANALYSE SELECT count(*) FROM t1 WHERE id > 10 AND id < 5000000;
                                                               QUERY PLAN                                                                
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=193908.66..193908.67 rows=1 width=8) (actual time=1726.007..1726.008 rows=1 loops=1)
   ->  Index Only Scan using pk_t1 on t1  (cost=0.44..181438.64 rows=4988010 width=0) (actual time=0.017..1323.316 rows=4999989 loops=1)
         Index Cond: ((id > 10) AND (id < 5000000))
         Heap Fetches: 4999989
 Planning time: 0.904 ms
 Execution time: 1726.031 ms
(6 rows)

habr_10=# EXPLAIN ANALYSE SELECT count(*) FROM t1 WHERE id > 10 AND id < 5000000;
                                                                          QUERY PLAN      
------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=153294.45..153294.46 rows=1 width=8) (actual time=1618.757..161
8.757 rows=1 loops=1)
   ->  Gather  (cost=153294.13..153294.44 rows=3 width=8) (actual time=1618.596..1618.751 
rows=4 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         ->  Partial Aggregate  (cost=152294.13..152294.14 rows=1 width=8) (actual time=16
10.488..1610.488 rows=1 loops=4)
               ->  Parallel Index Only Scan using pk_t1 on t1  (cost=0.44..148255.01 rows=
1615648 width=0) (actual time=1.779..1274.247 rows=1249997 loops=4)
                     Index Cond: ((id > 10) AND (id < 5000000))
                     Heap Fetches: 1258298
 Planning time: 0.931 ms
 Execution time: 1619.854 ms
(10 rows)

Parallel Index Scan
Теперь рассмотрим план исполнения, возвращенный таким запросом :

habr_9_6=# EXPLAIN ANALYSE SELECT count(c_100) FROM t1 WHERE id < 5000000;
                                                             QUERY PLAN                                                             
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=181438.82..181438.83 rows=1 width=8) (actual time=1655.367..1655.368 rows=1 loops=1)
   ->  Index Scan using pk_t1 on t1  (cost=0.44..168968.77 rows=4988019 width=4) (actual time=0.760..1137.062 rows=4999999 loops=1)
         Index Cond: (id < 5000000)
 Planning time: 0.055 ms
 Execution time: 1655.391 ms
(5 rows)

habr_10=# EXPLAIN ANALYSE SELECT count(c_100) FROM t1 WHERE id < 5000000;
                                                                       QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=140773.27..140773.28 rows=1 width=8) (actual time=1675.122..1675.122 rows=1 loops=1)
   ->  Gather  (cost=140772.95..140773.26 rows=3 width=8) (actual time=1675.111..1675.119 rows=4 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         ->  Partial Aggregate  (cost=139772.95..139772.96 rows=1 width=8) (actual time=1662.439..1662.439 rows=1 loops=4)
               ->  Parallel Index Scan using pk_t1 on t1  (cost=0.44..135733.82 rows=1615651 width=4) (actual time=1.020..1335.593 rows=1250000 loops=4)
                     Index Cond: (id < 5000000)
 Planning time: 0.060 ms
 Execution time: 1676.201 ms
(9 rows)

Наблюдение за фоновыми процессами

Эта главка не относится непосредственно к ускорению PostgreSQL, но уместная здесь, так как новые возможности распараллеливания дополнились и новыми средствами наблюдения за параллельными процессами.

В версии 10, как и в версии 9.6, можно, выполняя запрос в одной сессии, читать тексты запросов, обрабатываемых фоновыми процессами других сессий, используя представление pg_stat_activity:

habr_9_6=# -[ RECORD 1 ]----+------------------------------------------------------------------------
pid              | 12789
application_name | psql
backend_start    | 2018-03-30 12:51:10.997649+03
query            | SELECT pid,application_name,backend_start, query FROM pg_stat_activity;
-[ RECORD 2 ]----+------------------------------------------------------------------------
pid              | 12801
application_name | psql
backend_start    | 2018-03-30 12:52:57.486572+03
query            | EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT COUNT(id) FROM t1;
-[ RECORD 3 ]----+------------------------------------------------------------------------
pid              | 12823
application_name | psql
backend_start    | 2018-03-30 12:54:32.775267+03
query            | 
-[ RECORD 4 ]----+------------------------------------------------------------------------
pid              | 12822
application_name | psql
backend_start    | 2018-03-30 12:54:32.778756+03
query            | 
-[ RECORD 5 ]----+------------------------------------------------------------------------
pid              | 12821
application_name | psql
backend_start    | 2018-03-30 12:54:32.782583+03
query

В 10-ке видны типы процессов (backend_type), среди которых могут оказаться и фоновые процессы. К тому же поле state поможет WHERE state='active' оставить только активные процессы :

habr_10=# SELECT pid,application_name,backend_start,backend_type,query
FROM pg_stat_activity WHERE state='active';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------
pid              | 2225
application_name | psql
backend_start    | 2018-03-29 17:08:23.43802+03
backend_type     | background worker
query            | EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT count(id) FROM t1;
-[ RECORD 2 ]----+-----------------------------------------------------------------------------------------------------------
pid              | 462
application_name | psql
backend_start    | 2018-03-29 14:08:19.939538+03
backend_type     | client backend
query            | SELECT pid,application_name,backend_start, backend_type, query FROM pg_stat_activity WHERE state='active';
-[ RECORD 3 ]----+-----------------------------------------------------------------------------------------------------------
pid              | 2224
application_name | psql
backend_start    | 2018-03-29 17:08:23.44016+03
backend_type     | background worker
query            | EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT count(id) FROM t1;
-[ RECORD 4 ]----+-----------------------------------------------------------------------------------------------------------
pid              | 2223
application_name | psql
backend_start    | 2018-03-29 17:08:23.442845+03
backend_type     | background worker
query            | EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT count(id) FROM t1;
-[ RECORD 5 ]----+-----------------------------------------------------------------------------------------------------------
pid              | 2090
application_name | psql
backend_start    | 2018-03-29 17:03:03.776892+03
backend_type     | client backend
query            | EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT count(id) FROM t1;

Без WHERE state='active' будут видны и такие служебные процессы как walwriter и checkpointer, которые во время запроса оказались неактивны :

-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------
pid              | 2825
application_name | 
backend_start    | 2017-10-25 17:22:29.188114+03
backend_type     | background worker
state            | 
query            | 
-[ RECORD 2 ]----+---------------------------------------------------------------------------------------------
pid              | 2823
application_name | 
backend_start    | 2017-10-25 17:22:29.187815+03
backend_type     | autovacuum launcher
state            | 
query            | 
-[ RECORD 3 ]----+---------------------------------------------------------------------------------------------
pid              | 2855
application_name | psql
backend_start    | 2018-03-29 18:18:09.743613+03
backend_type     | client backend
state            | active
query            | SELECT pid,application_name,backend_start, backend_type, state, query FROM pg_stat_activity;
-[ RECORD 4 ]----+---------------------------------------------------------------------------------------------
pid              | 2821
application_name | 
backend_start    | 2017-10-25 17:22:29.18081+03
backend_type     | background writer
state            | 
query            | 
-[ RECORD 5 ]----+---------------------------------------------------------------------------------------------
pid              | 2820
application_name | 
backend_start    | 2017-10-25 17:22:29.181031+03
backend_type     | checkpointer
state            | 
query            | 
-[ RECORD 6 ]----+---------------------------------------------------------------------------------------------
pid              | 2822
application_name | 
backend_start    | 2017-10-25 17:22:29.180576+03
backend_type     | walwriter
state            | 
query            |------

Выигрыш при агрегировании

Для экономии места не будем приводить код создания базы данных Заказов, включающей несколько таблиц. Вот пример запроса, использующего предложение GROUP BY с разными наборами группирования :

EXPLAIN (ANALYZE, BUFFERS, COSTS off) SELECT
GROUPING(client_type, country_code)::bit(2),
       GROUPING(client_type)::boolean g_type_cli,
       GROUPING(country_code)::boolean g_code_pays,
       cl.client_type,
       co.country_code,
       SUM(l.price*l.quantity) AS topay
  FROM orders c
  JOIN order_lines l
    ON (c.order_number = l.order_number)
  JOIN clients cl
    ON (c.client.id = cl.client_id)
  JOIN contacts co
    ON (cl.contact_id = co.contact_id)
 WHERE c.order_date BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY CUBE (cl.client_type, co.country_code);

Запрос будет обрабатываться по-разному в 9.6 и в 10. В PostgreSQL 9.6, задействуется узел плана GroupAggregate :

                             QUERY PLAN
--------------------------------------------------------------------------------
 GroupAggregate  (actual time=2720.032..4971.515 rows=40 loops=1)
   Group Key: cl.type_client, co.code_pays
   Group Key: cl.type_client
   Group Key: ()
   Sort Key: co.code_pays
     Group Key: co.code_pays
   Buffers: shared hit=8551 read=47879, temp read=32236 written=32218
   ->  Sort  (actual time=2718.534..3167.936 rows=1226456 loops=1)
         Sort Key: cl.type_client, co.code_pays
         Sort Method: external merge  Disk: 34664kB
         Buffers: shared hit=8551 read=47879, temp read=25050 written=25032
     ->  Hash Join  (actual time=525.656..1862.380 rows=1226456 loops=1)
           Hash Cond: (l.numero_commande = c.numero_commande)
           Buffers: shared hit=8551 read=47879, temp read=17777 written=17759
       ->  Seq Scan on lignes_commandes l  
             (actual time=0.091..438.819 rows=3141967 loops=1)
             Buffers: shared hit=2241 read=39961
       ->  Hash  (actual time=523.476..523.476 rows=390331 loops=1)
             Buckets: 131072  Batches: 8  Memory Usage: 3162kB
             Buffers: shared hit=6310 read=7918, temp read=1611 written=2979
         ->  Hash Join  
               (actual time=152.778..457.347 rows=390331 loops=1)
               Hash Cond: (c.client_id = cl.client_id)
               Buffers: shared hit=6310 read=7918, temp read=1611 written=1607
           ->  Seq Scan on commandes c  
                 (actual time=10.810..132.984 rows=390331 loops=1)
                 Filter: ((date_commande >= '2014-01-01'::date)
                           AND (date_commande <= '2014-12-31'::date))
                 Rows Removed by Filter: 609669
                 Buffers: shared hit=2241 read=7918
           ->  Hash  (actual time=139.381..139.381 rows=100000 loops=1)
                 Buckets: 131072  Batches: 2  Memory Usage: 3522kB
                 Buffers: shared hit=4069, temp read=515 written=750
                 ->  Hash Join  
                     (actual time=61.976..119.724 rows=100000 loops=1)
                     Hash Cond: (co.contact_id = cl.contact_id)
                     Buffers: shared hit=4069, temp read=515 written=513
                   ->  Seq Scan on contacts co  
                         (actual time=0.051..18.025 rows=110005 loops=1)
                         Buffers: shared hit=3043
                   ->  Hash  
                         (actual time=57.926..57.926 rows=100000 loops=1)
                         Buckets: 65536  Batches: 2  Memory Usage: 3242kB
                         Buffers: shared hit=1026, temp written=269
                     ->  Seq Scan on clients cl  
                           (actual time=0.060..21.896 rows=100000 loops=1)
                           Buffers: shared hit=1026
 Planning time: 1.739 ms
 Execution time: 4985.385 ms
(41 rows)

В PostgreSQL 10, как можно заметить, появляется узел плана MixedAggregate, то есть возможность выполнения GROUPING SETS (наборы группирования) с хешированием и сортировкой. Использование MixedAggregate ускоряет выполнение запроса вдвое :

                             QUERY PLAN
--------------------------------------------------------------------------------
 MixedAggregate  (actual time=2640.531..2640.561 rows=40 loops=1)
   Hash Key: cl.type_client, co.code_pays
   Hash Key: cl.type_client
   Hash Key: co.code_pays
   Group Key: ()
   Buffers: shared hit=8418 read=48015, temp read=17777 written=17759
   ->  Hash Join  (actual time=494.339..1813.743 rows=1226456 loops=1)
       Hash Cond: (l.numero_commande = c.numero_commande)
       Buffers: shared hit=8418 read=48015, temp read=17777 written=17759
       ->  Seq Scan on lignes_commandes l
             (actual time=0.019..417.992 rows=3141967 loops=1)
             Buffers: shared hit=2137 read=40065
       ->  Hash  (actual time=493.558..493.558 rows=390331 loops=1)
             Buckets: 131072  Batches: 8  Memory Usage: 3162kB
             Buffers: shared hit=6278 read=7950, temp read=1611 written=2979
           ->  Hash Join  (actual time=159.207..429.528 rows=390331 loops=1)
                 Hash Cond: (c.client_id = cl.client_id)
                 Buffers: shared hit=6278 read=7950, temp read=1611 written=1607
               ->  Seq Scan on commandes c
                 (actual time=2.562..103.812 rows=390331 loops=1)
                     Filter: ((date_commande >= '2014-01-01'::date)
                               AND (date_commande <= '2014-12-31'::date))
                     Rows Removed by Filter: 609669
                     Buffers: shared hit=2209 read=7950
               ->  Hash  (actual time=155.728..155.728 rows=100000 loops=1)
                     Buckets: 131072  Batches: 2  Memory Usage: 3522kB
                     Buffers: shared hit=4069, temp read=515 written=750
                   ->  Hash Join
                 (actual time=73.906..135.779 rows=100000 loops=1)
                         Hash Cond: (co.contact_id = cl.contact_id)
                         Buffers: shared hit=4069, temp read=515 written=513
                       ->  Seq Scan on contacts co  
                             (actual time=0.011..18.347 rows=110005 loops=1)
                             Buffers: shared hit=3043
                       ->  Hash  (actual time=70.006..70.006 rows=100000 loops=1)
                             Buckets: 65536  Batches: 2  Memory Usage: 3242kB
                             Buffers: shared hit=1026, temp written=269
                           ->  Seq Scan on clients cl  
                                 (actual time=0.014..26.689 rows=100000 loops=1)
                                 Buffers: shared hit=1026
 Planning time: 1.910 ms
 Execution time: 2642.349 ms
(36 rows)

Переходные таблицы

Если триггер работает на уровне операторов, OLD и NEW использовать нельзя, так как они применимы только к одной строке. Для этого случая стандарт SQL предусматривает переходные таблицы.

Версия 10 позволяет решить эту проблему на основе стандарта SQL.

Вот пример использования:

Мы создадим таблицу main, у которой будет триггер, и таблицу archive для хранения удаленных из main записей.

habr_10=# CREATE TABLE main (c1 integer, c2 text);
CREATE TABLE

habr_10=# CREATE TABLE archive (id integer GENERATED ALWAYS AS IDENTITY, 
            dlog timestamp DEFAULT now(),
            main_c1 integer, main_c2 text);
CREATE TABLE

Теперь надо создать код для хранимой процедуры :

habr_10=# CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
            BEGIN
              INSERT INTO archive (main_c1, main_c2) SELECT c1, c2 FROM oldtable;
              RETURN null;
            END
            $$;
CREATE FUNCTION

И добавить триггер к таблице main :

habr_10=# CREATE TRIGGER tr1
            AFTER DELETE ON main
            REFERENCING OLD TABLE AS oldtable
            FOR EACH STATEMENT
            EXECUTE PROCEDURE log_delete();
CREATE TRIGGER

Теперь вставим миллион строк и удалим их. Можно узнать время удаления строк и время работы триггера, используя EXPLAIN ANALYZE :

habr_10=# INSERT INTO main SELECT i, 'a_string'||i FROM generate_series(1, 1000000) i;
INSERT 0 1000000

habr_10=# EXPLAIN (ANALYZE) DELETE FROM main;
                                                      QUERY PLAN                          
------------------------------------------------------------------------------------------
 Delete on main  (cost=0.00..17642.13 rows=1127313 width=6) (actual time=1578.771..1578.77
1 rows=0 loops=1)
   ->  Seq Scan on main  (cost=0.00..17642.13 rows=1127313 width=6) (actual time=0.018..10
6.833 rows=1000000 loops=1)
 Planning time: 0.026 ms
 Trigger tr1: time=2494.337 calls=1
 Execution time: 4075.228 ms
(5 rows)

Мы видим, что удаление строк занимает примерно 1.5 секунды, в то время как триггер работает 2.5 секунды.

Для сравнения вот как это делалось раньше (с конфигурацией триггера на уровне строк) :

habr_9_6=# CREATE TABLE main (c1 integer, c2 text);
CREATE TABLE

habr_9_6=# CREATE TABLE archive (id integer, 
            dlog timestamp DEFAULT now(),
            main_c1 integer, main_c2 text);
CREATE TABLE

habr_9_6=# CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
            BEGIN
              INSERT INTO archive (main_c1, main_c2) VALUES (old.c1, old.c2);
              RETURN null;
            END
            $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER tr1
            AFTER DELETE ON main
            FOR EACH ROW
            EXECUTE PROCEDURE log_delete();
CREATE TRIGGER

habr_9_6=# INSERT INTO main SELECT i, 'a_string'||i FROM generate_series(1, 1000000) i;
INSERT 0 1000000

habr_9_6=# EXPLAIN ANALYZE DELETE FROM main;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Delete on main  (cost=0.00..16369.00 rows=1000000 width=6) (actual time=2009.263..2009.263 rows=0 loops=1)
   ->  Seq Scan on main  (cost=0.00..16369.00 rows=1000000 width=6) (actual time=0.028..108.559 rows=1000000 loops=1)
 Planning time: 0.131 ms
 Trigger tr1: time=8572.522 calls=1000000
 Execution time: 10649.182 ms
(5 rows)

Мы видим, что в режиме работы на уровне строки триггер удаляет миллион строк за 10.7 секунд, из них 8.6 приходится на работу триггера. При работе триггера на уровне операторов получается 4 секунды из которых 1.5 тратится на работу триггера. То есть переходные таблицы позволяют увеличить производительность.

Большой интерес к переходным таблицам связан именно с этим.

Чтобы узнать больше по этой теме, следуйте :

• Implement syntax for transition tables in AFTER triggers
• Cool Stuff in PostgreSQL 10: Transition Table Triggers

Многоколоночная статистика

Появилась возможность создавать статистику по нескольким столбцам одной таблицы. Благодаря этому можно улучшить оценки при составлении плана выполнения в случае, когда столбцы сильно коррелируют.

Например :

habr_10=# CREATE TABLE multi (a INT, b INT);
CREATE TABLE

habr_10=# INSERT INTO multi SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
INSERT 0 10000

habr_10=# ANALYZE multi;
ANALYZE

Распределение данных очень простое: существует всего 100 различных значений, распределенных по таблице равномерно.

Для столбца a:

habr_10=# EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM multi WHERE a = 1;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Seq Scan on multi  (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 9900
 Planning time: 0.063 ms
 Execution time: 0.496 ms
(5 rows)

Оптимизатор проверяет условие и делает вывод, что селективность этого условия 1% (rows=100 из 10000 вставленных записей).

Аналогично получаем оценку по столбцу b.

Теперь применим то же условие к каждому столбцу, используя AND :

habr_10=# EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM multi WHERE a = 1 AND b = 1;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Seq Scan on multi  (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.116 ms
 Execution time: 2.154 ms
(5 rows)

Оптимизатор оценивает селективность для каждого условия отдельно, получая ту же оценку в 1 %, что мы видели выше. Окончательная оценка селективности дает 0,01 % уникальных значений, то есть недооценивает очень существенно (большая разница между cost и actual).

Чтобы улучшить оценку, мы теперь можем создать многоколоночную статистику :

habr_10=# CREATE STATISTICS s1 (dependencies) ON a, b FROM multi;
CREATE STATISTICS

habr_10=# ANALYZE multi;
ANALYZE

Теперь проверим :

habr_10=# EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM multi WHERE a = 1 AND b = 1;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Seq Scan on multi  (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.086 ms
 Execution time: 0.525 ms
(5 rows)

Теперь оценка адекватна.

Для получения более полной информации можно обратиться к странице Implement multivariate n-distinct coefficients.

Продолжение следует