Nel mondo Postgres, gli indici sono essenziali per una navigazione efficiente nell'archivio di un database (chiamato "heap"). Postgres non supporta il clustering per questo e l'architettura MVCC ti fa finire con molte versioni della stessa tupla. Pertanto, è molto importante essere in grado di creare e mantenere indici efficienti per supportare le applicazioni.
Ecco alcuni suggerimenti per ottimizzare e migliorare l'uso degli indici.
Nota: le query mostrate di seguito funzionano su un file non modificato .
Uso degli indici di copertura
Diamo un'occhiata a una richiesta di estrazione di indirizzi email per utenti inattivi. Tavolo customer c'è una colonna active, e la query è semplice:
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)
La query richiama l'intera sequenza di scansione della tabella customer. Creiamo un indice su 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)
Ha aiutato, la scansione successiva si è trasformata in "index scan". Ciò significa che Postgres eseguirà la scansione dell'indice "idx_cust1", quindi continuare la ricerca nell'heap della tabella per leggere i valori di altre colonne (in questo caso, la colonna email) di cui ha bisogno la query.
Gli indici di copertura vengono introdotti in PostgreSQL 11. Consentono di includere una o più colonne aggiuntive nell'indice stesso: i loro valori sono archiviati nell'archivio dati dell'indice.
Se sfruttassimo questa funzionalità e aggiungessimo il valore email all'interno dell'indice, Postgres non avrebbe bisogno di cercare il valore nell'heap della tabella. email. Vediamo se funzionerà:
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 che la query ora necessita solo di un indice, che aiuta a evitare tutto l'I/O del disco per leggere l'heap della tabella.
Gli indici di copertura sono attualmente disponibili solo per alberi B. Tuttavia, in questo caso, lo sforzo di manutenzione sarà maggiore.
Utilizzo di indici parziali
Gli indici parziali indicizzano solo un sottoinsieme delle righe in una tabella. Ciò consente di risparmiare la dimensione degli indici e rende le scansioni più veloci.
Supponiamo di voler ottenere un elenco degli indirizzi e-mail dei nostri clienti in California. La richiesta sarà così:
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)Quali indici ordinari ci daranno:
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 è stato sostituito dalla scansione dell'indice idx_address1e poi ha scansionato l'heap address.
Poiché si tratta di una query frequente e da ottimizzare, possiamo utilizzare un indice parziale, che indicizza solo quelle righe con indirizzi in cui il distretto ‘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)
Ora la query legge solo idx_address2 e non tocca il tavolo address.
Utilizzo di indici multivalore
Alcune colonne da indicizzare potrebbero non contenere un tipo di dati scalare. Tipi di colonne come jsonb, arrays и tsvector contengono valori compositi o multipli. Se devi indicizzare tali colonne, di solito devi cercare tra tutti i singoli valori in quelle colonne.
Proviamo a trovare i titoli di tutti i film contenenti tagli di riprese non riuscite. Tavolo film c'è una colonna di testo chiamata special_features. Se il film ha questa "proprietà speciale", la colonna contiene l'elemento come un array di testo Behind The Scenes. Per cercare tutti questi film, dobbiamo selezionare tutte le righe con "Dietro le quinte" quando qualsiasi valori di matrice special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operatore di nidificazione @> controlla se il lato destro è un sottoinsieme del lato sinistro.
Richiesta piano:
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)Che richiede una scansione completa dell'heap con un costo di 67.
Vediamo se un normale indice B-tree 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'indice non è stato nemmeno preso in considerazione. L'indice B-tree non è a conoscenza dell'esistenza di singoli elementi nei valori indicizzati.
Abbiamo bisogno 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 la mappatura di valori singoli rispetto a valori compositi indicizzati, con conseguente costo del piano di query più che dimezzato.
Eliminare gli indici duplicati
Gli indici si accumulano nel tempo e talvolta un nuovo indice può contenere la stessa definizione di uno dei precedenti. È possibile utilizzare la vista del catalogo per ottenere definizioni SQL leggibili dall'utente degli indici. pg_indexes. Puoi anche trovare 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
Può capitare di ritrovarsi con molti indici, uno dei quali indicizza un superset di colonne che indicizzano altri indici. Ciò può essere desiderabile o meno: il superset può comportare scansioni solo indice, il che è positivo, ma potrebbe occupare troppo spazio o la query che il superset doveva ottimizzare non viene più utilizzata.
Se hai bisogno di automatizzare la definizione di tali indici, puoi iniziare con dal tavolo pg_catalog.
Indici inutilizzati
Man mano che le applicazioni che utilizzano i database si evolvono, anche le query che utilizzano si evolvono. Gli indici aggiunti in precedenza non possono più essere utilizzati da alcuna query. Ogni volta che un indice viene analizzato, viene contrassegnato dal gestore delle statistiche e nella vista del catalogo di sistema pg_stat_user_indexes puoi vedere il valore idx_scan, che è un contatore cumulativo. Tracciare questo valore per un periodo di tempo (diciamo un mese) darà una buona idea di quali indici non vengono utilizzati e potrebbero essere eliminati.
Ecco una query per ottenere i conteggi di scansione correnti di tutti gli indici nello 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)Ricostruzione degli indici con meno blocchi
Gli indici spesso devono essere ricostruiti, ad esempio quando si gonfiano, e la ricostruzione può velocizzare la scansione. Anche gli indici possono essere danneggiati. La modifica dei parametri dell'indice potrebbe anche richiedere la sua ricostruzione.
Abilita la creazione parallela dell'indice
In PostgreSQL 11, la creazione di un indice B-Tree è simultanea. Per accelerare il processo di creazione, è possibile utilizzare diversi worker paralleli. Tuttavia, assicurati che queste opzioni di configurazione siano impostate correttamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;I valori predefiniti sono troppo piccoli. Idealmente, questi numeri dovrebbero aumentare insieme al numero di core del processore. Leggi di più in .
Creazione dell'indice di sfondo
È possibile creare un indice in background utilizzando l'opzione CONCURRENTLY comandi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXQuesta procedura di creazione dell'indice differisce dalla solita in quanto non richiede un lock sulla tabella, e quindi non blocca le operazioni di scrittura. D'altra parte, richiede più tempo e consuma più risorse.
Postgres offre molta flessibilità per la creazione di indici e modi per risolvere eventuali casi speciali, nonché modi per gestire il database nel caso in cui la tua applicazione cresca in modo esponenziale. Ci auguriamo che questi suggerimenti ti aiutino a ottenere rapidamente le tue query e il tuo database pronto per la scalabilità.
Fonte: habr.com