Sobre cómo tuve que lidiar con la optimización de consultas de PostgreSQL y qué resultó de todo esto.
¿Por qué tuviste que hacerlo? Sí, porque los 4 años anteriores todo funcionó en silencio, con calma, como un reloj en marcha.
Como un epígrafe.
Basado en hechos reales.
Todos los nombres han sido cambiados, las coincidencias son aleatorias.
Cuando se logra un determinado resultado, siempre es interesante recordar cuál fue el ímpetu para el comienzo, cómo comenzó todo.
Entonces, lo que sucedió como resultado se describe brevemente en el artículo "".
Probablemente será interesante recrear la cadena de eventos anteriores.
El historial mantuvo la fecha de inicio exacta: 2018-09-10 18:02:48.
Además, en la historia hay una petición a partir de la cual comenzó todo:
Solicitud de problemaSELECCIONAR
p. "PARAMETER_ID" como parámetro_id,
pd. "PD_NAME" COMO pd_name,
pd. "CUSTOMER_PARTNUMBER" COMO cliente_número de pieza,
w."LRM" COMO LRM,
W. "LOTID" COMO lotid,
W. "RTD_VALUE" COMO RTD_value,
w. "LOWER_SPEC_LIMIT" COMO lower_spec_limit,
w. "UPPER_SPEC_LIMIT" COMO upper_spec_limit,
p."TYPE_CALCUL" COMO type_calcul,
s."SPENT_NAME" COMO gastado_nombre,
s."FECHA_GASTO" COMO fecha_gastado,
extract(año desde "SPENT_DATE") COMO año,
extract(mes desde "SPENT_DATE") como mes,
s."NOMBRE_INFORME" COMO nombre_informe,
p. "STPM_NAME" COMO stpm_name,
p."CUSTOMERPARAM_NAME" COMO customerparam_name
DESDE wdataw,
gastado,
pmtrp,
gastado_pdsp,
PD PD
DONDE s."SPENT_ID" = w."SPENT_ID"
Y p."PARAMETER_ID" = w."PARAMETER_ID"
Y s."SPENT_ID" = sp."SPENT_ID"
Y pd. "PD_ID" = sp. "PD_ID"
Y s."FECHA_GASTO" >= '2018-07-01' Y s."FECHA_GASTO" <= '2018-09-30'
y s."FECHA_GASTO" = (SELECCIONE MAX(s2."FECHA_GASTO")
DE gastó s2,
datos w2
DONDE s2."SPENT_ID" = w2."SPENT_ID"
Y w2."LRM" = w."LRM");
Descripción del problema, predeciblemente estándar: “Todo está mal. Dime cuál es el problema".
Inmediatamente recordé una broma de los tiempos de las unidades de 3 pulgadas y media:
El lamer llega al hacker.
- No me funciona nada, dime donde esta el problema.
-En el ADN...
Pero, por supuesto, esta no es la forma de resolver los incidentes de rendimiento. “Puede que no seamos entendidos" (Con). Necesito averiguarlo.
Bueno, vamos a cavar. Tal vez eso se acumulará como resultado.
inversión iniciada
Entonces, lo que se puede ver de inmediato a simple vista, sin siquiera recurrir a la ayuda de EXPLAIN.
1) No se utilizan JOIN. Esto es malo, especialmente si el número de conexiones es más de uno.
2) Pero lo que es aún peor: una subconsulta correlacionada, además, con agregación. Esto es muy malo.
Esto es malo, por supuesto. Pero esto es sólo por un lado. Por otro lado, esto es muy bueno, porque el problema claramente tiene solución y la solicitud es mejorable.
No vayas al adivino (C).
El plan de consulta no es tan complicado, pero bastante indicativo:
Plan de ejecución
Lo más interesante y útil, como siempre, al principio y al final.
Bucle anidado (costo=935.84...479763226.18 filas=3322 ancho=135) (tiempo real=31.536...8220420.295 filas=8111656 bucles=1)
Tiempo de planificación: 3.807ms
Tiempo de ejecución: 8222351.640 ms
El tiempo de ejecución es de más de 2 horas.
Hipótesis falsas que tomaron tiempo
Hipótesis 1- El optimizador está equivocado, construye el plan equivocado.
Para visualizar el plan de ejecución, utilizaremos el sitio . Sin embargo, el sitio no mostró nada interesante o útil. A primera y segunda vista, nada que realmente pudiera ayudar. A menos que el análisis completo sea mínimo. Adelante.
Hipótesis 2-Impacto en la base por el lateral del autovacío, hay que desmontar los frenos.
Pero, los demonios de autovacuum se comportan bien, no hay procesos pendientes. Cualquier carga seria - no. Necesito buscar algo más.
Hipótesis 3-Estadística está desactualizada, necesita recalcular todo vuela
De nuevo, eso no. Las estadísticas están actualizadas. Lo cual, dada la falta de problemas con el autovacío, no es sorprendente.
Comenzando a optimizar
La tabla principal 'wdata' ciertamente no es pequeña, casi 3 millones de registros.
Y es sobre esta mesa donde va Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") Y ((SubPlan 1) = s."SPENT_DATE"))
-> Escaneo secuencial en wdata w (costo=0.00..574151.49 filas=26886249 ancho=46) (tiempo real=0.005..8153.565 filas=26873950 bucles=1)
Actuamos como estándar: “hagamos un índice y todo vuela”.
Hizo un índice en el campo "SPENT_ID"
Como resultado:
Plan de ejecución de consultas usando un índice
Bueno, ¿ayudó?
Fue 8 222 351.640ms (poco más de 2 horas)
Se convirtió en: 6 985 431.575 ms (casi 2 horas)
En general, las mismas manzanas, vista lateral.
Recordemos los clásicos:
“¿Tienes el mismo, pero sin alas? Buscará".
En principio, esto podría llamarse un buen resultado, bueno, no bueno, pero aceptable. Como mínimo, proporcione un informe amplio al cliente que describa cuánto se ha hecho y por qué lo que se ha hecho es bueno.
Sin embargo, la decisión final aún está lejos. Muy lejos.
Y ahora lo más interesante: seguimos optimizando, puliremos la consulta
Paso uno: use JOIN
Consulta reescrita, ahora se ve así (bueno al menos mas bonita):
Consulta usando JOINSELECCIONAR
p. "PARAMETER_ID" como parámetro_id,
pd. "PD_NAME" COMO pd_name,
pd. "CUSTOMER_PARTNUMBER" COMO cliente_número de pieza,
w."LRM" COMO LRM,
W. "LOTID" COMO lotid,
W. "RTD_VALUE" COMO RTD_value,
w. "LOWER_SPEC_LIMIT" COMO lower_spec_limit,
w. "UPPER_SPEC_LIMIT" COMO upper_spec_limit,
p."TYPE_CALCUL" COMO type_calcul,
s."SPENT_NAME" COMO gastado_nombre,
s."FECHA_GASTO" COMO fecha_gastado,
extract(año desde "SPENT_DATE") COMO año,
extract(mes desde "SPENT_DATE") como mes,
s."NOMBRE_INFORME" COMO nombre_informe,
p. "STPM_NAME" COMO stpm_name,
p."CUSTOMERPARAM_NAME" COMO customerparam_name
DESDE wdata w INNER JOIN gastados s ON w."SPENT_ID"=s."SPENT_ID"
UNIÓN INTERNA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
UNIÓN INTERNA gastado_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
UNIÓN INTERNA pd pd ON pd."PD_ID" = sp."PD_ID"
¿Dónde?
s."FECHA_GASTO" >= '2018-07-01' Y s."FECHA_GASTO" <= '2018-09-30'Y
s."FECHA_GASTO" = (SELECCIONE MAX(s2."FECHA_GASTO")
DESDE wdata w2 INNER JOIN gastó s2 EN w2."SPENT_ID"=s2."SPENT_ID"
UNIÓN INTERNA wdata w
ON w2."LRM" = w."LRM" );
Tiempo de planificación: 2.486ms
Tiempo de ejecución: 1223680.326 ms
Así que aquí está el primer resultado.
Fue 6 985 431.575 ms (casi 2 horas).
Se convirtió en: 1 223 680.326 ms (poco más de 20 minutos).
Buen resultado. En principio, nuevamente, sería posible detenerse allí. Pero tan poco interesante, no puedes parar.
Mbo
Paso dos: deshacerse de la subconsulta correlacionada
Texto de solicitud modificado:
Sin subconsulta correlacionadaSELECCIONAR
p. "PARAMETER_ID" como parámetro_id,
pd. "PD_NAME" COMO pd_name,
pd. "CUSTOMER_PARTNUMBER" COMO cliente_número de pieza,
w."LRM" COMO LRM,
W. "LOTID" COMO lotid,
W. "RTD_VALUE" COMO RTD_value,
w. "LOWER_SPEC_LIMIT" COMO lower_spec_limit,
w. "UPPER_SPEC_LIMIT" COMO upper_spec_limit,
p."TYPE_CALCUL" COMO type_calcul,
s."SPENT_NAME" COMO gastado_nombre,
s."FECHA_GASTO" COMO fecha_gastado,
extract(año desde "SPENT_DATE") COMO año,
extract(mes desde "SPENT_DATE") como mes,
s."NOMBRE_INFORME" COMO nombre_informe,
p. "STPM_NAME" COMO stpm_name,
p."CUSTOMERPARAM_NAME" COMO customerparam_name
DESDE wdata w INNER JOIN gastado s ON s."SPENT_ID" = w."SPENT_ID"
UNIÓN INTERNA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
UNIÓN INTERNA gastado_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
UNIÓN INTERNA pd pd ON pd."PD_ID" = sp."PD_ID"
UNIÓN INTERNA (SELECCIONE w2."LRM", MAX(s2."SPENT_DATE")
DESDE gastado s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
AGRUPAR POR w2.LRM
) md en w. "LRM" = md. "LRM"
¿Dónde?
s."FECHA_GASTO" >= '2018-07-01' Y s."FECHA_GASTO" <= '2018-09-30';
Tiempo de planificación: 2.291ms
Tiempo de ejecución: 165021.870 ms
Fue 1 223 680.326 ms (poco más de 20 minutos).
Se convirtió en: 165 021.870 ms (poco más de 2 minutos).
Esto ya está bastante bien.
Sin embargo, como dicen los ingleses,Pero siempre hay un pero". Un resultado demasiado bueno debería despertar sospechas automáticamente. Algo está mal aquí.
La hipótesis de corregir la consulta para deshacerse de la subconsulta correlacionada es correcta. Pero necesita un pequeño ajuste para obtener el resultado final correcto.
Como resultado, el primer resultado intermedio:
Consulta editada sin subconsulta correlacionadaSELECCIONAR
p. "PARAMETER_ID" como parámetro_id,
pd. "PD_NAME" COMO pd_name,
pd. "CUSTOMER_PARTNUMBER" COMO cliente_número de pieza,
w."LRM" COMO LRM,
W. "LOTID" COMO lotid,
W. "RTD_VALUE" COMO RTD_value,
w. "LOWER_SPEC_LIMIT" COMO lower_spec_limit,
w. "UPPER_SPEC_LIMIT" COMO upper_spec_limit,
p."TYPE_CALCUL" COMO type_calcul,
s."SPENT_NAME" COMO gastado_nombre,
s."FECHA_GASTO" COMO fecha_gastado,
extract(año desde s. "SPENT_DATE") COMO año,
extract(mes de s. "SPENT_DATE") como mes,
s."NOMBRE_INFORME" COMO nombre_informe,
p. "STPM_NAME" COMO stpm_name,
p."CUSTOMERPARAM_NAME" COMO customerparam_name
DESDE wdata w INNER JOIN gastado s ON s."SPENT_ID" = w."SPENT_ID"
UNIÓN INTERNA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
UNIÓN INTERNA gastado_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
UNIÓN INTERNA pd pd ON pd."PD_ID" = sp."PD_ID"
UNIÓN INTERNA (SELECCIONE w2."LRM", MAX(s2."SPENT_DATE") COMO "SPENT_DATE"
DESDE gastado s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
AGRUPAR POR w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w."LRM"
¿Dónde?
s."FECHA_GASTO" >= '2018-07-01' Y s."FECHA_GASTO" <= '2018-09-30';
Tiempo de planificación: 3.192ms
Tiempo de ejecución: 208014.134 ms
Entonces, lo que tenemos como resultado es el primer resultado aceptable, que no nos da vergüenza mostrar al cliente:
Comenzó con: 8 222 351.640 ms (más de 2 horas)
Logrado: 1 ms (poco más de 223 minutos).
Resultado (intermedio): 208 014.134 ms (poco más de 3 minutos).
Gran resultado
Total
Esto podría haberse detenido.
PERO ...
El apetito viene con la comida. El camino se dominará caminando. Cualquier resultado es intermedio. Parado en seco. Etc.
Sigamos con la optimización.
Gran idea. Sobre todo teniendo en cuenta que el cliente ni siquiera estaba en contra. E incluso fuertemente - para.
Entonces, es hora de rediseñar la base de datos. La estructura de solicitud en sí ya no se puede optimizar (aunque, como resultó más tarde, hay una opción para que todo realmente vuele). Pero ahora, para optimizar y desarrollar el diseño de la base de datos, esta ya es una idea muy prometedora. Y lo más importante interesante. Una vez más, recuerda la juventud. Después de todo, no me convertí inmediatamente en un DBA, dejé de programadores (básico, ensamblador, si, si dos veces más, oráculo, plsql). Un tema interesante, por supuesto, para memorias separadas ;-).
Sin embargo, no nos desviemos.
Por lo tanto,
¿Y tal vez seccionar nos ayude?
Spoiler: "Sí, ayudó, y en la optimización del rendimiento, incluso".
Pero esa es una historia completamente diferente ...
Continuará…
Fuente: habr.com