Antipatrones de PostgreSQL: JOIN y OR dañinos

Cuidado con las operaciones que traen amortiguadores...
Usando una pequeña consulta como ejemplo, veamos algunos enfoques universales para optimizar consultas en PostgreSQL. Depende de usted si los usa o no, pero vale la pena conocerlos.

En algunas versiones posteriores de PG la situación puede cambiar a medida que el planificador se vuelve más inteligente, pero para 9.4/9.6 se ve aproximadamente igual, como en los ejemplos aquí.

Tomemos una petición muy real:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

sobre nombres de tablas y camposLos nombres "rusos" de campos y tablas se pueden tratar de forma diferente, pero es una cuestión de gustos. Porque el aquí en Tensor no hay desarrolladores extranjeros, y PostgreSQL nos permite dar nombres incluso en jeroglíficos, si encerrado entre comillas, entonces preferimos nombrar los objetos de forma inequívoca y clara para que no haya discrepancias.
Veamos el plan resultante:

[mira explicar.tensor.ru]

144 ms y casi 53 KB de buffers - ¡Es decir, más de 400 MB de datos! Y tendremos suerte si todos ellos están en el caché en el momento de nuestra solicitud; de lo contrario, tardará mucho más en leerse desde el disco.

¡El algoritmo es lo más importante!

Para optimizar de alguna manera cualquier solicitud, primero debe comprender qué debe hacer.
Dejemos el desarrollo de la estructura de la base de datos en sí fuera del alcance de este artículo por ahora y aceptemos que podemos hacerlo de manera relativamente "barata". reescribir la solicitud y/o enrollar sobre la base algunas de las cosas que necesitamos Índices.

Entonces la solicitud:
— comprueba la existencia de al menos algún documento
- en la condición que necesitamos y de un cierto tipo
- donde el autor o intérprete es el empleado que necesitamos

ÚNETE + LÍMITE 1

Muy a menudo es más fácil para un desarrollador escribir una consulta en la que primero se unen una gran cantidad de tablas y luego solo queda un registro de todo este conjunto. Pero más fácil para el desarrollador no significa más eficiente para la base de datos.
En nuestro caso sólo había 3 mesas, ¿y cuál es el efecto...?

Primero eliminemos la conexión con la tabla "Tipo de documento" y al mismo tiempo le digamos a la base de datos que nuestro registro de tipo es único (lo sabemos, pero el planificador aún no tiene idea):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Sí, si la tabla/CTE consta de un solo campo de un solo registro, entonces en PG puedes incluso escribir así, en lugar de

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Evaluación diferida en consultas PostgreSQL

BitmapOr y UNION

En algunos casos, Bitmap Heap Scan nos costará mucho; por ejemplo, en nuestra situación, cuando muchos registros cumplen con la condición requerida. Lo conseguimos porque O condición convertida en mapa de bitsO- operación en plan.
Volvamos al problema original: necesitamos encontrar un registro correspondiente a cualquier de las condiciones, es decir, no es necesario buscar todos los registros de 59K en ambas condiciones. Hay una manera de resolver una condición y ir al segundo solo cuando no se encontró nada en el primero. El siguiente diseño nos ayudará:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

El LÍMITE 1 “externo” garantiza que la búsqueda finaliza cuando se encuentra el primer registro. Y si ya se encuentra en el primer bloque, el segundo bloque no se ejecutará (nunca ejecutado con respecto a).

“Ocultar condiciones difíciles bajo CASE”

Hay un momento extremadamente inconveniente en la consulta original: verificar el estado con la tabla relacionada "DocumentExtension". Independientemente de la verdad de otras condiciones en la expresión (por ejemplo, d.“Eliminado” NO ES VERDADERO), esta conexión siempre se ejecuta y “cuesta recursos”. Se gastarán más o menos, depende del tamaño de esta tabla.
Pero puedes modificar la consulta para que la búsqueda de un registro relacionado se produzca sólo cuando sea realmente necesario:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Una vez desde la tabla vinculada a nosotros Ninguno de los campos es necesario para el resultado., entonces tenemos la oportunidad de convertir JOIN en una condición en una subconsulta.
Dejemos los campos indexados "fuera de los corchetes de CASO", agreguemos condiciones simples del registro al bloque CUANDO, y ahora la consulta "pesada" se ejecuta solo cuando se pasa a ENTONCES.

Mi apellido es "Total"

Recopilamos la consulta resultante con todas las mecánicas descritas anteriormente:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Ajustando [a] índices

Un ojo entrenado notó que las condiciones indexadas en los subbloques UNION son ligeramente diferentes; esto se debe a que ya tenemos índices adecuados en la mesa. Y si no existieran, valdría la pena crearlos: Documento(Persona3, Tipo de Documento) и Documento(Tipo de documento, Empleado).
sobre el orden de los campos en condiciones de filaDesde el punto de vista del planificador, por supuesto, puedes escribir (A, B) = (constA, constB)Y (B, A) = (constB, constanteA). Pero al grabar en el orden de los campos del índice, dicha solicitud es simplemente más conveniente para depurar más adelante.
¿Qué hay en el plan?

[mira explicar.tensor.ru]

Desafortunadamente, tuvimos mala suerte y no se encontró nada en el primer bloque UNION, por lo que el segundo aún se ejecutó. Pero aun así - sólo 0.037 ms y 11 buffers!
Hemos acelerado la solicitud y reducido el bombeo de datos en la memoria. varios miles de veces, utilizando técnicas bastante simples: un buen resultado con un poco de copiar y pegar. 🙂

Fuente: habr.com