Al món de Postgres, els índexs són essencials per a una navegació eficient de l'emmagatzematge de la base de dades (anomenat "munt"). Postgres no admet l'agrupació per a això, i l'arquitectura MVCC fa que acabeu amb moltes versions de la mateixa tupla. Per tant, és molt important poder crear i mantenir índexs eficients per donar suport a les aplicacions.
Aquests són alguns consells per optimitzar i millorar l'ús dels índexs.
Nota: les consultes que es mostren a continuació funcionen sense modificar .
Ús d'índexs de cobertura
Vegem una sol·licitud per extreure adreces de correu electrònic per a usuaris inactius. Taula customer hi ha una columna active, i la consulta és senzilla:
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 consulta invoca la seqüència d'exploració completa de la taula customer. Creem un índex en una columna 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)
Va ajudar, l'escaneig posterior es va convertir en "index scan". Això vol dir que Postgres escanejarà l'índex "idx_cust1", i després continueu cercant el munt de taula per llegir els valors d'altres columnes (en aquest cas, la columna email) que necessita la consulta.
Els índexs de cobertura s'introdueixen a PostgreSQL 11. Us permeten incloure una o més columnes addicionals al propi índex: els seus valors s'emmagatzemen al magatzem de dades de l'índex.
Si utilitzem aquesta funció i afegim el valor del correu electrònic dins de l'índex, aleshores Postgres no hauria de cercar el valor a la pila de la taula. email. A veure si això funciona:
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' ens diu que la consulta ara només necessita un índex, la qual cosa ajuda a evitar que totes les E/S de disc puguin llegir l'emmagatzematge de la taula.
Actualment, els índexs de cobertura només estan disponibles per als arbres B. Tanmateix, en aquest cas, l'esforç de manteniment serà més gran.
Ús d'índexs parcials
Els índexs parcials només indexen un subconjunt de files d'una taula. Això estalvia la mida dels índexs i fa que les exploracions siguin més ràpides.
Suposem que volem obtenir una llista de les adreces de correu electrònic dels nostres clients a Califòrnia. La petició serà així:
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)Quins índexs ordinaris ens donaran:
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)
Escaneig address s'ha substituït per l'escaneig d'índex idx_address1i després va escanejar el munt address.
Com que aquesta és una consulta freqüent i cal optimitzar-la, podem utilitzar un índex parcial, que indexa només aquelles files amb adreces en què el districte ‘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)
Ara la consulta només es llegeix idx_address2 i no toca la taula address.
Ús d'índexs de valors múltiples
És possible que algunes columnes que cal indexar no continguin un tipus de dades escalar. Tipus de columnes com jsonb, arrays и tsvector continguin valors compostos o múltiples. Si necessiteu indexar aquestes columnes, normalment heu de cercar tots els valors individuals d'aquestes columnes.
Intentem trobar els títols de totes les pel·lícules que contenen retalls de preses sense èxit. Taula film hi ha una columna de text anomenada special_features. Si la pel·lícula té aquesta "propietat especial", aleshores la columna conté l'element com a matriu de text Behind The Scenes. Per cercar totes aquestes pel·lícules, hem de seleccionar totes les files amb "Behind The Scenes" quan qualsevol valors de matriu special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operador de nidificació @> comprova si el costat dret és un subconjunt del costat esquerre.
Sol·licitud de pla:
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)Que sol·licita una exploració de pila completa amb un cost de 67.
Vegem si un índex normal d'arbre B ens ajuda:
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'índex ni tan sols es va considerar. L'índex de l'arbre B no és conscient de l'existència d'elements individuals en els valors indexats.
Necessitem un índex 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'índex GIN admet mapejar valors únics amb valors compostos indexats, donant lloc a un cost del pla de consultes que es redueix a més de la meitat.
Desfer-se dels índexs duplicats
Els índexs s'acumulen amb el temps i, de vegades, un índex nou pot contenir la mateixa definició que un dels anteriors. Podeu utilitzar la vista de catàleg per obtenir definicions SQL d'índexs llegibles pels humans. pg_indexes. També podeu trobar fàcilment definicions idèntiques:
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)
Índexs de superconjunt
Pot passar que acabis amb molts índexs, un dels quals indexa un superconjunt de columnes que indexen altres índexs. Això pot ser o no desitjable: el superconjunt pot donar lloc a exploracions només d'índex, la qual cosa és bo, però pot ocupar massa espai, o la consulta que el superconjunt volia optimitzar ja no s'utilitza.
Si necessiteu automatitzar la definició d'aquests índexs, podeu començar de la taula pg_catalog.
Índexs no utilitzats
A mesura que les aplicacions que utilitzen bases de dades evolucionen, també ho fan les consultes que utilitzen. Els índexs afegits anteriorment ja no poden ser utilitzats per cap consulta. Cada vegada que s'escaneja un índex, és marcat pel gestor d'estadístiques i a la vista del catàleg del sistema pg_stat_user_indexes pots veure el valor idx_scan, que és un comptador acumulatiu. El seguiment d'aquest valor durant un període de temps (per exemple, un mes) donarà una bona idea de quins índexs no s'estan utilitzant i es podrien deixar de banda.
Aquí hi ha una consulta per obtenir els recomptes d'exploració actuals de tots els índexs de l'esquema ‘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)Reconstruint índexs amb menys bloquejos
Sovint s'han de reconstruir els índexs, per exemple, quan s'inflen, i la reconstrucció pot accelerar l'exploració. També els índexs es poden danyar. Canviar els paràmetres de l'índex també pot requerir la reconstrucció.
Activa la creació d'índexs paral·lels
A PostgreSQL 11, la creació d'un índex B-Tree és concurrent. Per accelerar el procés de creació, es poden utilitzar diversos treballadors paral·lels. Tanmateix, assegureu-vos que aquestes opcions de configuració estiguin configurades correctament:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Els valors per defecte són massa petits. Idealment, aquests números haurien d'augmentar juntament amb el nombre de nuclis de processador. Llegeix més a .
Creació d'índex de fons
Podeu crear un índex en segon pla mitjançant l'opció CONCURRENTLY ordres CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXAquest procediment de creació d'índex difereix de l'habitual perquè no requereix un bloqueig a la taula i, per tant, no bloqueja les operacions d'escriptura. D'altra banda, requereix més temps i consumeix més recursos.
Postgres ofereix molta flexibilitat per crear índexs i maneres de resoldre casos especials, així com maneres de gestionar la base de dades en cas que la vostra aplicació creixi de manera explosiva. Esperem que aquests consells us ajudin a fer les vostres consultes ràpidament i a preparar la vostra base de dades per escalar.
Font: www.habr.com