How to speed up PostgreSQL 10

    (The article used examples and explanations from the book Nouveaulités de PostgreSQL 10. (c) Dalibo, translation from French by Igor Levshin, editor Yegor Rogov ( original ). Examples are checked, sometimes modified for greater clarity)


    Of course, we are already waiting for the 11th version of PostgreSQL to wait. But now it’s clear that some rather radical performance improvements appeared already in version 10. It definitely makes sense to deal with them first.


    The performance of the "tens" has improved in several directions at once. This article will focus on acceleration due to:


    • parallelization of scanning tables and indexes,
    • more efficient aggregation,
    • quick transition tables
    • query acceleration due to multi-column statistics.

    We will start with concurrency.


    Concurrency in PostgreSQL 10


    In version 9.6, parallelization of sequential table reading, joining, and aggregation already worked. This concerned read requests, but not write requests. No INSERT/ UPDATE/ DELETEor CTE-writing queries (Common Table Expressions, common table expressions) or servicing operations ( CREATE INDEX, VACUUM, ANALYZE) did not support parallelization.


    Version 10 makes it possible to parallelize:


    • index scan ( Index Scanand Index Only Scan)
    • merge join ( Merge Join)
    • collecting results while maintaining the sort order ( Gather Merge)
    • execution of prepared requests
    • execution of non-correlating subqueries

    In a merge join ( Merge Join), the left and right tables are ordered and then compared in parallel.


    The plan node Gather, introduced in version 9.6, collects the results of all background processes in random order. Gather Mergeapplies if each background process returns sorted results. The node keeps order.


    To learn more about concurrency, see Robert Haas's Parallel Query v2 .


    Parameters


    Accordingly, parameters appeared in postgresql.config: it
    min_parallel_table_scan_sizedetermines the minimum amount of table data above which the possibility of parallelizing a scan can be considered.


    min_parallel_index_scan_size determines the minimum amount of index data above which the possibility of parallelizing the scan can be considered.


    max_parallel_workersdetermines the maximum number of background processes that a DBMS can allocate for processing parallel requests. By default, this parameter is 8.


    When you increase or decrease this parameter, do not forget to consider the parameter max_parallel_workers_per_gather


    max_parallel_workers_per_gatherdefines the maximum number of parallel processes that can be allocated to a single Gather plan node. By default, the parameter is 2. A value of 0 disables query parallelism.


    Training


    Create a table t1in 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

    Change the parameter 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)

    Repeat the same with PostgreSQL 9.6.


    Parallel Bitmap Heap Scan


    In Reading PostgreSQL 9.6, only sequential table scans ( parallel sequential scan) could be parallelized when reading , but not index access. The scheduler had to choose between parallelization and index usage.


    Because PostgreSQL 10 is available parallel bitmap heap scan, scanning processes create data structures in memory that show which data pages to read. Background processes will then be able to read their portion of the pages in parallel.


    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 and Parallel Index Scan


    Parallel index-only scan


    Index scanning can now be done in parallel. Consider the execution plan returned by the following query, paying attention to the presence of the node 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
    Now consider the execution plan returned by this query:


    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)

    Monitoring Background Processes


    This chapter does not apply directly to PostgreSQL acceleration, but is relevant here, as new parallelization capabilities have been supplemented with new means of monitoring parallel processes.


    In version 10, as in version 9.6, it is possible, by executing a request in one session, to read the texts of requests processed by the background processes of other sessions using the view 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

    In 10-ke types of processes are visible ( backend_type), among which there may also be background processes. In addition, the field statewill help WHERE state='active'to leave only active processes:


    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;

    Without WHERE state='active', such service processes as walwriterand checkpointer, which turned out to be inactive during the request, will also be visible :


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

    Aggregate gain


    Для экономии места не будем приводить код создания базы данных Заказов, включающей несколько таблиц. Вот пример запроса, использующего предложение 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)

    The optimizer estimates the selectivity for each condition separately, receiving the same estimate of 1% as we saw above. The final assessment of selectivity gives 0.01% of unique values, that is, it underestimates very significantly (a large difference between costand actual).


    To improve the score, we can now create multi-column statistics:


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

    Now check:


    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)

    Now the assessment is adequate.


    For more information, see the Implement multivariate n-distinct coefficients page .


    To be continued


    Also popular now: