🥇Antipatróns PostgreSQL: JOIN e OR prexudiciais

Coidado coas operacións que traen buffers...
Usando unha pequena consulta como exemplo, vexamos algúns enfoques universais para optimizar consultas en PostgreSQL. Se os usas ou non depende de ti, pero paga a pena coñecelos.

Nalgunhas versións posteriores de PG a situación pode cambiar a medida que o planificador se fai máis intelixente, pero para 9.4/9.6 parece aproximadamente o mesmo, como nos exemplos aquí.

Tomemos unha petición moi 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 os nomes de táboas e camposOs nomes "rusos" de campos e táboas pódense tratar de forma diferente, pero esta é unha cuestión de gustos. Porque o aquí en Tensor non hai desenvolvedores estranxeiros, e PostgreSQL permítenos dar nomes mesmo en xeroglíficos, se entre comiñas, entón preferimos nomear os obxectos de forma inequívoca e clara para que non haxa discrepancias.
Vexamos o plan resultante:

[Mira explicar.tensor.ru]

144ms e case 53K de buffers - é dicir, máis de 400 MB de datos! E teremos sorte se todos eles están na caché no momento da nosa solicitude, se non, tardará moitas veces máis cando se le desde o disco.

O algoritmo é o máis importante!

Para optimizar dalgún xeito calquera solicitude, primeiro debes comprender o que debe facer.
Deixemos o desenvolvemento da propia estrutura da base de datos fóra do ámbito deste artigo polo de agora, e esteamos de acordo en que podemos "de forma relativamente barata" reescribir a solicitude e/ou rodar sobre a base algunhas das cousas que necesitamos Índices.

Entón a solicitude:
— comproba a existencia de polo menos algún documento
- na condición que precisamos e dun determinado tipo
- onde o autor ou intérprete é o empregado que necesitamos

ÚNETE + LÍMITE 1

Moitas veces é máis fácil para un programador escribir unha consulta onde se unen primeiro un gran número de táboas e despois só queda un rexistro de todo este conxunto. Pero máis fácil para o programador non significa máis eficiente para a base de datos.
No noso caso só había 3 táboas, e cal é o efecto...

Primeiro desfacemos a conexión coa táboa "Tipo de documento" e, ao mesmo tempo, digamos á base de datos que o noso rexistro tipo é único (sabémolo, pero o planificador aínda non ten idea):

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

Si, se a táboa/CTE consta dun só campo dun único rexistro, entón en PG podes escribir así, en lugar de

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

Avaliación perezosa en consultas PostgreSQL

BitmapOr vs UNION

Nalgúns casos, Bitmap Heap Scan custaranos moito, por exemplo, na nosa situación, cando moitos rexistros cumpren a condición requirida. Conseguímolo porque OR condición convertida en BitmapOr- Operación en plan.
Volvamos ao problema orixinal: necesitamos atopar un rexistro correspondente calquera a partir das condicións, é dicir, non hai necesidade de buscar todos os rexistros de 59 XNUMX en ambas as condicións. Hai unha forma de resolver unha condición, e ir ao segundo só cando non se atopou nada no primeiro. O seguinte deseño axudaranos:

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

LÍMITE 1 "externo" garante que a busca remate cando se atopa o primeiro rexistro. E se xa se atopa no primeiro bloque, non se executará o segundo bloque (nunca executado respecto de).

"Ocultar condicións difíciles baixo CASE"

Hai un momento extremadamente inconveniente na consulta orixinal: comprobar o estado na táboa relacionada "Extensión do documento". Independentemente da verdade doutras condicións na expresión (por exemplo, d.“Eliminado” NON É VERDADEIRO), esta conexión sempre se executa e "custa recursos". Gastaranse máis ou menos - depende do tamaño desta táboa.
Pero pode modificar a consulta para que a busca dun rexistro relacionado se produza só cando sexa 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

Unha vez dende a táboa vinculada ata nós ningún dos campos é necesario para o resultado, entón temos a oportunidade de converter JOIN nunha condición nunha subconsulta.
Deixemos os campos indexados "fóra dos corchetes CASE", engade condicións sinxelas do rexistro ao bloque WHEN e agora a consulta "pesada" execútase só ao pasar a THEN.

O meu apelido é "Total"

Recollemos a consulta resultante con todas as 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;

Axuste [a] índices

Un ollo adestrado notou que as condicións indexadas nos subbloques UNION son lixeiramente diferentes, porque xa temos índices axeitados sobre a mesa. E se non existisen, pagaría a pena crear: Documento (Person3, DocumentType) и Documento (Tipo de documento, empregado).
sobre a orde dos campos nas condicións ROWDende o punto de vista do planificador, por suposto, podes escribir (A, B) = (constA, constB)E (B, A) = (constB, constA). Pero ao gravar na orde dos campos do índice, tal solicitude é simplemente máis conveniente para depurar máis tarde.
Que hai no plan?

[Mira explicar.tensor.ru]

Por desgraza, tivemos mala sorte e non se atopou nada no primeiro bloque UNION, polo que o segundo aínda estaba executado. Pero aínda así - só 0.037 ms e 11 buffers!
Axilizamos a solicitude e reducimos o bombeo de datos na memoria varios miles de veces, usando técnicas bastante sinxelas: un bo resultado cun pouco de copiar e pegar. 🙂

Fonte: www.habr.com

Antipatróns de PostgreSQL: JOIN e OR daniños