Cómo hacer que una consulta de Postgres sea 1,000 veces más rápida

• Mattermost usa Elasticsearch para reducir la carga de la base de datos y ofrecer resultados de búsqueda mucho más rápidos
  • Para que Elasticsearch funcione correctamente, es necesario indexar todos los datos que serán objeto de búsqueda
  • Para los datos ya indexados, el trabajo posterior de indexación de nuevas publicaciones y archivos es bastante rápido
  • Sin embargo, indexar por completo desde cero una base de datos muy grande (100 millones de publicaciones) es muy lento (ni siquiera llegó a la mitad en 18 horas y se iba volviendo cada vez más lento)
• A través del gráfico del tiempo consumido por cada llamada a la base de datos, se identificó que la consulta SQL del método PostStore.GetPostsBatchForIndexing era el problema
  • Esa consulta básicamente ordena las publicaciones por timestamp de creación y devuelve N publicaciones más nuevas que un timestamp dado
  • El trabajo de indexación ejecuta esta consulta repetidamente hasta que todas las publicaciones quedan indexadas
• Se analizó el plan de ejecución de la consulta usando EXPLAIN (ANALYZE, BUFFERS):
  • Al hacer un index scan en la tabla Posts, se estaban procesando 40 millones de bloques para aplicar la condición Filter (309GB)
  • El JOIN con la tabla Channels no era el problema
  • Si se aplicaba solo la parte Posts.CreateAt > ?1 de la cláusula OR del WHERE, era mucho más rápido (30ms)
  • A partir de ahí, al aplicar la condición Posts.CreateAt = ?1 AND Posts.Id > ?2, se volvía extremadamente rápido (0.047ms)
• Identificación de la causa:
  • La consulta original recorría todas las filas de Posts y las descartaba con Filter, mientras que la consulta modificada revisaba solo el índice y extraía únicamente las filas necesarias
  • La razón por la que la consulta se volvía cada vez más lenta con el tiempo era que tenía que descartar cada vez más filas
• Solución:
  • Se aprovechó la función de comparación de constructores de filas de PostgreSQL y se cambió la condición a (Posts.CreateAt, Posts.Id) > (?1, ?2)
  • Con este cambio, el tiempo de ejecución de la consulta se redujo drásticamente a 34 milisegundos
  • Sin embargo, en MySQL la consulta modificada resultó ser más lenta. Como en MySQL la consulta original era más rápida, se agregó una bifurcación en el código para usar consultas distintas según la base de datos
• Lecciones aprendidas:
  • Al usar EXPLAIN, hay que usar siempre la opción BUFFERS
  • Hay que procurar usar Index Cond en lugar de Filter
  • Hay que asumir que PostgreSQL y MySQL casi siempre se comportan de manera diferente
• Conclusión
  • Gracias a la optimización, se logró reducir el tiempo de ejecución de la consulta en más de 1000 veces
  • Esta optimización se incorporó en Mattermost v9.7.0 y en la versión ESR v9.5
  • Este trabajo de optimización permitió aprender muchas cosas

[dontcryme]

Como también aparece en la última publicación, el título de este artículo se siente un poco como clickbait, pero si quisiéramos cambiarlo por algo más práctico,

¿sería algo como

"casos de uso de PostgreSQL aprendidos a través de errores"

?

[vwjdalsgkv]

Mmm... personalmente, si se escribe un artículo de este nivel mencionando a una empresa o producto en particular, creo que más bien terminaría bajando bastante mi confianza en ese producto.
La organización es clara y ordenada, pero da pena que el valor técnico que contiene parezca un poco insuficiente.

[savvykang]

Yo también sentí que este artículo me hizo confiar menos. Están vendiendo un producto y lanzaron una función sin siquiera hacer pruebas de procesamiento a gran escala. Me parece que un índice tan simple debería haberse configurado desde la etapa de desarrollo de la función. Da la impresión de que se omitieron muchos pasos del proceso de desarrollo de software.