Postgres ááá¹áá¬ááœááºá áá±áá¬áá±á·á
áºááá¯ááŸá±á¬ááºááŸá¯á áááá±á¬ááºáá±á¬áááºážááŒá±á¬ááºážááŒááŸá¯á¡ááœáẠá¡ááœáŸááºážááááºážáá»á¬ážááẠáááŸááááŒá
áºááá¯á¡ááºááẠ("heap" áá¯áá±á«áºáááº)á Postgres ááẠáááºážá¡ááœáẠá¡á
á¯ááá¯ááºá¡ááŒá¯á¶ááá¯ááºááŒá¯áá¯ááºááŒááºážááᯠááá¶á·ááá¯ážáá«á ááŸáá·áº MVCC áááá¯áá¬ááẠááá·áºá¡á¬áž áá°áá®áá±á¬ tuple áá¬ážááŸááºážáá»á¬ážá
áœá¬ááŒáá·áº á¡áá¯á¶ážáááºá
á±áááºá ááá¯á·ááŒá±á¬áá·áºá á¡áá¯á¶ážáá»áááá¯ááááºáá»á¬ážááᯠáá¶á·ááá¯ážáááºá¡ááœáẠáááá±á¬ááºáá±á¬ á¡ááœáŸááºážááááºážáá»á¬ážááᯠáááºáá®ážááááºážááááºážááá¯ááºááẠá¡ááœááºá¡áá±ážááŒá®ážáá«áááºá
á€áááºááŸá¬ á¡ááœáŸááºážááááºážáá»á¬ážá¡áá¯á¶ážááŒá¯ááŸá¯ááᯠá¡áá±á¬ááºážáá¯á¶ážááŒá áºá¡á±á¬áẠááŒáŸáá·áºáááºááẠá¡ááŒá¶ááŒá¯áá»ááºá¡áá»áá¯á·ááŒá áºáááºá
ááŸááºáá»ááº- á¡á±á¬ááºááœááºáá±á¬áºááŒáá¬ážáá±á¬ áá±ážááŒááºážáá»ááºáá»á¬ážááẠááœááºážáá¶áá¬ážáá±á¬ á¡áá¬áá
áºáá¯ááœáẠá¡áá¯ááºáá¯ááºáá«áááºá
Covering Index áá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááŒááºážá
áááŸá¯ááºáááŸá¬ážá¡áá¯á¶ážááŒá¯áá°áá»á¬ážá¡ááœáẠá¡á®ážáá±ážááºááááºá
á¬áá»á¬ážááᯠáá¯ááºáá°ááẠáá±á¬ááºážááá¯áá»ááºááᯠááŒáá·áºááŒáá«á
áá¯á·á á
á¬ážááœá² customer
áá±á¬áºáá¶áá
áºáá¯ááŸááááºá active
á áá±ážááŒááºážáá»ááºááẠááá¯ážááŸááºážáááº-
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
áá±ážááœááºážááẠááá¬ážá¡ááŒáá·áºá¡á
á¯á¶ááᯠá
áááºáááºá
á
áºáá±ážááŒááºáž á¡á
á®á¡á
ááºááᯠáá±á«áºááá¯áááºá customer
. áá±á¬áºáá¶áá
áºáá¯áá±á«áºááœáẠá¡ááœáŸááºážáá
áºáᯠáááºáá®ážááŒáá«á
áá¯á· active
:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
áááºážááẠáá°áá®áá±ážáááºá áá±á¬ááºáááºááœá²á
áááºááºááẠ"index scan
â . ááá¯ááá¯áááºááŸá¬ Postgres ááẠá¡ááœáŸááºážááᯠá
áááºááºáááºáááºáidx_cust1
"á ááá¯á·áá±á¬áẠá¡ááŒá¬ážáá±á¬áºáá¶áá»á¬ážá áááºááá¯ážáá»á¬ážááᯠáááºááẠááá¬ážá¡ááŸáá¯ááºáá¯á¶á¡á¬áž áááºáááºááŸá¬ááœá±áá« (á€ááá
á¹á
ááœááºá áá±á¬áºáá¶á email
á) áá±ážááŒááºážááŸá¯ ááá¯á¡ááºáá«áááºá
á¡áá»á¯á¶ážáááºáá±á¬ á¡ááœáŸááºážáá»á¬ážááᯠPostgreSQL 11 ááœáẠááááºáááºáá¬ážáááºá áááºážááá¯á·ááẠááá·áºá¡á¬áž á¡ááœáŸááºážááááºážááœáẠáá áºáᯠááá¯á·ááá¯áẠáá áºáá¯áááºááá¯áá±á¬ áá±á¬ááºáááºáá±á¬áºáá¶áá»á¬ážáá«áááºááœáá·áºááŒá¯ááẠ- áááºážááá¯á·ááááºááá¯ážáá»á¬ážááᯠá¡ááœáŸááºážáá±áá¬á ááá¯ážááœáẠááááºážáááºážáá¬ážáááºá
áá»áœááºá¯ááºááá¯á·ááẠá€á¡ááºá¹áá«áááºááᯠá¡ááœáá·áºáá±á¬ááºážáá°áᬠá¡ááœáŸááºážá¡ááœááºáž á¡á®ážáá±ážááºáááºááá¯ážááᯠáá±á«ááºážááá·áºáá«áá ááá¯á·áá±á¬áẠPostgres ááẠáááºááá¯ážá¡ááœáẠááá¬ážááœááºááᯠááŸá¬ááœá±ááẠáááá¯á¡ááºáá«á email
. áá«á á¡áá¯ááºááŒá
áºáááŒá
ẠááŒáá·áºáá¡á±á¬ááºá
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan
' ááá¯áá±ážááŒááºážáá»ááºááẠááá¬ážáá¯á¶á¡á¬ážáááºááẠdisk I/O á¡á¬ážáá¯á¶ážááᯠááŸá±á¬ááºááŸá¬ážááẠáá°áá®áá±ážááá·áº á¡ááœáŸááºážáá
áºáá¯áᬠááá¯á¡ááºááŒá±á¬ááºáž áá»áœááºá¯ááºááá¯á·á¡á¬áž ááŒá±á¬ááŒáááºá
á¡áá»á¯á¶ážáááºáá±á¬ á¡ááœáŸááºážááááºážáá»á¬ážááẠB-trees á¡ááœááºáᬠáá±á¬áá±á¬ááẠáááá¯ááºáá«áááºá ááá¯á·áá±á¬áºáááºáž á€ááá á¹á ááœááºá ááŒá¯ááŒááºááááºážááááºážááŸá¯ á¡á¬ážáá¯ááºááŸá¯ááẠááá¯ááá¯ááŒáá·áºáá¬ážáá¬áááºááŒá áºáááºá
Partial Index áá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááŒááºážá
áá áºá áááºáá áºááá¯ááºáž á¡ááœáŸááºážááááºážáá»á¬ážááẠááá¬ážáá áºáá¯ááŸá á¡áááºážáá»á¬ážá á¡á á¯ááœá²áá áºáá¯áá¬ááŒá áºáááºá áááºážááẠá¡ááœáŸááºážááááºážáá»á¬ážá á¡ááœááºá¡á á¬ážááᯠáááºáá¬á á±ááŒá®áž á áááºáááºááŸá¯áá»á¬ážááᯠááá¯ááá¯ááŒááºáááºá á±áááºá
áááºáá®ááá¯ážáá®ážáá¬ážááŸá áá»áœááºá¯ááºááá¯á·ááá±á¬ááºáááºáá»á¬ážá á¡á®ážáá±ážááºááááºá á¬áá»á¬ážá á¬áááºážááᯠááá°ááá¯áááºáᯠááá¯ááŒáá«á áá¯á·á áá±á¬ááºážááá¯áá»ááºááẠá€áá²á·ááá¯á·ááŒá áºáááá·áºáááº-
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)
áá¬áá¬ááºá¡ááœáŸááºážááááºážáá»á¬ážááẠáá»áœááºá¯ááºááá¯á·á¡á¬áž áááºááá·áºá¡áá¬áá»á¬áž áá±ážáááºáááºážá
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
á
ááẠaddress
á¡ááœáŸááºážá
áááºááºááŒáá·áº á¡á
á¬ážááá¯ážáá¬ážáááºá idx_address1
ááá¯á·áá±á¬áẠá¡ááŸáá¯ááºáá¯á¶ááᯠá
áááºááºáááºáááºá address
.
áááºážááẠáááŒá¬áááá±ážááŒááºážááŸá¯ááŒá
áºááŒá®áž á¡áá±á¬ááºážáá¯á¶ážááŒá
áºá¡á±á¬ááºááŒá¯áá¯ááºááẠááá¯á¡ááºáá±á¬ááŒá±á¬áá·áºá áááá¯ááºá¡ááœááºážááŸáááááºá
á¬áá»á¬ážáá«ááŸáááá·áº á¡áááºážáá»á¬ážááá¯áᬠá¡ááœáŸááºážááááºážáá±ážááá·áº áá
áºá
áááºáá
áºááá¯ááºážá¡ááœáŸááºážááᯠáá»áœááºá¯ááºááá¯á·á¡áá¯á¶ážááŒá¯ááá¯ááºáá«áááºá âCaliforniaâ
:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
áá¯áá±á¬á· áá±ážááœááºážá áááºáá¯á¶áá²á idx_address2
á
á¬ážááœá²ááá¯ááááá°ážá address
.
Multi-Value Index áá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááŒááºážá
á¡ááœáŸááºážááŒá¯áááá·áº áá±á¬áºáá¶á¡áá»áá¯á·ááœáẠá
ááá¬áá±áá¬á¡áá»áá¯ážá¡á
á¬áž ááá«áááºáá«á áá±á¬áºáᶠá¡áá»áá¯ážá¡á
á¬ážáá»á¬áž jsonb
, arrays
О tsvector
áá±á«ááºážá
áẠááá¯á·ááá¯áẠáááºááá¯ážáá»á¬ážá
áœá¬ áá«áááºáááºá ááá¯ááá¯á·áá±á¬áá±á¬áºáá¶áá»á¬ážááᯠá¡ááœáŸááºážáá±ážááẠááá¯á¡ááºáá«áá ááá¯áá±á¬áºáá¶áá»á¬ážááœáẠáá
áºáŠážáá»ááºážáááºááá¯ážáá»á¬ážá¡á¬ážáá¯á¶ážááᯠááŸá¬ááœá±ááááºáááºá
áá¡á±á¬ááºááŒááºáá±á¬ á¡áá»áááºáá»á¬ážááᯠááŒááºáá±á¬ááºáá¬ážáá±á¬ áá¯ááºááŸááºá¡á¬ážáá¯á¶ážá áá±á«ááºážá
ááºáá»á¬ážááᯠááŸá¬ááŒáá·áºááŒáá«á
áá¯á·á á
á¬ážááœá² film
á
á¬áá¬ážáá±á¬áºáá¶áá¯áá±á«áºáááºá special_features
. áá¯ááºááŸááºááœáẠဠ"á¡áá°ážááá¯ááºááá¯ááºááŸá¯" ááŸááá«áá áá±á¬áºáá¶ááœáẠá
á¬áá¬ážá¡áááºážá¡áá»ááºážáá
áºáá¯á¡áá±ááŒáá·áº á¡á
áááºá¡ááá¯ááºážáá«ááŸááááºá Behind The Scenes
. ááá¯áá²á·ááá¯á·áá±á¬áá¬ááºáá¬ážáá»á¬ážá¡á¬ážáá¯á¶ážááᯠááŸá¬ááœá±ááẠ"Behind The Scenes" ááŸáá·áº á¡áááºážá¡á¬ážáá¯á¶ážááᯠááœá±ážáá»ááºááẠááá¯á¡ááºáá«áááºá áááºááá·áº array áááºááá¯ážáá»á¬áž special_features
:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
ááá¯ááºá¡á±á¬áº @>
áá¬áááºááŒááºážááẠáááºáááºááŒááºážá á¡ááœá²áá
áºáᯠáá¯ááºááá¯áẠá
á
áºáá±ážáá«á
á¡á á®á¡á ááºáá±á¬ááºážááá¯áááº-
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)
áá¯ááºáá»á áááẠ67 ááŒáá·áº á¡ááŒáá·áºá¡á á¯á¶ á áááºáááºááẠáá±á¬ááºážááá¯áá¬ážáááºá
áá¯á¶ááŸáẠB-tree á¡ááœáŸááºážááááºážá áá»áœááºá¯ááºááá¯á·ááᯠáá°áá®áá±ážááá¬áž ááŒáá·áºáá¡á±á¬ááºá
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)
á¡ááœáŸááºážáá±á¬áẠááá·áºáá ááºážá á¬ážáá°ážá B-tree á¡ááœáŸááºážááááºážááẠá¡ááœáŸááºážááááºážáááºááá¯ážáá»á¬ážááœáẠáá áºáá¯áá»ááºážááŒááºá ááºáá»á¬áž ááŸááá±ááŒá±á¬ááºážááᯠááááá«á
áá»áœááºá¯ááºááá¯á·ááẠGIN á¡ááœáŸááºážááááºážáá áºáá¯ááá¯áááºá
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)
GIN á¡ááœáŸááºážááááºážááẠá¡ááœáŸááºážááááºážáá±á«ááºážá ááºáá¬ážáá±á¬áááºááá¯ážáá»á¬ážááŸáá·áº ááŒá±áá¯á¶áá áºáá¯áá»ááºážáááºááá¯ážáá»á¬ážááᯠáá¶á·ááá¯ážáá±ážáááºá ááááºá¡áá±ááŒáá·áº query plan áá¯ááºáá»á ááááºááẠáááºáááºáá»á±á¬áºáááºá
ááœá¬ážáá±áá±á¬ á¡ááœáŸááºážáá»á¬ážááᯠáááºááŸá¬ážááŒááºážá
á¡ááœáŸááºážáá»á¬ážááẠá¡áá»áááºááŒá¬áá¬áááºááŸáá·áºá¡áá»áŸ á
á¯áá¯á¶áá¬áᬠáá
áºáá«áá
áºáá¶ááœáẠá¡ááœáŸááºážá¡áá
áºáá
áºáá¯ááœáẠááááºáá
áºáá¯ááŸáá·áºáá
áºáá¯áá²á·ááá¯á· áá°áá®áá±á¬á¡áááá¹áá«ááºááœáá·áºááá¯ááŸá¯áá»á¬ážáá«ááŸááááºá á¡ááœáŸááºážáá»á¬ážá áá°áá¬ážáááºááá¯ááºáá±á¬ SQL á¡áááá¹áá«ááºááœáá·áºááá¯áá»ááºáá»á¬ážááᯠááá°ááẠáááºááá±á¬ááºááŒááºááœááºážááᯠáááºá¡áá¯á¶ážááŒá¯ááá¯ááºáááºá pg_indexes
. á¡áá¬ážáá° á¡áááá¹áá«ááºááœáá·áºááá¯áá»ááºáá»á¬ážááá¯áááºáž á¡ááœááºááá° ááŸá¬ááœá±ááá¯ááºáááº-
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And hereâs the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Superset á¡ááœáŸááºážáá»á¬áž
áááºááẠá¡ááœáŸááºážáá»á¬ážá áœá¬ááŒáá·áº á¡áá¯á¶ážáááºááá¯ááºááŒá®ážá áá áºáá¯ááẠá¡ááŒá¬ážá¡ááœáŸááºážááááºážáá»á¬ážááᯠá¡ááœáŸááºážáá±ážááá·áº áá±á¬áºáá¶áá»á¬ážá á á°áá«áááºáá áºáá¯ááᯠááœáŸááºážáá±ážáááºá áááºážááẠááŸá áºááá¯ááœááºááŒá áºááá¯ááºááẠááá¯á·ááá¯áẠáááŒá áºáá±á¬ááºážááŒá áºááá¯ááºáááºâá á°áá«áááºááºááẠá¡ááœáŸááºáž-áá®ážááá·áºá áááºááºáááºááŒááºážáá»á¬ážááᯠááŒá áºáá±á«áºá á±ááá¯ááºáááºá áááºážááẠáá±á¬ááºážááœááºáá±á¬áºáááºáž áá±áá¬á¡ááœááºáá»á¬ážá áœá¬áá°ááá¯ááºáááºá ááá¯á·ááá¯áẠááá¯áá±á¬ááºážá¡á±á¬ááºááŒá¯áá¯ááºááẠáááºááœááºáá¬ážááá·áº á á°áá«áááºááºááá±ážááŒááºážáá»ááºááᯠá¡áá¯á¶ážáááŒá¯áá±á¬á·áá«á
ááá¯ááá¯á·áá±á¬ á¡ááœáŸááºážááááºážáá»á¬ážá á¡áááá¹áá«ááºááœáá·áºááá¯áá»ááºááᯠá¡ááá¯á¡áá»á±á¬ááºáá¯ááºááẠááá¯á¡ááºáá«áá áááºááẠááŒáá·áº á
áááºááá¯ááºáááºá pg_catalog
.
á¡áá¯á¶ážáááŒá¯áá±á¬ á¡ááœáŸááºážáá»á¬áž
áá±áá¬áá±á·á
áºáá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááá·áº á¡ááá®áá±ážááŸááºážáá»á¬áž ááá¯ážáááºááŒá±á¬ááºážáá²áá¬áááºááŸáá·áºá¡áá»áŸ áááºážááá¯á·á¡áá¯á¶ážááŒá¯ááá·áº áá±ážááœááºážáá»á¬ážááᯠáá¯ááºáá±á¬ááºáá«á á¡á
á±á¬ááá¯ááºážááœáẠááá·áºááœááºážáá¬ážáá±á¬ á¡ááœáŸááºážáá»á¬ážááᯠáááºááá·áºáá±ážááŒááºážááŸá¯á០á¡áá¯á¶ážáááŒá¯áá±á¬á·áá«á á¡ááœáŸááºážáá
áºáá¯ááᯠá
áááºáááºááá¯ááºááá¯ááºážá áááºážááᯠá
á¬áááºážá¡ááºážáááºáá±áá»á¬á á¡ááŸááºá¡áá¬ážááŒá¯ááŒá®áž á
áá
áºáááºááá±á¬ááºááŒááºááœááºážááœáẠááŒá
áºáááºá pg_stat_user_indexes
áááºááá¯ážááᯠáááºááŒááºááá¯ááºáááºá idx_scan
ááá¯ážááœá¬ážáá¬áá±á¬ áááºááŒááºáá
áºáá¯ááŒá
áºáááºá á€áááºááá¯ážááᯠá¡áá»áááºá¡ááá¯ááºážá¡áá¬áá
áºáá¯á¡áá (áá
áºáááŒá±á¬áá«) ááá¯ááŒá±áá¬áá¶ááŒááºážááẠáááºááá·áºááœáŸááºážááááºážáá»á¬ážááᯠá¡áá¯á¶ážáááŒá¯áá² áá»áááºážááœá¬ážááá¯ááºáááºááᯠáá±á¬ááºážá
áœá¬ á¡ááŒá¶áá¬ááºáá±ážáááá·áºáááºá
á€áááºááŸá¬ schema ááŸá á¡ááœáŸááºážá¡á¬ážáá¯á¶ážá áááºááŸáá
áááºáááºá
á
áºáá±ážááŒááºážá¡áá±á¡ááœááºááᯠááá°ááẠá
á¯á¶á
ááºážááŸá¯áá
áºáá¯ááŒá
áºáááºá âpublicâ
:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)
áá±á¬á·áááºáááºážáá»á¬ážááŒáá·áº á¡ááœáŸááºážáá»á¬ážááᯠááŒááºáááºáááºáá±á¬ááºááŒááºážá
á¡ááœáŸááºážáá»á¬ážááẠáá±á¬ááºážáá¬áá±á¬á¡áá«ááœááºá á¥ááá¬á¡á¬ážááŒáá·áº ááŒááºáááºáááºáá±á¬ááºááẠááá¯á¡ááºááŒá®áž ááŒááºáááºáááºáá±á¬ááºááŒááºážááẠá áááºááºááᯠá¡ááŸáááºááŒáŸáá·áºááá¯ááºáááºá á¡ááœáŸááºážááááºážáá»á¬ážáááºáááºáž áá»ááºá á®ážááœá¬ážááá¯ááºáááºá á¡ááœáŸááºážáá±á¬ááºáá»á¬ážááᯠááŒá±á¬ááºážáá²ááŒááºážáááºáááºáž áááºážááᯠááŒááºáááºáááºáá±á¬ááºááẠááá¯á¡ááºááá¯ááºáááºá
á¡ááŒáá¯ááºá¡ááœáŸááºážáááºáá®ážááŸá¯ááᯠááœáá·áºáá«á
PostgreSQL 11 ááœáẠB-Tree á¡ááœáŸááºážááá¯áááºáá®ážááŒááºážááẠáá áºááŒáá¯ááºáááºážááŒá áºáááºá áááºáá®ážááŸá¯áá¯ááºáááºážá ááºááᯠá¡ááŸáááºááŒáŸáá·áºáááºá á¡ááŒáá¯ááºáá¯ááºáá¬ážáá»á¬ážá áœá¬ááᯠá¡áá¯á¶ážááŒá¯ááá¯ááºáááºá ááá¯á·áá±á¬áºá á€ááœá²á·á ááºážááŸá¯ááœá±ážáá»ááºá áá¬áá»á¬ážááᯠááŸááºáááºá áœá¬áááºááŸááºáá¬ážááŒá±á¬ááºáž áá±áá»á¬áá«á á±á
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;
áá°áááºážáááºááá¯ážáá»á¬ážááẠáá±ážáááºááœááºážáááºá á¡áá±á¬ááºážáá¯á¶ážááŸá¬á á€áá¶áá«ááºáá»á¬ážááẠáááá¯áááºáᬠcore á¡áá±á¡ááœááºááŸáá·áºá¡áá° ááá¯ážááá·áºáááºá ááœááºááá¯ááá¯áááºááŸá¯áá«á
áá±á¬ááºáá¶á¡ááœáŸááºážáááºáá®ážááŒááºážá
ááœá±ážáá»ááºááŸá¯ááᯠá¡áá¯á¶ážááŒá¯á áá±á¬ááºáá¶ááœáẠá¡ááœáŸááºážáá
áºáᯠáááºáá®ážááá¯ááºáááºá CONCURRENTLY
ááááºáá±á¬áºááá¯á·ááᯠCREATE INDEX
:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX
á€á¡ááœáŸááºážáááºáá®ážááŸá¯áá¯ááºáá¯á¶ážáá¯ááºáááºážááẠá á¬ážááœá²áá±á«áºááœááºáá±á¬á·áááºáááºáááá¯á¡ááºáá±á¬ááŒá±á¬áá·áº áá¯á¶ááŸááºáá¯á¶á á¶ááŸáá·áº ááœá²ááŒá¬ážáá±á¬ááŒá±á¬áá·áº á á¬áá±ážááŒááºážáá¯ááºáá±á¬ááºááŸá¯áá»á¬ážááᯠááááºááá¯á·áá¬ážááŒááºážáááŸááá«á á¡ááŒá¬ážáá áºáááºááœááºá áááºážááẠá¡áá»áááºááá¯áá°á á¡áááºážá¡ááŒá áºáá»á¬ážááᯠááá¯ááá¯áá¯á¶ážá áœá²áááºá
Postgres ááẠá¡ááœáŸááºážáá»á¬ážáááºáá®ážááŒááºážááŸáá·áº á¡áá°ážááá
á¹á
áááºáá
áºáá¯áá¯ááᯠááŒá±ááŸááºážááẠáááºážáááºážáá»á¬ážá¡ááŒáẠááá·áºá¡ááºááºáá®áá±ážááŸááºážááŒá®ážááœá¬ážáá¬áá«á áá±áá¬áá±á·á
áºááᯠá
á®áá¶ááá·áºááœá²ááẠáááºážáááºážáá»á¬ážá
áœá¬ááᯠáá¶á·ááá¯ážáá±ážáá«áááºá á€á¡ááŒá¶ááŒá¯áá»ááºáá»á¬ážááẠááá·áºáá±ážááŒááºážáá»ááºáá»á¬ážááᯠáá»ááºááŒááºá
áœá¬áááŸááááºááŸáá·áº ááá·áºáá±áá¬áá±á·á
áºááᯠá¡ááá¯ááºážá¡áá¬á¡áá á¡áááºááá·áºááŒá
áºáá±á
á±ááẠáá°áá®áá±ážáááá·áºáááºáᯠáá»áŸá±á¬áºááá·áºáá«áááºá
source: www.habr.com