Nel mondo di Postgres, gli indici sono estremamente importanti per una navigazione efficace nel database (denominato «heap»). Postgres non supporta la clustering per questo, e l'architettura MVCC porta ad accumulare molte versioni dello stesso tuple. Pertanto, è fondamentale saper creare e mantenere indici efficienti a supporto delle applicazioni.
Vi propongo alcuni consigli per l'ottimizzazione e il miglior utilizzo degli indici.
Nota: le query mostrate di seguito funzionano su un .
Utilizzo degli indici coprenti (Covering Indexes)
Diamo un'occhiata a una query per estrarre indirizzi email per utenti inattivi. Nella tabella customer c'è la colonna active, e la query risulta abbastanza 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 comporta una scansione sequenziale completa della tabella customer. Creiamo un indice sulla 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) Questo ha aiutato, la successiva scansione è diventata un «index scan«. Ciò significa che Postgres scansionerà l'indice «idx_cust1«, per poi continuare a cercare nella heap della tabella per leggere i valori di altre colonne (in questo caso, la colonna email), necessaria alla query.
Nella versione 11 di PostgreSQL sono stati introdotti gli indici coprenti. Questi consentono di includere nell'indice una o più colonne aggiuntive — i loro valori vengono memorizzati nel deposito dati dell'indice.
Se avessimo utilizzato questa funzionalità e aggiunto il valore dell'email all'interno dell'indice, Postgres non avrebbe bisogno di cercare nella heap della tabella il valore email. Vediamo se 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 che ora alla query basta un solo indice, il che aiuta a evitare tutte le operazioni di lettura su disco della heap della tabella.
Oggi gli indici coprenti sono disponibili solo per gli alberi B. Tuttavia, in questo caso, gli sforzi per la manutenzione saranno maggiori.
Utilizzo di indici parziali
Gli indici parziali indicizzano solo un sottoinsieme delle righe della tabella. Questo consente di risparmiare spazio sugli indici e di eseguire scansioni più rapidamente.
Supponiamo che dobbiamo ottenere un elenco degli indirizzi email dei nostri clienti della California. La query sarà:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
che ha un piano di query che prevede la scansione di entrambe le tabelle unite:
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)Cosa ci daranno gli indici normali:
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 è stata sostituita dalla scansione dell'indice idx_address1, e poi è stata scansionata l'heap address.
Poiché questa è una query frequente e deve essere ottimizzata, 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 la tabella address.
Utilizzo di indici multivalore (Multi-Value Indexes)
Alcune colonne da indicizzare potrebbero non contenere un tipo di dato scalare. Tipi di colonne come jsonb, arrays e tsvector possono contenere valori composti o multipli. Se devi indicizzare colonne di questo tipo, di solito è necessario cercare in tutti i singoli valori di queste colonne.
Proviamo a trovare i titoli di tutti i film che contengono tagli di doppiaggi non riusciti. Nella tabella film c'è una colonna di testo chiamata special_features. Se un film ha questa «caratteristica speciale», allora nella colonna è presente un elemento sotto forma di array di testo Behind The Scenes. Per trovare tutti questi film dobbiamo selezionare tutte le righe con «Behind The Scenes» in qualsiasi valore dell'array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; L'operatore di contenimento @> verifica se la parte destra è un sottoinsieme della parte sinistra.
Piano della query:
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 del heap a un costo di 67.
Vediamo se un indice B-tree ci può aiutare:
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 considerato. L'indice B-tree non si accorge 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 il confronto di singoli valori con valori composti indicizzati, quindi il costo del piano di query diminuirà di più della metà.
Eliminiamo la duplicazione degli indici.
Gli indici si accumulano nel tempo e a volte un nuovo indice può contenere la stessa definizione di uno dei precedenti. Per ottenere definizioni di indici in formato leggibile per l'uomo, è possibile utilizzare la vista del catalogo pg_indexes. Sarà inoltre facile trovare definizioni duplicate:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
Ecco il risultato quando eseguito sul database pagila di esempio:
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 righe)
Indici di sovraccarico (Superset Indexes)
Potrebbe succedere che accumuliate molti indici, uno dei quali indicizza un insieme di colonne che sono indicizzate da altri indici. Questo può essere sia desiderabile che non — un sovraccarico può portare a scansionare solo tramite indici, il che è positivo, ma può anche occupare troppo spazio, oppure la query per cui è stato creato questo sovraccarico non è più utilizzata.
Se avete bisogno di automatizzare la definizione di tali indici, potete iniziare da dalla tabella pg_catalog.
Indici non utilizzati
Con lo sviluppo delle applicazioni che utilizzano database, si sviluppano anche le query utilizzate. Gli indici aggiunti in precedenza potrebbero non essere più utilizzati da alcuna query. Ogni volta che viene eseguita una scansione dell'indice, viene registrato dal gestore delle statistiche, e nella vista del catalogo di sistema pg_stat_user_indexes è possibile vedere il valore idx_scan, che funge da contatore cumulativo. Monitorare questo valore nel tempo (diciamo, un mese) fornirà una buona idea di quali indici non vengono utilizzati e possono essere rimossi.
Ecco una query per ottenere i contatori di scansione attuali di tutti gli indici nello schema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
con un output come questo:
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 righe)Ricreazione degli indici con meno blocchi
Spesso gli indici devono essere ricreati, ad esempio, quando aumentano di dimensioni, e la ricreazione può accelerare la scansione. Inoltre, gli indici possono danneggiarsi. La modifica delle impostazioni dell'indice può anche richiedere la sua ricreazione.
Abilitiamo la creazione parallela degli indici
In PostgreSQL 11, la creazione di un indice B-Tree è concorrente. Può essere utilizzato più lavoratori che lavorano in parallelo per velocizzare il processo di creazione. Tuttavia, assicurati che queste impostazioni siano configurate correttamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;I valori predefiniti sono troppo bassi. Idealmente, questi numeri dovrebbero essere aumentati insieme al numero di core del processore. Leggi di più in .
Creazione di indici in background
Puoi creare un indice in background utilizzando l'impostazione CONCURRENTLY comandi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXQuesta procedura di creazione dell'indice si differenzia dalla normale perché non richiede il blocco della tabella, il che significa che non blocca le operazioni di scrittura. D'altra parte, richiede più tempo e consuma più risorse.
Postgres offre molte possibilità flessibili per la creazione di indici e modi per affrontare eventuali casi particolari, oltre a fornire strumenti per gestire il database nel caso di una crescita esplosiva della tua applicazione. Ci auguriamo che questi suggerimenti ti aiutino a rendere le tue query veloci e il database pronto a scalare.
Fonte: habr.com
