Как ускорили PostgreSQL 10
(В статье использованы примеры и пояснения из книги Nouveaulités 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.
Продолжение следует
