En el mundo de Postgres, los índices son cruciales para navegar un repositorio de base de datos de manera eficiente (llamado montón, montón). Postgres no admite la agrupación en clúster, y la arquitectura MVCC hace que acumule muchas versiones de la misma tupla. Por lo tanto, es muy importante poder crear y mantener índices efectivos para admitir aplicaciones.
Aquí hay algunos consejos para optimizar y mejorar el uso de índices.
Nota: las consultas que se muestran a continuación funcionan en una base de datos de muestra de pagila no modificada.Usar índices de cobertura
Revisemos la solicitud para recuperar direcciones de correo electrónico para usuarios inactivos. La tabla de
customer tiene una columna
active , y la solicitud es simple:
pagila=
La consulta invoca la secuencia completa de escaneo de la tabla del
customer . Creemos un índice para la columna
active :
pagila=
Ayudó, la exploración posterior se convirtió en una "
index scan ". Esto significa que Postgres escaneará el índice
idx_cust1 y luego continuará buscando en el montón de la tabla para leer los valores de las otras columnas (en este caso, la columna de
email ) que necesita la consulta.
PostgreSQL 11 introdujo índices de cobertura. Le permiten incluir una o más columnas adicionales en el propio índice; sus valores se almacenan en el almacén de datos del índice.
Si utilizamos esta función y agregamos un valor de correo electrónico dentro del índice, Postgres no necesitaría buscar el valor de
email en el montón de la tabla. Veamos si esto funciona:
pagila=
"
Index Only Scan " nos dice que la consulta ahora solo necesita un índice, lo que ayuda a evitar que todas las E / S de disco lean el montón de tabla.
Hoy en día, los índices de cobertura solo están disponibles para los árboles B. Sin embargo, en este caso, los esfuerzos de escolta serán mayores.
Usando índices parciales
Los índices parciales solo indexan un subconjunto de las filas de una tabla. Esto ahorra el tamaño de los índices y escaneos más rápidos.
Supongamos que necesitamos obtener una lista de direcciones de correo electrónico de nuestros clientes de 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=
Qué índices ordinarios nos darán:
pagila=
El escaneo de la
address fue reemplazado por el
idx_address1 índice
idx_address1 , y luego se escaneó el montón de
address .
Dado que esta es una consulta frecuente y necesita ser optimizada, podemos usar un índice parcial que indexa solo aquellas filas con direcciones en las que la
'California' :
pagila=
Ahora la solicitud solo lee
idx_address2 y no toca la tabla de
address .
Uso de índices de valores múltiples
Es posible que algunas columnas que deban indexarse no contengan un tipo de datos escalar.
jsonb tipos de
jsonb como
jsonb ,
arrays y
tsvector contienen valores múltiples o múltiples. Si necesita indexar dichas columnas, generalmente tiene que buscar todos los valores individuales en estas columnas.
Intentemos encontrar los nombres de todas las películas que contienen cortes de tomas fallidas. La tabla de
film tiene una columna de texto llamada
special_features . Si la película tiene esta "propiedad especial", entonces la columna contiene un elemento en forma de matriz de texto
Behind The Scenes . Para buscar todas esas películas, debemos seleccionar todas las filas con "Detrás de escena" para
cualquier valor de la matriz
special_features :
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
El operador de contención
@> verifica si el lado derecho es un subconjunto del lado izquierdo.
Plan de solicitud:
pagila=
Que solicita un análisis dinámico completo con un costo de 67.
Veamos si el índice B-tree normal nos ayuda:
pagila=
El índice ni siquiera fue considerado. El índice del árbol B no tiene conocimiento de la existencia de elementos individuales en los valores indexados.
Necesitamos un índice GIN.
pagila=
GIN-index admite la comparación de valores individuales con valores compuestos indexados, como resultado, el costo del plan de consulta se reduce en más de la mitad.
Deshágase de los índices duplicados
Los índices se acumulan con el tiempo y, a veces, un índice nuevo puede contener la misma definición que uno de los anteriores. Para obtener definiciones de índices de lectura humana de los índices, puede usar la vista de catálogo
pg_indexes . También puede encontrar fácilmente las mismas definiciones:
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=
Índices de superconjunto
Puede suceder que acumule muchos índices, uno de los cuales indexa un subconjunto de las columnas que indexan otros índices. Esto puede ser deseable o no: un superconjunto solo puede escanear por índice, lo cual es bueno, pero puede ocupar demasiado espacio, o la consulta para la que se pretendía optimizar este superconjunto ya no se usa.
Si necesita automatizar la definición de dichos índices, puede comenzar con
pg_index desde la tabla
pg_catalog .
Índices no utilizados
A medida que se desarrollan las aplicaciones que usan bases de datos, también lo hacen las consultas que usan. Los índices agregados anteriormente ya no pueden ser utilizados por ninguna consulta. Cada vez que se escanea el índice, lo marca el administrador de estadísticas y, en la
pg_stat_user_indexes catálogo del sistema
pg_stat_user_indexes puede ver el valor
idx_scan , que es un contador acumulativo. El seguimiento de este valor durante un período de tiempo (por ejemplo, un mes) dará una buena idea de qué índices no se usan y se pueden eliminar.
Aquí hay una solicitud 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=
Vuelva a crear índices con menos bloqueos
A menudo, los índices tienen que recrearse, por ejemplo, cuando están inflados en tamaño, y la recreación puede acelerar el escaneo. Además, los índices pueden estar corruptos. Cambiar los parámetros del índice también puede requerir volver a crearlo.
Habilitar creación de índice paralelo
En PostgreSQL 11, crear un índice B-Tree es competitivo. Para acelerar el proceso de creación, se pueden utilizar varios trabajadores paralelos. Sin embargo, asegúrese de que estos parámetros de configuración estén configurados 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 aumentarse junto con el número de núcleos de procesador. Lea la
documentación para más detalles.
Creación de índice de fondo
Puede crear un índice en segundo plano utilizando el parámetro
CONCURRENTLY del comando
CREATE INDEX :
pagila=
Este procedimiento de creación de índice difiere del habitual en que no requiere el bloqueo de 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 ofrece muchas opciones flexibles para crear índices y formas de resolver casos particulares, así como formas de administrar la base de datos en caso de crecimiento explosivo de su aplicación. Esperamos que estos consejos lo ayuden a agilizar sus consultas y a que su base de datos esté lista para escalar.