En el mundo de Postgres, los índices son cruciales para una navegación eficaz a través del almacenamiento de la base de datos (denominado «heap»). Postgres no admite la CLUSTERING para esto, y la arquitectura MVCC provoca que se acumulen muchas versiones de la misma tupla. Por lo tanto, es muy importante saber crear y mantener índices eficaces para apoyar a las aplicaciones.
Les presento algunos consejos para optimizar y mejorar el uso de los índices.
Nota: las consultas que se muestran a continuación funcionan en un .
Uso de índices cubrientes (Covering Indexes)
Analicemos una consulta para extraer direcciones de correo electrónico de usuarios inactivos. En la tabla customer hay una columna active, y la consulta resulta ser sencilla:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN DE CONSULTA
-----------------------------------------------------------
Seq Scan en customer (costo=0.00..16.49 filas=15 ancho=32)
Filtro: (active = 0)
(2 filas) La consulta llama a un escaneo secuencial completo de la tabla. customerCreemos un índice para la columna active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN DE CONSULTA
-----------------------------------------------------------------------------
Escaneo de Índice usando idx_cust1 en customer (costo=0.28..12.29 filas=15 ancho=32)
Condición del Índice: (active = 0)
(2 filas) Ayudó, el escaneo subsiguiente se convirtió en «index scan». Esto significa que Postgres escaneará el índice «idx_cust1», y luego continuará la búsqueda en el heap de la tabla para leer los valores de otras columnas (en este caso, la columna correo electrónico), que son necesarios para la consulta.
En PostgreSQL 11, se introdujeron índices cubrientes. Estos permiten incluir una o varias columnas adicionales en el mismo índice; sus valores se almacenan en el almacenamiento de datos del índice.
Si utilizáramos esta característica y agregáramos el valor del correo electrónico dentro del índice, entonces a Postgres no le necesitaría buscar en el heap de la tabla el valor. correo electrónicoVeamos si esto funcionará:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN DE CONSULTA
----------------------------------------------------------------------------------
Escaneo Solo de Índice usando idx_cust2 en customer (costo=0.28..12.29 filas=15 ancho=32)
Condición del Índice: (active = 0)
(2 filas) «Escaneo Solo de Índice» nos dice que la consulta ahora solo necesita un índice, lo que ayuda a evitar todas las operaciones de entrada/salida en disco para leer el heap de la tabla.
Hoy, los índices de cobertura solo están disponibles para los árboles B. Sin embargo, en este caso, los esfuerzos de mantenimiento serán mayores.
Uso de índices parciales
Los índices parciales solo indexan un subconjunto de las filas de la tabla. Esto permite ahorrar en el tamaño de los índices y realizar escaneos más rápidos.
Supongamos que necesitamos obtener una lista de direcciones de correo electrónico de nuestros clientes en California. La consulta sería la siguiente:
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)¿Qué nos proporcionarán los índices normales:
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) Escaneo address fue reemplazado por un escaneo de índice idx_address1, y luego se escaneó el montón address.
Dado que esta es una consulta común y necesita ser optimizada, podemos usar un índice parcial que solo indexe las filas con direcciones donde 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 tabla address.
Uso de índices multivaluados (Multi-Value Indexes)
Algunas columnas que necesitan ser indexadas pueden no contener un tipo de dato escalar. Tipos de columnas como jsonb, arrays y tsvector pueden contener valores compuestos o múltiples. Si necesita indexar tales columnas, generalmente tendrá que buscar a través de todos los valores individuales en estas columnas.
Intentaremos encontrar los títulos de todas las películas que contengan tomas de doblajes fallidos. En la tabla film hay una columna de texto llamada special_features. Si una película tiene esta "característica especial", entonces la columna contendrá un elemento en forma de matriz de texto Behind The Scenes. Para buscar todas esas películas, necesitamos seleccionar todas las filas con "Behind The Scenes" en cualquier valor de la matriz special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; El operador de contención @> verifica si la parte derecha es un subconjunto de la parte izquierda.
Plan de consulta:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
PLAN DE CONSULTA
-----------------------------------------------------------------
Secuencia de escaneo en film (costo=0.00..67.50 filas=5 ancho=15)
Filtro: (special_features @> '{"Behind The Scenes"}'::text[])
(2 filas)Que solicita un escaneo completo de la tabla con un costo de 67.
Veamos si un índice B-Tree 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"}';
PLAN DE CONSULTA
-----------------------------------------------------------------
Secuencia de escaneo en film (costo=0.00..67.50 filas=5 ancho=15)
Filtro: (special_features @> '{"Behind The Scenes"}'::text[])
(2 filas)El índice ni siquiera fue considerado. 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"}';
PLAN DE CONSULTA
---------------------------------------------------------------------------
Escaneo de heap de bitmap en film (costo=8.04..23.58 filas=5 ancho=15)
Condición de verificación: (special_features @> '{"Behind The Scenes"}'::text[])
-–> Escaneo de índice de bitmap en idx_film2 (costo=0.00..8.04 filas=5 ancho=0)
Condición de índice: (special_features @> '{"Behind The Scenes"}'::text[])
(4 filas)El índice GIN permite la coincidencia de valores individuales con valores compuestos indexados, lo que reduce el costo del plan de consulta más de la mitad.
Eliminamos la duplicación de índices.
Los índices se acumulan con el tiempo, y a veces un nuevo índice puede contener la misma definición que uno de los anteriores. Para obtener definiciones de índices legibles para humanos, se puede utilizar la vista del catálogo pg_indexes. También podrás encontrar fácilmente definiciones duplicadas:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
Y aquí está el resultado cuando se ejecuta en la base de datos pagila de stock:
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 (Superset Indexes)
Es posible que acumules muchos índices, uno de los cuales indexa un subconjunto de columnas que están indexadas por otros índices. Esto puede ser tanto deseable como no — un superconjunto puede resultar en que las consultas se realicen solo a través de los índices, lo cual es bueno, pero también puede ocupar demasiado espacio, o la consulta para la cual se creó este superconjunto ya no se utiliza.
Si necesitas automatizar la identificación de tales índices, puedes comenzar con de la tabla pg_catalog.
Índices no utilizados
A medida que las aplicaciones que utilizan bases de datos evolucionan, también lo hacen las consultas que utilizan. Los índices agregados anteriormente pueden no ser utilizados por ninguna consulta. Cada vez que se escanea un índice, se marca por el administrador de estadísticas, y en la vista del catálogo del sistema pg_stat_user_indexes se puede ver el valor idx_scan, que es un contador acumulativo. Hacer un seguimiento de este valor durante un período de tiempo (digamos, un mes) proporcionará una buena visión de qué índices no se utilizan y pueden ser eliminados.
Aquí hay una consulta para obtener los contadores actuales de escaneo de todos los índices en el esquema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
con una salida como esta:
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 filas)Recreación de índices con menos bloqueos
Con frecuencia es necesario recrear índices, por ejemplo, cuando se expanden en tamaño, y recrearlos puede acelerar el escaneo. También los índices pueden dañarse. Cambiar los parámetros del índice también puede requerir su recreación.
Activamos la creación paralela de índices
En PostgreSQL 11, la creación de un índice B-Tree es concurrente. Para acelerar el proceso de creación, se pueden usar múltiples trabajadores trabajando en paralelo. Sin embargo, asegúrate de que estos parámetros de configuración estén establecidos correctamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Los valores predeterminados son demasiado bajos. Idealmente, estos números deberían aumentarse junto con la cantidad de núcleos del procesador. Lee más en .
Creación de índices en segundo plano
Puedes crear un índice en segundo plano utilizando el parámetro CONCURRENTLY comando CREAR ÍNDICE:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEste procedimiento de creación de índices difiere del habitual en que no requiere bloquear la tabla, lo que significa que no bloquea las operaciones de escritura. Por otro lado, toma más tiempo y consume más recursos.
Postgres ofrece muchas opciones flexibles para la creación de índices y formas de abordar cualquier caso particular, además de proporcionar maneras de gestionar la base de datos en caso de un crecimiento explosivo de tu aplicación. Esperamos que estos consejos te ayuden a hacer que las consultas sean rápidas y que la base esté lista para escalar.
Fuente: habr.com
