Главная » Хабрахабр » Как ускорили PostgreSQL 10

Как ускорили 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 тратится на работу триггера. То есть переходные таблицы позволяют увеличить производительность.

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

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

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

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

Например :

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.

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


x

Ещё Hi-Tech Интересное!

Фиаско. История одной самоделки IoT

Большинство статей пишется по принципу «Я/мы это сделал/и, глядите как круто!». Эта же публикация посвящается провальному проекту. Добро пожаловать под кат… Это продолжение моей публикации Разработка умных устройств на примере контроллера теплого пола на ESP8266 Планировка — евротрешка, коридор, кухня-гостиная ...

Хакер Алексей, который защищает маршрутизаторы MikroTik без разрешения владельцев, стал знаменитым

маршрутизаторов MikroTik по всему миру (в том числе 40 тыс. На Хабре подробно рассказывали про уязвимость CVE-2018-14847, которой подвержены около 370 тыс. Если вкратце, уязвимость в MikroTik RouterOS позволяет без особой авторизации прочитать удалённо любой файл с роутера, включая плохо ...