Ajuste de índices en Azure Database for PostgreSQL: servidor flexible
SE APLICA A: 
Servidor flexible de Azure Database for PostgreSQL
El ajuste de índices es una característica de Azure Database for PostgreSQL: servidor flexible que mejora automáticamente el rendimiento de la carga de trabajo al analizar las consultas con seguimiento y proporcionar recomendaciones de índice.
Se trata de una oferta integrada en Azure Database for PostgreSQL: servidor flexible que se basa en Supervisar el rendimiento con la funcionalidad del Almacén de consultas. El ajuste de índices analiza la carga de trabajo de la que el Almacén de consultas realiza el seguimiento y genera recomendaciones de índice para mejorar el rendimiento de la carga de trabajo analizada o para quitar índices duplicados o sin usar.
- Identifique qué índices son beneficiosos crear porque podrían mejorar significativamente las consultas analizadas durante una sesión de ajuste de índices.
- Identifique los índices que son duplicados exactos y que se pueden eliminar para reducir el impacto en el rendimiento que su existencia y mantenimiento tienen en el rendimiento general del sistema.
- Identifique los índices que no se usan en un período configurable que podrían ser candidatos para eliminar.
Descripción general del algoritmo de ajuste de índices
Cuando el parámetro de servidor index_tuning.mode está configurado en report, las sesiones de ajuste se inician automáticamente con la frecuencia configurada en el parámetro de servidor index_tuning.analysis_interval, expresada en minutos.
En la primera fase, la sesión de ajuste busca la lista de bases de datos en las que considera que cualquier recomendación que pueda generar podría afectar significativamente al rendimiento general del sistema. Para ello, recopila todas las consultas registradas por el Almacén de consultas cuyas ejecuciones se capturaron dentro del intervalo de búsqueda en el que se centra esta sesión de ajuste. El intervalo de búsqueda abarca actualmente hasta los últimos index_tuning.analysis_interval minutos, desde la hora de inicio de la sesión de ajuste.
Para todas las consultas iniciadas por el usuario con ejecuciones registradas en el Almacén de consultas y cuyas estadísticas en runtime no se restablecen, el sistema las clasifica en función del tiempo de ejecución total agregado. Centra su atención en las consultas más destacadas, en función de su duración.
Las siguientes consultas se excluyen de esa lista:
- Consultas iniciadas por el sistema. (es decir, las consultas ejecutadas por el rol
azuresu) - Consultas ejecutadas en el contexto de cualquier base de datos del sistema (
azure_sys,template0,template1yazure_maintenance).
El algoritmo itera las bases de datos de destino, buscando posibles índices que podrían mejorar el rendimiento de las cargas de trabajo analizadas. También busca índices que puedan eliminarse porque se han identificado como duplicados o no se han utilizado durante un periodo de tiempo configurable.
Recomendaciones de CREATE INDEX
Para cada base de datos identificada como candidata para su análisis para generar recomendaciones de índice, se tienen en cuenta todas las consultas SELECT, UPDATE, INSERT y DELETE ejecutadas durante el intervalo de búsqueda y en el contexto de esa base de datos específica.
Nota:
El ajuste de índices analiza no solo las instrucciones SELECT, sino también las instrucciones DML (UPDATE, INSERT y DELETE).
El conjunto resultante de consultas se clasifica en función de su tiempo de ejecución total agregado y se analiza el index_tuning.max_queries_per_database superior para ver posibles recomendaciones de índice.
Las posibles recomendaciones tienen como objetivo mejorar el rendimiento de estos tipos de consultas:
- Consultas con filtros (es decir, consultas con predicados en la cláusula WHERE),
- Las consultas que combinan varias relaciones, independientemente de si siguen la sintaxis en la que las combinaciones se expresan con la cláusula JOIN o si los predicados de combinación se expresan en la cláusula WHERE.
- Consultas que combinan filtros y predicados de combinación.
- Consultas con agrupación (consultas con una cláusula GROUP BY).
- Consultas que combinan filtros y agrupación.
- Consultas con ordenación (consultas con una cláusula ORDER BY).
- Consultas que combinan filtros y ordenación.
Nota:
El único tipo de índice que el sistema recomienda actualmente es el de tipo árbol B.
Si una consulta hace referencia a una columna de una tabla y esa tabla no tiene estadísticas, omite toda la consulta y no genera ninguna recomendación de índice para mejorar su ejecución.
El análisis necesario para recopilar estadísticas se puede desencadenar manualmente mediante el comando ANALYZE o automáticamente mediante el demonio autovacuum.
index_tuning.max_indexes_per_table especifica el número de índices que se pueden recomendar, excepto los índices que ya podrían existir en la tabla para cualquier tabla única a la que haga referencia cualquier número de consultas durante una sesión de ajuste.
index_tuning.max_index_count especifica el número de recomendaciones de índice generadas para todas las tablas de cualquier base de datos analizada durante una sesión de ajuste.
Para que se emita una recomendación de índice, el motor de ajuste debe calcular que este mejora al menos una consulta de la carga de trabajo analizada por un factor especificado con index_tuning.min_improvement_factor.
Del mismo modo, se comprueban todas las recomendaciones de índice para asegurarse de que no introducen regresión en ninguna consulta única de esa carga de trabajo de un factor especificado con index_tuning.max_regression_factor.
Nota:
index_tuning.min_improvement_factor y index_tuning.max_regression_factor hacen referencia al coste de los planes de consulta, no a su duración o a los recursos que consumen durante la ejecución.
Todos los parámetros mencionados en los párrafos anteriores, sus valores predeterminados e intervalos válidos se describen en las opciones de configuración.
El script generado junto con la recomendación de crear un índice sigue este patrón:
create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])
Incluye la cláusula concurrently. Para obtener más información sobre los efectos de esta cláusula, visite la documentación oficial de PostgreSQL para CREATE INDEX.
El ajuste de índices genera automáticamente los nombres de los índices recomendados, que normalmente constan de los nombres de las diferentes columnas de clave separadas por "_" (guiones bajos) y con un sufijo "_idx" constante. Si la longitud total del nombre supera los límites de PostgreSQL o si entra en conflicto con las relaciones existentes, el nombre será ligeramente diferente. Podría truncarse y se podría anexar un número al final del nombre.
Calcular el impacto de una recomendación CREATE INDEX
El impacto de crear una recomendación de índice se mide en IndexSize (megabytes) y QueryCostImprovement (porcentaje).
IndexSize es un valor único que representa el tamaño estimado del índice, teniendo en cuenta la cardinalidad actual de la tabla y el tamaño de las columnas a las que hace referencia el índice recomendado.
QueryCostImprovement consta de una matriz de valores, donde cada elemento representa la mejora del costo del plan para cada consulta cuyo costo del plan se estima que mejorará si existiera este índice. Cada elemento muestra el identificador de la consulta (consultado) y el porcentaje por el que el costo del plan mejoraría si se implementara la recomendación (dimensional).
Recomendaciones DROP INDEX y REINDEX
Para cada base de datos para la que se determina la funcionalidad de ajuste de índices, debe iniciar una nueva sesión y, una vez completada la fase de recomendaciones CREATE INDEX, recomienda quitar o volver a indexar los índices existentes, en función de los siguientes criterios:
- Excluir si se considera duplicado de otros.
- Excluir si no se usa durante un período de tiempo configurable.
- Vuelva a indexar los índices marcados como no válidos.
Quitar índices duplicados
Recomendaciones para quitar índices duplicados: en primer lugar, identifique qué índices tienen duplicados.
Los duplicados se clasifican en función de las distintas funciones que se pueden atribuir al índice y en función de sus tamaños estimados.
Por último, se recomienda quitar todos los duplicados con una clasificación inferior a su líder de referencia y se describe por qué cada duplicado se clasificó de esa manera.
Para que dos índices se consideren duplicados, deben:
- Crearse en función de la misma tabla.
- Ser un índice del mismo tipo exacto.
- Coincidir en sus columnas de clave y, para las claves de índice de varias columnas, coincidir en el orden en que se hace referencia a ellas.
- Coincidir en el árbol de expresión de su predicado. Solo se aplica a índices parciales.
- Coincidir en el árbol de expresión de todas las referencias de columna no simples. Solo se aplica a los índices creados en expresiones.
- Coincidir en la intercalación de cada columna a la que se hace referencia en la clave.
Quitar índices sin usar
Las recomendaciones para quitar índices sin usar identifican los índices que:
- No se usaron durante al menos
index_tuning.unused_min_perioddías. - Muestran una cantidad mínima (media diaria) de DML
index_tuning.unused_dml_per_tableen la tabla donde se crea el índice. - Muestran una cantidad mínima (media diaria) de lecturas
index_tuning.unused_reads_per_tableen la tabla donde se crea el índice.
Volver a indexar índices no válidos
Las recomendaciones para volver a indexar los índices existentes identifican los índices marcados como no válidos. Para obtener más información sobre por qué y cuándo los índices están marcados como no válidos, consulte la documentación oficial de REINDEX en PostgreSQL.
Calcular el impacto de una recomendación DROP INDEX
El impacto de una recomendación DROP INDEX se mide en dos dimensiones: Ventaja (porcentaje) e IndexSize (megabytes).
La ventaja es un valor único que se puede omitir por ahora.
IndexSize es un valor único que representa el tamaño estimado del índice, teniendo en cuenta la cardinalidad actual de la tabla y el tamaño de las columnas a las que hace referencia el índice recomendado.
Configuración del ajuste de índices
El ajuste de índices se puede habilitar, deshabilitar y configurar mediante un conjunto de parámetros que controlan su comportamiento, como la frecuencia con la que se puede ejecutar una sesión de ajuste.
Explore todos los detalles sobre la configuración correcta de la característica de ajuste de índices en cómo habilitar, deshabilitar y configurar el ajuste de índices.
Información generada por el ajuste de índices
Cómo leer, interpretar y usar recomendaciones generadas por el ajuste de índices describe detalladamente cómo obtener y usar las recomendaciones generadas por el ajuste de índices.
Limitaciones y compatibilidad
A continuación se muestra la lista de limitaciones y ámbito de compatibilidad para el ajuste de índices.
Niveles de proceso y SKU admitidos
El ajuste de índices se admite en todos los niveles disponibles actualmente: Ampliable, De uso general y Optimizado para memoria, y en cualquier SKU de proceso compatible actualmente con al menos 4 núcleos virtuales.
Versiones compatibles de PostgreSQL
El ajuste de índices se admite en las versiones principales 12 o posteriores de Azure Database for PostgreSQL: servidor flexible.
Uso de search_path
El ajuste de índices consume el valor almacenado en la columna search_path de query_store.qs_view, de modo que, cuando se analiza cada consulta, el mismo valor de search_path que se estableció cuando la consulta se ejecutó originalmente es la que se establece para analizar posibles recomendaciones.
Consulta con parámetros
Las consultas con parámetros creadas con PREPARE o mediante el protocolo de consulta extendida se analizan y analizan para generar recomendaciones de índice en ellas.
Para el análisis de consultas con parámetros, el ajuste de índices requiere que pg_qs.parameters_capture_mode esté establecido en capture_first_sample cuando el almacén de consultas captura la ejecución de la consulta. También requiere que el almacén de consultas capture correctamente los parámetros cuando se ejecuta la consulta. En otras palabras, para la consulta que se va a analizar, query_store.qs_view debe tener su columna parameters_capture_status establecida en succeeded.
Modo de solo lectura y réplicas de lectura
Dado que el ajuste de índices se basa en el almacén de consultas, que no se admite en réplicas de lectura o cuando una instancia está en modo de solo lectura, no se admite en réplicas de lectura ni en instancias que están en modo de solo lectura.
Las recomendaciones que se ven en una réplica de lectura se generaron en la réplica principal después de haber analizado exclusivamente la carga de trabajo que se ejecutó en la réplica principal.
Reducción vertical del proceso
Si el ajuste de índices está habilitado en un servidor y reduce verticalmente el proceso del servidor a menos del número mínimo de núcleos virtuales necesarios, la característica permanece habilitada. Dado que la característica no se admite en servidores con menos de 4 núcleos virtuales, no se ejecutará para analizar la carga de trabajo y generar recomendaciones, incluso si index_tuning.mode se estableció en ON cuando el proceso se ha escalado verticalmente. Aunque el servidor no cumple los requisitos mínimos, no se puede acceder a todos los parámetros del servidor index_tuning.*. Cada vez que escale la copia de seguridad del servidor a un proceso que cumpla los requisitos mínimos, index_tuning.mode se configura con el valor que se estableció antes de reducirlo verticalmente a un proceso que no cumple los requisitos.
Alta disponibilidad y réplicas de lectura
Si tiene configurada una alta disponibilidad o réplicas de lectura en su servidor, tenga en cuenta las implicaciones asociadas a la producción de cargas de trabajo de escritura intensiva en el servidor principal cuando se implementen los índices recomendados. Tenga especial cuidado al crear índices con un tamaño estimado grande.