Nel mondo di Postgres, gli indici sono estremamente importanti per una navigazione efficace all'interno dello storage del database (chiamato «heap»). Postgres non supporta la clusterizzazione per questo, e l'architettura MVCC porta a un accumulo di molte versioni dello stesso tuple. Quindi, è fondamentale sapere come creare e mantenere indici efficaci per supportare le applicazioni.
Ecco alcuni suggerimenti per ottimizzare e migliorare l'uso degli indici.
Nota: le query mostrate di seguito funzionano su un .
Utilizzo di indici coprenti (Covering Indexes)
Analizziamo una query per estrarre gli indirizzi email degli utenti inattivi. Nella tabella customer c'è una colonna active, e la query risulta 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 per la 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 funzionato, la scansione successiva è diventata un «index scan«. Questo significa che Postgres scansionerà l'indice «idx_cust1«, e poi continuerà a cercare nello heap della tabella per leggere i valori delle altre colonne (in questo caso, la colonna email), necessaria alla query.
In PostgreSQL 11 sono stati introdotti gli indici coprenti. Essi consentono di includere nel proprio indice una o più colonne aggiuntive — i loro valori sono conservati nella storage del dato dell'indice.
Se avessimo usato questa possibilità e aggiunto il valore dell'email all'interno dell'indice, non sarebbe stato necessario a Postgres cercare nello 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 unicamente l'indice, il che aiuta a evitare tutte le operazioni di I/O su disco per leggere l'heap della tabella.
Oggi gli indici coprenti sono disponibili solo per gli alberi-B. Tuttavia, in questo caso, gli sforzi di mantenimento saranno maggiori.
Utilizzo di indici parziali
Gli indici parziali indicizzano solo un sottoinsieme delle righe della tabella. Questo consente di risparmiare dimensioni sugli indici e di eseguire scansioni più rapide.
Supponiamo di voler ottenere un elenco di indirizzi email dei nostri clienti in California. La query sarà la seguente:
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 comporta la scansione di entrambe le tabelle coinvolte:
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 ordinari:
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) La 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 le righe con indirizzi nel quale 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 a valori multipli (Multi-Value Indexes)
Alcune colonne da indicizzare potrebbero non contenere un tipo di dati scalare. Tipi di colonne come jsonb, arrays e tsvector possono contenere valori compositi o multipli. Se è necessario indicizzare tali colonne, di solito è necessario cercare tra tutti i singoli valori in queste colonne.
Proviamo a trovare i titoli di tutti i film che contengono estratti da doppiaggi falliti. Nella tabella film c'è una colonna di testo chiamata special_features. Se un film ha questa "caratteristica speciale", allora nella colonna c'è un elemento sotto forma di array di testo Behind The Scenes. Per trovare tutti i film di questo tipo, dobbiamo selezionare tutte le righe con "Behind The Scenes" per qualsiasi valori dell'array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; L'operatore di contenimento @> controlla se il lato destro è un sottoinsieme del lato sinistro.
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 della heap con un costo di 67.
Vediamo se ci aiuta un indice 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)L'indice non è stato nemmeno considerato. L'indice B-tree non si rende conto 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 compositi indicizzati, riducendo così il costo del piano della query di oltre la metà.
Eliminiamo la duplicazione degli indici
Gli indici si accumulano nel tempo, e talvolta un nuovo indice può avere la stessa definizione di uno precedente. Per ottenere definizioni SQL leggibili dall'uomo degli indici, è possibile utilizzare la vista di catalogo pg_indexes. Potrai 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;
Ecco il risultato quando eseguito sul database pagila di base:
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 di insieme superiore (Superset Indexes)
Può accadere che accumuli molti indici, uno dei quali indicizza un insieme di colonne che altri indici indicizzano. Questo può essere sia desiderabile che indesiderabile: un insieme superiore può portare a scan solo attraverso gli indici, il che è positivo, ma può occupare troppo spazio, oppure una query per la quale era stato progettato l'insieme potrebbe già non essere utilizzata.
Se è necessario automatizzare l'identificazione di tali indici, è possibile iniziare da dalla tabella pg_catalog.
Indici inutilizzati
Con l'evoluzione delle applicazioni che utilizzano i database, anche le query utilizzate si evolvono. Gli indici aggiunti in precedenza potrebbero non essere più utilizzati da alcuna query. Durante ogni scansione dell'indice, viene segnato dal gestore delle statistiche e nella vista del catalogo di sistema pg_stat_user_indexes è possibile vedere il valore idx_scan, che è un contatore cumulativo. Monitorare questo valore per un certo periodo di tempo (ad esempio un mese) darà una buona idea di quali indici non vengono utilizzati e possono essere rimossi.
Ecco una query per ottenere i contatori attuali di scansione 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)Ricreazione degli indici con minori blocchi
Spesso è necessario ricreare gli indici, ad esempio quando aumentano di dimensioni, e la ricreazione può accelerare le scansioni. Inoltre, gli indici possono danneggiarsi. La modifica dei parametri dell'indice può anche richiedere la sua ricreazione.
Abilitiamo la creazione parallela degli indici
In PostgreSQL 11, la creazione di indici B-Tree è concorrente. Per accelerare il processo di creazione, possono essere utilizzati più worker in parallelo. Assicurati però che questi parametri di configurazione siano impostati correttamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;I valori di default sono troppo bassi. Idealmente, questi numeri dovrebbero essere aumentati insieme al numero di core del processore. Ulteriori informazioni possono essere trovate in .
Creazione in background degli indici
Puoi creare un indice in background utilizzando il parametro CONCURRENTLY del comando. CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXQuesta procedura di creazione dell'indice è diversa dalla normale in quanto non richiede il blocco della tabella, e quindi non blocca le operazioni di scrittura. D'altra parte, richiede più tempo e consuma più risorse.
Postgres offre molte opzioni flessibili per la creazione di indici e modi per affrontare casi particolari, nonché fornisce strumenti per gestire il database in caso di esplosivo aumento della tua applicazione. Speriamo che questi consigli ti aiutino a rendere le query veloci e il database pronto per scalare.
Fonte: habr.com
