PostgreSQL Antipatterns: επιβλαβείς JOIN και OR

Προσοχή στις λειτουργίες που φέρνουν buffer...
Χρησιμοποιώντας ένα μικρό ερώτημα ως παράδειγμα, ας δούμε μερικές καθολικές προσεγγίσεις για τη βελτιστοποίηση ερωτημάτων στο PostgreSQL. Το αν θα τα χρησιμοποιήσετε ή όχι εξαρτάται από εσάς, αλλά αξίζει να τα μάθετε.

Σε ορισμένες επόμενες εκδόσεις του PG η κατάσταση μπορεί να αλλάξει καθώς ο προγραμματιστής γίνεται πιο έξυπνος, αλλά για τις 9.4/9.6 φαίνεται περίπου το ίδιο, όπως στα παραδείγματα εδώ.

Ας πάρουμε ένα πολύ πραγματικό αίτημα:

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;

σχετικά με τα ονόματα πινάκων και πεδίωνΤα «ρωσικά» ονόματα πεδίων και πινάκων μπορούν να αντιμετωπιστούν διαφορετικά, αλλά αυτό είναι θέμα γούστου. Επειδή η εδώ στο Tensor δεν υπάρχουν ξένοι προγραμματιστές και η PostgreSQL μας επιτρέπει να δίνουμε ονόματα ακόμη και σε ιερογλυφικά, αν περικλείεται σε εισαγωγικά, τότε προτιμούμε να ονομάζουμε αντικείμενα ξεκάθαρα και ξεκάθαρα ώστε να μην υπάρχουν αποκλίσεις.
Ας δούμε το σχέδιο που προκύπτει:

[κοιτάξτε στο εξηγήστε.tensor.ru]

144ms και σχεδόν 53K buffer - δηλαδή περισσότερα από 400MB δεδομένων! Και θα είμαστε τυχεροί εάν όλα αυτά βρίσκονται στην κρυφή μνήμη μέχρι την ώρα του αιτήματός μας, διαφορετικά θα χρειαστεί πολλές φορές περισσότερος χρόνος κατά την ανάγνωση από το δίσκο.

Ο αλγόριθμος είναι το πιο σημαντικό!

Για να βελτιστοποιήσετε με κάποιο τρόπο οποιοδήποτε αίτημα, πρέπει πρώτα να καταλάβετε τι πρέπει να κάνει.
Ας αφήσουμε προς το παρόν την ανάπτυξη της ίδιας της δομής της βάσης δεδομένων εκτός του πεδίου εφαρμογής αυτού του άρθρου και ας συμφωνήσουμε ότι μπορούμε σχετικά «φθηνά» ξαναγράψτε το αίτημα και/ή κυλήστε στη βάση μερικά από τα πράγματα που χρειαζόμαστε Δείκτες.

Το αίτημα λοιπόν:
— ελέγχει την ύπαρξη τουλάχιστον κάποιου εγγράφου
- στην κατάσταση που χρειαζόμαστε και συγκεκριμένου τύπου
- όπου ο συγγραφέας ή ο ερμηνευτής είναι ο υπάλληλος που χρειαζόμαστε

ΕΓΓΡΑΦΗ + LIMIT 1

Αρκετά συχνά είναι ευκολότερο για έναν προγραμματιστή να γράψει ένα ερώτημα όπου ένας μεγάλος αριθμός πινάκων είναι αρχικά ενωμένος και μετά απομένει μόνο μία εγγραφή από ολόκληρο το σύνολο. Αλλά πιο εύκολο για τον προγραμματιστή δεν σημαίνει πιο αποτελεσματικό για τη βάση δεδομένων.
Στην περίπτωσή μας υπήρχαν μόνο 3 τραπέζια - και ποιο είναι το αποτέλεσμα...

Ας απαλλαγούμε πρώτα από τη σύνδεση με τον πίνακα "Τύπος εγγράφου" και ας πούμε ταυτόχρονα στη βάση δεδομένων ότι Το αρχείο τύπου μας είναι μοναδικό (το γνωρίζουμε αυτό, αλλά ο προγραμματιστής δεν έχει ιδέα ακόμα):

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

Ναι, αν ο πίνακας/CTE αποτελείται από ένα μόνο πεδίο μιας μεμονωμένης εγγραφής, τότε στο PG μπορείτε ακόμη και να γράψετε έτσι, αντί για

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

Τεμπέλης αξιολόγηση σε ερωτήματα PostgreSQL

BitmapOr εναντίον UNION

Σε ορισμένες περιπτώσεις, το Bitmap Heap Scan θα μας κοστίσει πολύ - για παράδειγμα, στην περίπτωσή μας, όταν πολλές εγγραφές πληρούν την απαιτούμενη προϋπόθεση. Το πήραμε γιατί Η συνθήκη OR μετατράπηκε σε BitmapOr- λειτουργία στο σχέδιο.
Ας επιστρέψουμε στο αρχικό πρόβλημα - πρέπει να βρούμε μια αντίστοιχη εγγραφή σε οποιονδήποτε από τις συνθήκες - δηλαδή, δεν χρειάζεται να αναζητήσετε όλες τις εγγραφές 59K και στις δύο συνθήκες. Υπάρχει τρόπος να επιλυθεί μια προϋπόθεση, και πηγαίνετε στο δεύτερο μόνο όταν δεν βρέθηκε τίποτα στο πρώτο. Ο παρακάτω σχεδιασμός θα μας βοηθήσει:

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

Το "External" LIMIT 1 διασφαλίζει ότι η αναζήτηση τελειώνει όταν βρεθεί η πρώτη εγγραφή. Και αν βρεθεί ήδη στο πρώτο μπλοκ, το δεύτερο μπλοκ δεν θα εκτελεστεί (δεν εκτελέστηκε ποτέ με σεβασμό σε).

«Απόκρυψη δύσκολων συνθηκών υπό ΠΕΡΙΠΤΩΣΗ»

Υπάρχει μια εξαιρετικά άβολη στιγμή στο αρχικό ερώτημα - έλεγχος της κατάστασης σε σχέση με τον σχετικό πίνακα "Επέκταση εγγράφου". Ανεξάρτητα από την αλήθεια άλλων συνθηκών στην έκφραση (για παράδειγμα, δ. «Διαγράφηκε» ΔΕΝ ΕΙΝΑΙ ΑΛΗΘΕΙΑ), αυτή η σύνδεση εκτελείται πάντα και «κοστίζει πόρους». Περισσότερα ή λιγότερα από αυτά θα δαπανηθούν - εξαρτάται από το μέγεθος αυτού του πίνακα.
Αλλά μπορείτε να τροποποιήσετε το ερώτημα έτσι ώστε η αναζήτηση για μια σχετική εγγραφή να πραγματοποιείται μόνο όταν είναι πραγματικά απαραίτητο:

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

Μόλις από τον συνδεδεμένο πίνακα σε εμάς κανένα από τα πεδία δεν χρειάζεται για το αποτέλεσμα, τότε έχουμε την ευκαιρία να μετατρέψουμε το JOIN σε συνθήκη σε ένα δευτερεύον ερώτημα.
Ας αφήσουμε τα ευρετηριασμένα πεδία "εκτός των αγκύλων CASE", προσθέστε απλές συνθήκες από την εγγραφή στο μπλοκ WHEN - και τώρα το ερώτημα "βαρύ" εκτελείται μόνο κατά τη μετάβαση στο THEN.

Το επώνυμό μου είναι "Total"

Συλλέγουμε το ερώτημα που προκύπτει με όλους τους μηχανισμούς που περιγράφονται παραπάνω:

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;

Προσαρμογή [σε] ευρετήρια

Ένα εκπαιδευμένο μάτι παρατήρησε ότι οι συνθήκες ευρετηρίου στα υπομπλοκ UNION είναι ελαφρώς διαφορετικές - αυτό συμβαίνει επειδή έχουμε ήδη κατάλληλα ευρετήρια στο τραπέζι. Και αν δεν υπήρχαν, θα άξιζε να δημιουργήσετε: Έγγραφο (Person3, DocumentType) и Έγγραφο (Τύπος εγγράφου, Υπάλληλος).
σχετικά με τη σειρά των πεδίων σε συνθήκες ROWΑπό την πλευρά του σχεδιαστή, φυσικά, μπορείτε να γράψετε (A, B) = (constA, constB)Και (B, A) = (constB, constA). Αλλά κατά την εγγραφή με τη σειρά των πεδίων στο ευρετήριο, ένα τέτοιο αίτημα είναι απλώς πιο βολικό για εντοπισμό σφαλμάτων αργότερα.
Τι υπάρχει στο σχέδιο;

[κοιτάξτε στο εξηγήστε.tensor.ru]

Δυστυχώς, ήμασταν άτυχοι και δεν βρέθηκε τίποτα στο πρώτο μπλοκ ΕΝΩΣΗΣ, οπότε το δεύτερο συνεχίστηκε να εκτελείται. Αλλά ακόμα κι έτσι - μόνο 0.037ms και 11 buffer!
Επιταχύναμε το αίτημα και μειώσαμε την άντληση δεδομένων στη μνήμη αρκετές χιλιάδες φορές, χρησιμοποιώντας αρκετά απλές τεχνικές - ένα καλό αποτέλεσμα με λίγη αντιγραφή-επικόλληση. 🙂

Πηγή: www.habr.com