No mundo de Postgres, os índices son fundamentais para navegar de forma eficiente polo almacenamento da base de datos (chamado montón). Postgres non admite a agrupación para iso, e a arquitectura MVCC fai que acabes con moitas versións da mesma tupla. Polo tanto, é moi importante poder crear e manter índices eficaces para soportar aplicacións.
Traigo á súa atención algúns consellos para optimizar e mellorar o uso dos índices.
Nota: as consultas que se mostran a continuación funcionan sen modificar .
Usando índices de cobertura
Vexamos unha solicitude para recuperar enderezos de correo electrónico para usuarios inactivos. Na táboa customer hai unha columna active, e a consulta é sinxela:
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 consulta chama á secuencia de exploración da táboa completa customer. Imos crear un índice na 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)
Axudado, a exploración posterior converteuse en "index scan". Isto significa que Postgres rastrexará o índice "idx_cust1", e despois continúa buscando no montón da táboa para ler os valores doutras columnas (neste caso, a columna email) que precisa a solicitude.
PostgreSQL 11 introduciu índices de cobertura. Permítenche incluír unha ou máis columnas adicionais no propio índice: os seus valores gárdanse no almacén de datos do índice.
Se usamos esta función e engadimos o valor de correo electrónico dentro do índice, entón Postgres non necesitaría buscar o valor no montón da táboa. email. A ver se isto 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" indícanos que a consulta agora só necesita o índice, o que axuda a evitar todas as E/S do disco para ler o montón da táboa.
Hoxe, os índices de cobertura só están dispoñibles para árbores B. Non obstante, neste caso o esforzo de mantemento será maior.
Usando índices parciais
Os índices parciais indexan só un subconxunto das filas da táboa. Isto permítelle gardar o tamaño do índice e realizar escaneos máis rápido.
Digamos que necesitamos obter unha lista de enderezos de correo electrónico para os nosos clientes en California. A solicitude será así:
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)Que índices regulares nos darán:
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)
Escanear address substituíuse pola exploración do índice idx_address1, e despois escaneouse o montón address.
Dado que esta é unha consulta frecuente e precisa ser optimizada, podemos utilizar un índice parcial, que indexa só aquelas filas con enderezos nos que a área ‘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)
Agora a solicitude só le idx_address2 e non toca a mesa address.
Usando índices de varios valores
Algunhas columnas que deben ser indexadas poden non conter un tipo de datos escalar. Tipos de columna como jsonb, arrays и tsvector conteñen significados compostos ou múltiples. Se precisas indexar tales columnas, normalmente tes que buscar todos os valores individuais nesas columnas.
Tentemos buscar os títulos de todas as películas que conteñan clips de tomas sen éxito. Na táboa film hai unha columna de texto chamada special_features. Se unha película ten esta "propiedade especial", entón a columna contén un elemento en forma de matriz de texto Behind The Scenes. Para buscar todas as películas deste tipo, temos que seleccionar todas as filas con "Detrás das cámaras" en calquera valores de matriz special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operador de contención @> comproba se o lado dereito é un subconxunto do lado esquerdo.
Plan de consulta:
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 solicita unha exploración de pila completa cun custo de 67.
A ver se un índice de árbore B normal nos axuda:
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)O índice nin sequera foi considerado. O índice B-tree non ten idea da existencia de elementos individuais nos valores que indexa.
Necesitamos un índice 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)O índice GIN admite a asignación de valores individuais a valores compostos indexados, o que supón máis da metade do custo do plan de consulta.
Desfacerse dos índices duplicados
Os índices acumúlanse co paso do tempo e ás veces un novo índice pode conter a mesma definición que un dos anteriores. Pode utilizar a vista de catálogo para obter definicións de índice SQL lexibles por humanos pg_indexes. Tamén podes atopar facilmente as mesmas definicións:
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)
Índices de superconjunto
Pode ocorrer que acumule moitos índices, un dos cales indexa un superconxunto de columnas que outros índices indexan. Isto pode ser desexable ou non: un superconxunto pode resultar nunha exploración só de índice, o que é bo, pero pode ocupar demasiado espazo ou a consulta que se pretendía optimizar o superconxunto xa non está en uso.
Se precisa automatizar a definición de tales índices, pode comezar por da mesa pg_catalog.
Índices non utilizados
A medida que evolucionan as aplicacións que usan bases de datos, tamén evolucionan as consultas que utilizan. Os índices engadidos anteriormente xa non poden ser usados por ningunha consulta. Cada vez que se escanea un índice, o xestor de estatísticas anota e na vista do catálogo do sistema pg_stat_user_indexes podes ver o valor idx_scan, que é un contador acumulativo. O seguimento deste valor durante un período de tempo (por exemplo, un mes) dará unha boa idea de cales son os índices que non se están a usar e que se poden eliminar.
Aquí tes unha solicitude para obter os recontos actuais de exploración de todos os índices do 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)Reconstruíndo índices con menos bloqueos
Moitas veces hai que volver a crear índices, por exemplo cando se inchan, e a súa recreación pode acelerar a exploración. Os índices tamén poden corromperse. O cambio de parámetros do índice tamén pode requirir a súa recreación.
Activa a creación de índices paralelos
En PostgreSQL 11, a creación de índices B-Tree é simultánea. Para acelerar o proceso de creación pódense utilizar varios traballadores paralelos. Non obstante, asegúrate de que estes axustes de configuración estean configurados correctamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Os valores predeterminados son demasiado pequenos. Idealmente, estes números deberían aumentar xunto co número de núcleos de procesador. Ler máis en .
Creación de índice de fondo
Podes crear o índice en segundo plano usando a opción CONCURRENTLY ordes CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEste procedemento de creación de índices difire do habitual en que non require bloquear a táboa e, polo tanto, non bloquea as operacións de escritura. Por outra banda, leva máis tempo e consome máis recursos.
Postgres ofrece moitas opcións flexibles para crear índices e solucións para calquera caso especial, e tamén ofrece formas de xestionar a base de datos se a súa aplicación crece de forma explosiva. Agardamos que estes consellos che axuden a facer que as túas consultas sexan rápidas e que a túa base de datos estea lista para escalar.
Fonte: www.habr.com