Antipatterns PostgreSQL : JOIN et OR nuisibles

Attention aux opérations qui apportent des buffers...
En utilisant une petite requête comme exemple, examinons quelques approches universelles pour optimiser les requêtes dans PostgreSQL. Que vous les utilisiez ou non, cela dépend de vous, mais cela vaut la peine de les connaître.

Dans certaines versions ultérieures de PG, la situation peut changer à mesure que le planificateur devient plus intelligent, mais pour 9.4/9.6, cela semble à peu près le même, comme dans les exemples ici.

Prenons une demande bien réelle :

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;

à propos des noms de tables et de champsLes noms « russes » des champs et des tables peuvent être traités différemment, mais c'est une question de goût. Parce que le ici chez Tensor il n'y a pas de développeurs étrangers, et PostgreSQL nous permet de donner des noms même en hiéroglyphes, s'ils entouré de guillemets, alors nous préférons nommer les objets sans ambiguïté et clairement afin qu'il n'y ait pas de divergences.
Regardons le plan résultant :

[regardez expliquer.tensor.ru]

144 ms et près de 53 XNUMX tampons - soit plus de 400 Mo de données ! Et nous aurons de la chance si tous sont dans le cache au moment de notre demande, sinon la lecture à partir du disque prendra beaucoup plus de temps.

L'algorithme est le plus important !

Afin d'optimiser d'une manière ou d'une autre une demande, vous devez d'abord comprendre ce qu'elle doit faire.
Laissons pour l’instant le développement de la structure de la base de données elle-même en dehors du cadre de cet article, et convenons que nous pouvons le faire relativement « à moindre coût » réécrire la demande et/ou rouler sur la base certaines des choses dont nous avons besoin Index.

Donc la demande :
— vérifie l'existence d'au moins un document
- dans l'état dont nous avons besoin et d'un certain type
- où l'auteur ou l'interprète est l'employé dont nous avons besoin

REJOINDRE + LIMITE 1

Très souvent, il est plus facile pour un développeur d'écrire une requête dans laquelle un grand nombre de tables sont d'abord jointes, puis un seul enregistrement reste de cet ensemble entier. Mais plus facile pour le développeur ne signifie pas plus efficace pour la base de données.
Dans notre cas, il n'y avait que 3 tables - et quel est l'effet...

Supprimons d'abord la connexion avec la table "Type de document", et indiquons en même temps à la base de données que notre enregistrement de type est unique (nous le savons, mais le planificateur n'en a pas encore la moindre idée) :

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

Oui, si la table/CTE se compose d'un seul champ d'un seul enregistrement, alors dans PG, vous pouvez même écrire comme ceci, au lieu de

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

Évaluation paresseuse dans les requêtes PostgreSQL

BitmapOr et UNION

Dans certains cas, Bitmap Heap Scan nous coûtera cher - par exemple, dans notre situation, lorsqu'un grand nombre d'enregistrements remplissent la condition requise. Nous l'avons eu parce que OU condition transformée en BitmapOr- fonctionnement en plan.
Revenons au problème initial : nous devons trouver un enregistrement correspondant à tout des conditions - c'est-à-dire qu'il n'est pas nécessaire de rechercher tous les 59 XNUMX enregistrements dans les deux conditions. Il existe un moyen de résoudre une condition, et aller au second seulement quand rien n'a été trouvé dans le premier. La conception suivante nous aidera :

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

« Externe » LIMIT 1 garantit que la recherche se termine lorsque le premier enregistrement est trouvé. Et s'il est déjà trouvé dans le premier bloc, le deuxième bloc ne sera pas exécuté (jamais exécuté dans le respect de).

« Cacher des conditions difficiles sous CASE »

Il y a un moment extrêmement gênant dans la requête d'origine : vérifier l'état par rapport à la table associée « DocumentExtension ». Indépendamment de la véracité des autres conditions de l'expression (par exemple, d. « Supprimé » N'EST PAS VRAI), cette connexion est toujours exécutée et « coûte des ressources ». Plus ou moins d'entre eux seront dépensés - cela dépend de la taille de ce tableau.
Mais vous pouvez modifier la requête pour que la recherche d'un enregistrement associé n'ait lieu que lorsque cela est vraiment nécessaire :

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

Une fois de la table liée à nous aucun des champs n'est nécessaire pour le résultat, nous avons alors la possibilité de transformer JOIN en condition sur une sous-requête.
Laissons les champs indexés "en dehors des crochets CASE", ajoutons des conditions simples de l'enregistrement au bloc WHEN - et maintenant la requête "lourde" n'est exécutée que lors du passage à ALORS.

Mon nom de famille est "Total"

Nous collectons la requête résultante avec toutes les mécaniques décrites ci-dessus :

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;

Ajustement [aux] index

Un œil exercé a remarqué que les conditions indexées dans les sous-blocs UNION sont légèrement différentes - c'est parce que nous avons déjà des index appropriés sur la table. Et s’ils n’existaient pas, cela vaudrait la peine de créer : Document (Personne3, Type de Document) и Document (Type de document, Employé).
à propos de l'ordre des champs dans les conditions ROWDu point de vue du planificateur, vous pouvez bien sûr écrire (A, B) = (constA, constB)Et (B, A) = (constB, constA). Mais lors de l'enregistrement dans l'ordre des champs dans l'index, une telle requête est tout simplement plus pratique à déboguer plus tard.
Qu'y a-t-il dans le plan ?

[regardez expliquer.tensor.ru]

Malheureusement, nous n'avons pas eu de chance et rien n'a été trouvé dans le premier bloc UNION, donc le deuxième a quand même été exécuté. Mais quand même - seulement 0.037 ms et 11 tampons!
Nous avons accéléré la requête et réduit le pompage des données en mémoire plusieurs milliers de fois, en utilisant des techniques assez simples - un bon résultat avec un peu de copier-coller. 🙂

Source: habr.com