PostgreSQL-Antipatterns: schädliche JOINs und ORs

Hüten Sie sich vor Vorgängen, die Puffer mit sich bringen ...
Schauen wir uns am Beispiel einer kleinen Abfrage einige universelle Ansätze zur Optimierung von Abfragen in PostgreSQL an. Ob Sie sie nutzen oder nicht, bleibt Ihnen überlassen, aber es lohnt sich, sie zu kennen.

In einigen nachfolgenden PG-Versionen kann sich die Situation ändern, wenn der Scheduler intelligenter wird, aber für 9.4/9.6 sieht es ungefähr gleich aus, wie in den Beispielen hier.

Nehmen wir eine ganz reale Bitte:

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;

über Tabellen- und FeldnamenDie „russischen“ Namen von Feldern und Tabellen können unterschiedlich behandelt werden, aber das ist Geschmackssache. Weil das hier bei Tensor Es gibt keine ausländischen Entwickler, und PostgreSQL ermöglicht es uns, Namen sogar in Hieroglyphen anzugeben, wenn dies der Fall ist in Anführungszeichen eingeschlossen, dann bevorzugen wir es, Objekte eindeutig und klar zu benennen, damit es keine Unstimmigkeiten gibt.
Schauen wir uns den resultierenden Plan an:

[siehe EXPLAIN.tensor.ru]

144 ms und fast 53 KB Puffer - also mehr als 400 MB Daten! Und wir haben Glück, wenn zum Zeitpunkt unserer Anfrage alle im Cache sind, sonst dauert das Lesen von der Festplatte um ein Vielfaches länger.

Der Algorithmus ist das Wichtigste!

Um eine Anfrage irgendwie zu optimieren, müssen Sie zunächst verstehen, was sie tun soll.
Lassen wir die Entwicklung der Datenbankstruktur selbst vorerst außerhalb des Rahmens dieses Artikels und sind uns einig, dass wir dies relativ „kostengünstig“ tun können. Schreiben Sie die Anfrage neu und/oder einige der Dinge, die wir brauchen, auf die Basis rollen Indizes.

Also die Anfrage:
— prüft die Existenz mindestens eines Dokuments
- in dem von uns benötigten Zustand und von einer bestimmten Art
- wo der Autor oder Interpret der Mitarbeiter ist, den wir brauchen

BEITRETEN + LIMIT 1

Für einen Entwickler ist es oft einfacher, eine Abfrage zu schreiben, bei der zunächst eine große Anzahl von Tabellen verknüpft wird und dann nur noch ein Datensatz aus diesem gesamten Satz übrig bleibt. Aber einfacher für den Entwickler bedeutet nicht, dass die Datenbank effizienter ist.
In unserem Fall gab es nur 3 Tische – und was ist der Effekt...

Lassen Sie uns zunächst die Verbindung mit der Tabelle „Dokumenttyp“ entfernen und dies gleichzeitig der Datenbank mitteilen Unser Typenrekord ist einzigartig (Wir wissen das, aber der Planer hat noch keine Ahnung):

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

Ja, wenn die Tabelle/CTE aus einem einzelnen Feld eines einzelnen Datensatzes besteht, können Sie in PG stattdessen sogar so schreiben

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

Verzögerte Auswertung in PostgreSQL-Abfragen

BitmapOr vs UNION

In einigen Fällen wird uns der Bitmap-Heap-Scan viel kosten – zum Beispiel in unserer Situation, wenn ziemlich viele Datensätze die erforderliche Bedingung erfüllen. Wir haben es bekommen, weil ODER-Bedingung wurde in BitmapOr umgewandelt- Betrieb im Plan.
Kehren wir zum ursprünglichen Problem zurück: Wir müssen einen entsprechenden Datensatz finden zu irgendwelchen Dies bedeutet, dass unter beiden Bedingungen nicht nach allen 59K-Datensätzen gesucht werden muss. Es gibt eine Möglichkeit, eine Bedingung zu klären, und Gehen Sie nur dann zum zweiten, wenn im ersten nichts gefunden wurde. Dabei hilft uns folgender Entwurf:

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

„Externes“ LIMIT 1 sorgt dafür, dass die Suche endet, wenn der erste Datensatz gefunden wird. Und wenn es bereits im ersten Block gefunden wird, wird der zweite Block nicht ausgeführt (nie ausgeführt In Planung).

„Schwierige Bedingungen unter CASE verbergen“

In der ursprünglichen Abfrage gibt es einen äußerst unbequemen Moment – ​​die Überprüfung des Status anhand der zugehörigen Tabelle „DocumentExtension“. Unabhängig von der Wahrheit anderer Bedingungen im Ausdruck (z. B. d„Gelöscht“ IST NICHT WAHR), wird diese Verbindung immer ausgeführt und „kostet Ressourcen“. Je nach Größe dieser Tabelle wird mehr oder weniger davon ausgegeben.
Sie können die Abfrage jedoch so ändern, dass die Suche nach einem zugehörigen Datensatz nur dann erfolgt, wenn dies wirklich erforderlich ist:

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

Einmal von der verlinkten Tabelle zu uns Keines der Felder wird für das Ergebnis benötigt, dann haben wir die Möglichkeit, JOIN in eine Bedingung für eine Unterabfrage umzuwandeln.
Lassen wir die indizierten Felder „außerhalb der CASE-Klammern“, fügen wir einfache Bedingungen aus dem Datensatz zum WHEN-Block hinzu – und jetzt wird die „schwere“ Abfrage nur bei der Übergabe an THEN ausgeführt.

Mein Nachname ist „Total“

Wir sammeln die resultierende Abfrage mit allen oben beschriebenen Mechanismen:

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;

Anpassen an Indizes

Einem geschulten Auge ist aufgefallen, dass die indizierten Bedingungen in den UNION-Unterblöcken leicht unterschiedlich sind – das liegt daran, dass wir bereits geeignete Indizes in der Tabelle haben. Und wenn es sie nicht gäbe, würde es sich lohnen, Folgendes zu schaffen: Dokument(Person3, DocumentType) и Dokument (Dokumenttyp, Mitarbeiter).
über die Reihenfolge der Felder in ROW-BedingungenAus Sicht des Planers ist es natürlich möglich, zu schreiben (A, B) = (constA, constB)Und (B, A) = (constB, constA). Aber bei der Aufnahme in der Reihenfolge der Felder im Index, eine solche Anfrage ist später einfach bequemer zu debuggen.
Was steht im Plan?

[siehe EXPLAIN.tensor.ru]

Leider hatten wir Pech und im ersten UNION-Block wurde nichts gefunden, sodass der zweite trotzdem ausgeführt wurde. Aber trotzdem – nur 0.037 ms und 11 Puffer!
Wir haben die Anfrage beschleunigt und das Datenpumpen im Speicher reduziert mehrere tausend Mal, mit relativ einfachen Techniken – ein gutes Ergebnis mit ein wenig Kopieren und Einfügen. 🙂

Source: habr.com