Geekly Articles each Day
📜 ⬆️ ⬇️

How to speed up PostgreSQL 10

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


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


Performance "dozens" has improved in several directions. This article will focus on acceleration due to:



We will start with concurrency.


Concurrency in PostgreSQL 10


In version 9.6, parallelization of sequential table reading, joining, and aggregation already worked. This was about reading requests, but not writing requests. Neither INSERT / UPDATE / DELETE , nor writing CTE queries (Common Table Expressions, common table expressions), nor serving operations ( CREATE INDEX , VACUUM , ANALYZE ) supported parallelization.


Version 10 allows you to parallelize:



With a merge join, the left and right tables are ordered and then compared in parallel.


The Gather plan node, which appeared in version 9.6, collects the results of all background processes in random order. Gather Merge applies if each background process returns sorted results. The node maintains order.


To learn more about concurrency, see the Parallel Query v2 article by Robert Haas .


Options


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


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


max_parallel_workers determines 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 max_parallel_workers_per_gather parameter


max_parallel_workers_per_gather determines 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 request parallelism.


Training


Create a table t1 in 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 PostgreSQL 9.6, when reading parallelization, it was possible to only sequentially scan tables ( parallel sequential scan ), but not index access. The scheduler had to choose between parallelization and using the index.


Due to the parallel bitmap heap scan available in PostgreSQL 10, scanning processes create in-memory data structures that indicate which data pages should be read. Background processes can then read their portions of 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 scans can now be done in parallel. Consider the execution plan returned by the following request, paying attention to the presence of the Gather node:


 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 such a 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 relate directly to PostgreSQL acceleration, but is relevant here, since new parallelization features have been supplemented with new tools for monitoring parallel processes.


In version 10, as in version 9.6, you can, while executing a request in one session, read the text of requests processed by background processes of other sessions, using the 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, you can see the types of processes ( backend_type ), among which may be background processes. In addition, the state field will 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' , service processes such as walwriter and checkpointer will also be visible, which during the query were inactive:


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

Benefit from aggregation


To save space, we will not provide the code for creating a database of Orders, which includes several tables. Here is an example of a query that uses the GROUP BY with different grouping sets:


 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);

The request will be processed differently in 9.6 and 10. In PostgreSQL 9.6, the GroupAggregate plan node is involved:


  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)

In PostgreSQL 10, as you can see, the MixedAggregate plan node appears, that is, the ability to perform GROUPING SETS (grouping sets) with hashing and sorting. Using MixedAggregate speeds query execution by half:


  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)

Transition tables


If the trigger works at the operator level, OLD and NEW cannot be used, since they are applicable only to one line. For this case, the SQL standard provides transition tables.


Version 10 solves this problem based on the SQL standard.


Here is an example of use:


We will create a main table, which will have a trigger, and an archive table to store the records deleted from 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

Now you need to create the code for the stored procedure:


 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

And add a trigger to the main table:


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

Now insert a million lines and delete them. You can find out the time for deleting rows and the trigger time using 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)

We see that deleting lines takes about 1.5 seconds, while the trigger works 2.5 seconds.


For comparison, this is how it was done before (with the configuration of the trigger at the row level):


 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)

We see that in the operation at the row level the trigger deletes a million rows in 10.7 seconds, of which 8.6 falls on the trigger operation. When the trigger operates at the operator level, 4 seconds are obtained, of which 1.5 is spent on the trigger. That is, the transition tables can increase productivity.


Great interest in transition tables is associated with this.


To learn more about this topic, follow:



Multicolumn statistics


Now you can create statistics for several columns of one table. This makes it possible to improve the estimates in drawing up the execution plan in the case when the columns correlate strongly.


For example :


 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

The distribution of data is very simple: there are only 100 different values ​​distributed evenly over the table.


For column 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)

The optimizer checks the condition and concludes that the selectivity of this condition is 1% (rows = 100 out of 10,000 inserted records).


Similarly, we obtain an estimate for column b .


Now apply the same condition to each column using 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, getting the same estimate at 1%, as we saw above. The final estimate of selectivity gives 0.01% of unique values, that is, it underestimates very significantly (a large difference between cost and actual ).


To improve the score, we can now create multicolumn statistics:


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

Now let's 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 score is adequate.


For more information, you can refer to the Implement multivariate n-distinct coefficients page.


To be continued


')

Source: https://habr.com/ru/post/352144/

More articles:


All Articles