En el mundo de Postgres, los índices son esenciales para la navegación eficiente del almacenamiento de una base de datos (llamado "montón"). Postgres no admite la agrupación en clústeres, y la arquitectura MVCC hace que termine con muchas versiones de la misma tupla. Por lo tanto, es muy importante poder crear y mantener índices eficientes para dar soporte a las aplicaciones.
Estos son algunos consejos para optimizar y mejorar el uso de los índices.
Nota: las consultas que se muestran a continuación funcionan en un modelo sin modificar .
Uso de índices de cobertura
Veamos una solicitud para extraer direcciones de correo electrónico para usuarios inactivos. Mesa customer hay una columna active, y la consulta es simple:
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 secuencia de exploración de la tabla completa. customer. Vamos a crear un índice 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)
Ayudó, el escaneo posterior se convirtió en "index scan". Esto significa que Postgres escaneará el índice "idx_cust1", y luego continúe buscando en el montón de la tabla para leer los valores de otras columnas (en este caso, la columna email) que necesita la consulta.
Los índices de cobertura se introducen en PostgreSQL 11. Le permiten incluir una o más columnas adicionales en el índice mismo: sus valores se almacenan en el almacén de datos del índice.
Si aprovechamos esta característica y agregamos el valor del correo electrónico dentro del índice, entonces Postgres no necesitaría buscar el valor en el montón de la tabla. email. Veamos si esto funcionará:
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' nos dice que la consulta ahora solo necesita un índice, lo que ayuda a evitar que toda la E/S del disco lea el montón de la tabla.
Los índices de cobertura actualmente solo están disponibles para árboles B. Sin embargo, en este caso, el esfuerzo de mantenimiento será mayor.
Uso de índices parciales
Los índices parciales indexan solo un subconjunto de las filas de una tabla. Esto ahorra el tamaño de los índices y hace que los escaneos sean más rápidos.
Digamos que queremos obtener una lista de las direcciones de correo electrónico de nuestros clientes en California. La solicitud 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)Lo que nos darán los índices ordinarios:
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 ha sido reemplazado por escaneo de índice idx_address1y luego escaneó el montón address.
Dado que esta es una consulta frecuente y debe optimizarse, podemos usar un índice parcial, que indexa solo aquellas filas con direcciones en las que el distrito ‘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)
Ahora la consulta solo lee idx_address2 y no toca la mesa address.
Uso de índices de valores múltiples
Algunas columnas que se van a indexar pueden no contener un tipo de datos escalares. Tipos de columna como jsonb, arrays и tsvector contienen valores compuestos o múltiples. Si necesita indexar dichas columnas, generalmente debe buscar en todos los valores individuales en esas columnas.
Tratemos de encontrar los títulos de todas las películas que contengan cortes de tomas fallidas. Mesa film hay una columna de texto llamada special_features. Si la película tiene esta "propiedad especial", la columna contiene el elemento como una matriz de texto Behind The Scenes. Para buscar todas esas películas, debemos seleccionar todas las filas con "Detrás de escena" cuando cualquier valores de matriz special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operador de anidamiento @> comprueba si el lado derecho es un subconjunto del lado izquierdo.
Solicitar plano:
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 un escaneo de montón completo con un costo de 67.
Veamos si un índice de árbol B regular nos ayuda:
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)Ni siquiera se consideró el índice. El índice B-tree no tiene conocimiento de la existencia de elementos individuales en los valores indexados.
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)El índice GIN admite el mapeo de valores únicos frente a valores compuestos indexados, lo que da como resultado un costo del plan de consulta que se reduce a más de la mitad.
Deshacerse de los índices duplicados
Los índices se acumulan con el tiempo y, a veces, un nuevo índice puede contener la misma definición que uno de los anteriores. Puede utilizar la vista de catálogo para obtener definiciones de índices SQL legibles por humanos. pg_indexes. También puede encontrar fácilmente definiciones idénticas:
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
Puede suceder que termine con muchos índices, uno de los cuales indexa un superconjunto de columnas que indexan otros índices. Esto puede ser deseable o no: el superconjunto puede dar como resultado exploraciones de solo índice, lo cual es bueno, pero puede ocupar demasiado espacio, o la consulta que el superconjunto pretendía optimizar ya no se utiliza.
Si necesita automatizar la definición de dichos índices, puede comenzar con de la mesa pg_catalog.
Índices no utilizados
A medida que evolucionan las aplicaciones que utilizan bases de datos, también lo hacen las consultas que utilizan. Los índices agregados anteriormente ya no pueden ser utilizados por ninguna consulta. Cada vez que se escanea un índice, el administrador de estadísticas lo marca y en la vista de catálogo del sistema pg_stat_user_indexes puedes ver el valor idx_scan, que es un contador acumulativo. El seguimiento de este valor durante un período de tiempo (digamos un mes) dará una buena idea de qué índices no se están utilizando y podrían eliminarse.
Aquí hay una consulta para obtener los recuentos de escaneo actuales de todos los índices en el 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)Reconstruir índices con menos bloqueos
Los índices a menudo necesitan reconstruirse, por ejemplo, cuando se hinchan, y la reconstrucción puede acelerar el escaneo. También los índices pueden corromperse. Cambiar los parámetros del índice también puede requerir reconstruirlo.
Habilitar la creación de índices paralelos
En PostgreSQL 11, la creación de un índice B-Tree es simultánea. Para acelerar el proceso de creación, se pueden utilizar varios trabajadores paralelos. Sin embargo, asegúrese de que estas opciones de configuración estén configuradas correctamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Los valores predeterminados son demasiado pequeños. Idealmente, estos números deberían aumentar junto con la cantidad de núcleos de procesador. Leer más en .
Creación de índice de fondo
Puede crear un índice en segundo plano usando la opción CONCURRENTLY команды CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEste procedimiento de creación de índices difiere del habitual en que no requiere un bloqueo en la tabla y, por lo tanto, no bloquea las operaciones de escritura. Por otro lado, lleva más tiempo y consume más recursos.
Postgres brinda mucha flexibilidad para crear índices y formas de resolver casos especiales, así como formas de administrar la base de datos en caso de que su aplicación crezca de manera explosiva. Esperamos que estos consejos lo ayuden a obtener sus consultas rápidamente y su base de datos esté lista para escalar.
Fuente: habr.com