
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
