In u mondu Postgres, l'indici sò indispensabili per a navigazione efficiente di l'almacenamiento di a basa di dati (chjamatu "heap"). Postgres ùn sustene micca u clustering per questu, è l'architettura MVCC ti face finisce cù parechje versioni di a stessa tupla. Dunque, hè assai impurtante per pudè creà è mantene indici efficaci per sustene l'applicazioni.
Eccu alcuni cunsiglii per ottimisà è migliurà l'usu di l'indici.
Nota: e dumande mostrate quì sottu funzionanu nantu à un mudificatu .
Utilizà l'indici di copertura
Fighjemu una dumanda per estrae l'indirizzi email per l'utilizatori inattivi. Table customer ci hè una culonna active, è a dumanda hè simplice:
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)
A dumanda invoca a sequenza di scansione di a tavola completa customer. Creemu un indice nantu à una colonna 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)
Hè aiutatu, a scansione successiva hè diventata "index scan". Questu significa chì Postgres scannerà l'indici "idx_cust1", è dopu cuntinuà a ricerca in u munzeddu di a tavola per leghje i valori di altre colonne (in questu casu, a colonna email) chì a dumanda hà bisognu.
L'indici di copertura sò introdutti in PostgreSQL 11. Permettenu di include una o più colonne supplementari in l'indici stessu - i so valori sò guardati in u magazzinu di dati di l'indici.
Se avemu apprufittatu di sta funzione è aghjunghje u valore di e-mail in l'indici, allora Postgres ùn hà micca bisognu di cercà a tavola per u valore. email. Videmu s'ellu funziona:
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' ci dice chì a dumanda avà solu bisognu di un indexu, chì aiuta à evità tutte l'I / O di u discu per leghje u munzeddu di a tabella.
L'indici di copertura sò attualmente dispunibili solu per l'arbureti B. Tuttavia, in questu casu, u sforzu di mantenimentu serà più altu.
Utilizà l'Indici Parziali
Indici parziali indici solu un sottumessu di e fila in una tabella. Questu salva a dimensione di l'indici è rende scans più veloce.
Diciamu chì vulemu ottene una lista di l'indirizzi email di i nostri clienti in California. A dumanda sarà cusì:
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)Chì indici ordinali ci daranu:
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)
Scansione address hè statu rimpiazzatu da l'index scan idx_address1e poi scannò u munzeddu address.
Siccomu questu hè una dumanda frequente è deve esse ottimisata, pudemu usà un indice parziale, chì indizia solu quelli fila cù indirizzi in quale u distrittu. ‘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)
Avà a dumanda solu leghje idx_address2 è ùn tocca a tavula address.
Utilizà indici multi-valori
Alcune colonne per esse indexate ùn ponu micca cuntene un tipu di dati scalari. Tipi di colonna cum'è jsonb, arrays и tsvector cuntenenu valori cumposti o multiplici. Sè avete bisognu di indexà tali colonne, di solitu avete da circà tutti i valori individuali in queste colonne.
Pruvemu di truvà i tituli di tutti i filmi chì cuntenenu taglii da piglià senza successu. Table film ci hè una colonna di testu chjamatu special_features. Se u filmu hà sta "proprietà speciale", allora a colonna cuntene l'elementu cum'è un array di testu Behind The Scenes. Per circà tutti i filmi, avemu bisognu di selezziunà tutte e fila cù "Behind The Scenes" quandu qualchissia valori di array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operatore di nidificazione @> verifica se u latu drittu hè un subset di u latu manca.
Pianu di dumanda:
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)Chì dumanda una scansione di heap cumpleta cù un costu di 67.
Videmu se un indice B-tree regulare ci aiuta:
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)L'indici ùn hè statu ancu cunsideratu. L'indici B-tree ùn hè micca cunnisciutu di l'esistenza di elementi individuali in i valori indexati.
Avemu bisognu di un indice 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)L'indice GIN supporta a mappatura di valori unichi contr'à i valori cumposti indiciati, risultatu in un costu di u pianu di dumanda chì hè più di a mità.
Eliminazione di l'indici duplicati
L'indici s'accumulanu cù u tempu, è qualchì volta un novu indice pò cuntene a listessa definizione cum'è unu di i precedenti. Pudete utilizà a vista di u catalogu per uttene definizioni SQL di l'indici umani leghjite. pg_indexes. Pudete ancu truvà facilmente definizioni identiche:
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)
Indici Superset
Puderia succede chì finiscinu cù parechji indici, unu di i quali indexa un superset di colonne chì indexanu altri indici. Questu pò esse o micca desideratu - u superset pò esse risultatu in scans indici solu, chì hè bonu, ma pò piglià troppu spaziu, o a quistione chì u superset era destinatu à ottimisà ùn hè più utilizata.
Sè avete bisognu di automatizà a definizione di tali indici, pudete inizià da a tavula pg_catalog.
Indici inutili
Cume l'applicazioni chì utilizanu basa di dati evoluzione, cusì facenu e dumande chì utilizanu. L'indici aghjuntu prima ùn ponu più esse aduprati da ogni dumanda. Ogni volta chì un indici hè scansatu, hè marcatu da u gestore di statistiche, è in a vista di u catalogu di u sistema pg_stat_user_indexes pudete vede u valore idx_scan, chì hè un contatore cumulativu. U seguimentu di stu valore per un periudu di tempu (per dì un mese) darà una bona idea di quali indici ùn sò micca usati è ponu esse abbandunati.
Eccu una dumanda per ottene i cunti di scansione attuale di tutti l'indici in u 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)Ricustruisce indici cù menu serrature
L'indici sò spessu bisognu di ricustruisce, per esempiu, quandu si gonfianu, è a ricustruzione pò accelerà a scansione. Ancu l'indici ponu esse corrotti. U cambiamentu di i paràmetri di l'indici pò ancu esse bisognu di ricustruisce.
Habilita a creazione d'indici paralleli
In PostgreSQL 11, a creazione di un indice B-Tree hè cuncurrente. Per accelerà u prucessu di creazione, parechji travagliadori paralleli ponu esse utilizati. Tuttavia, assicuratevi chì queste opzioni di cunfigurazione sò stabilite currettamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;I valori predeterminati sò troppu chjuchi. Ideale, questi numeri anu da aumentà cù u numeru di core di processore. Leghjite più in .
Creazione di l'indice di fondo
Pudete creà un indici in u fondu usendu l'opzione CONCURRENTLY squadre CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXQuesta prucedura di creazione d'indici difiere da quella abituale in chì ùn hè micca bisognu di una serratura nantu à a tavula, è per quessa ùn impedisce micca l'operazione di scrittura. Per d 'altra banda, ci vole più tempu è cunsuma più risorse.
Postgres furnisce assai flessibilità per creà indici è modi per risolve ogni casu speciale, è ancu manere di gestisce a basa di dati in casu chì a vostra applicazione cresce esplusivamente. Speremu chì sti cunsiglii vi aiuteranu à ottene e vostre dumande rapidamente è a vostra basa di dati pronta per scala.
Source: www.habr.com