PostgreSQL Index Advisor

• Extensión de PostgreSQL de Supabase que recomienda índices para mejorar el rendimiento de las consultas
• Si pasas una consulta a la función index_advisor(), devuelve el costo antes/después para startup/total y el SQL DDL para crear el índice
  • Ejecución: select * from index_advisor('select book.id from book where title = $1');
  • Retorno: {"CREATE INDEX ON public.book USING btree (title)"}
• Para consultas complejas, también puede devolver varias sentencias de creación de índices
• Soporte para parámetros genéricos ($1, $2, ..)
• Soporte para Materialized View
• Puede identificar tablas/columnas ocultas por una vista

[savvykang]

En la versión actual solo recomienda índices btree de una sola columna. No se puede usar si las condiciones de consulta se vuelven complejas o si estás haciendo búsquedas de texto completo https://supabase.com/docs/guides/…

[savvykang]

Se dice que cuando las condiciones de consulta son complejas, se usan varios índices de una sola columna en lugar de un índice multicolumna, pero parece que no funcionan exactamente igual. O también se comenta que hay situaciones en las que lo mejor es usar al mismo tiempo un índice multicolumna y varios índices de una sola columna.

https://www.postgresql.org/docs/current/indexes-bitmap-scans.html

[xguru]

Opiniones en Hacker News

• Estaría bien que hubiera una función que recomendara tipos de datos más eficientes basándose en los datos realmente almacenados en la tabla
• Sería bueno tener una base de datos que detecte automáticamente las consultas lentas y cree los índices necesarios
  • Si ejecutas pruebas de carga desde la aplicación, la base de datos podría recibir las llamadas, recopilar las consultas y ajustarse automáticamente
• No sabía que HypoPG había estado disponible en RDS por más de un año
• Quiero que se use un índice en una relation con joins de 3 o más tablas, pero si no pongo un limit en el CTE, Postgres intenta ejecutar cada join en paralelo y termina intentando hacer join de una enorme cantidad de filas
  • Últimamente, lidiar con el planificador de consultas me hace sentir que voy a terminar rompiendo con pg
• CockroachDB tiene una función similar integrada
  • Toma consultas lentas existentes, analiza índices virtuales y los sugiere para obtener un mejor plan de consulta
  • Se pueden agregar con un solo clic desde la UI de la consola
• En motores de consulta distribuidos como Presto o Spark, se hace algo parecido usando particiones y buckets en lugar de índices
  • Eso puede reducir cómputo, tiempo y costo
• Está escrito en Vanilla Pl/PgSQL, lo cual resulta práctico
  • Da la tentación de copiar la función index_advisor(text) a la sesión y empezar a meter hardcodeo y heurísticas
  • La mayoría de las extensiones realmente útiles requieren compilar, instalar, crear y eliminar
• Es similar a TiAdvisor de TiDB y usa un método virtual
• Estoy usando pghero y ofrece esta función con GUI
• No parece ofrecer consideraciones ni insights sobre los trade-offs involucrados
  • La extensión base, HypoPG, no parece recopilar estadísticas sobre los datos que afectan al planificador de consultas
• Me pregunto si reconoce tablas padre e hijas heredadas