Θυμάστε πώς ξεκίνησαν όλα. Όλα ήταν για πρώτη φορά και ξανά

Σχετικά με το πώς έπρεπε να αντιμετωπίσω τη βελτιστοποίηση ερωτημάτων PostgreSQL και τι προέκυψε από όλα αυτά.
Γιατί έπρεπε; Ναι, γιατί τα προηγούμενα 4 χρόνια όλα λειτουργούσαν ήσυχα, ήρεμα, σαν να χτυπάει το ρολόι.
ως επίγραφο.

Βασισμένο σε πραγματικά γεγονότα.
Όλα τα ονόματα έχουν αλλάξει, οι συμπτώσεις είναι τυχαίες.

Όταν επιτυγχάνεται ένα συγκεκριμένο αποτέλεσμα, είναι πάντα ενδιαφέρον να θυμόμαστε ποια ήταν η ώθηση για την αρχή, πώς ξεκίνησαν όλα.

Έτσι, αυτό που συνέβη ως αποτέλεσμα περιγράφεται εν συντομία στο άρθρο "Η σύνθεση ως μία από τις μεθόδους για τη βελτίωση της απόδοσης της PostgreSQL».

Μάλλον θα είναι ενδιαφέρον να αναδημιουργήσουμε την αλυσίδα των προηγούμενων γεγονότων.
Το ιστορικό κράτησε την ακριβή ημερομηνία έναρξης — 2018-09-10 18:02:48.
Επίσης, στην ιστορία υπάρχει ένα αίτημα από το οποίο ξεκίνησαν όλα:
Αίτημα προβλήματοςSELECT
σελ. "PARAMETER_ID" ως parameter_id,
σελ. "PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" ΩΣ RTD_value,
w. "LOWER_SPEC_LIMIT" ΩΣ κατώτερο_όριο_προδιαγραφών,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS spent_name,
s"SPENT_DATE" AS spent_date,
απόσπασμα (έτος από "SPENT_DATE") ΩΣ έτος,
απόσπασμα (μήνας από "SPENT_DATE") ως μήνα,
s"REPORT_NAME" AS report_name,
σελ. "STPM_NAME" AS stpm_name,
σ."CUSTOMERPARAM_NAME" AS πελάτηparam_name
ΑΠΟ wdataw,
ξόδεψε s,
pmtrp,
spent_pdsp,
πδ πδ
WHERE s."SPENT_ID" = w."SPENT_ID"
ΚΑΙ σ."PARAMETER_ID" = w"PARAMETER_ID"
AND s."SPENT_ID" = sp."SPENT_ID"
AND PD. "PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30'
και s."SPENT_DATE" = (ΕΠΙΛΟΓΗ ΜΕΓ.(s2."SPENT_DATE")
ΑΠΟ ξοδευμένο s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
ΚΑΙ w2."LRM" = w"LRM");

Περιγραφή του προβλήματος, αναμενόμενα τυπική - «Όλα είναι άσχημα. Πες μου ποιο είναι το πρόβλημα».
Θυμήθηκα αμέσως ένα αστείο από τους χρόνους των δίσκων 3 ιντσών:

Ο λαμπερός έρχεται στον χάκερ.
- Δεν μου δουλεύει τίποτα, πες μου πού είναι το πρόβλημα.
-Στο DNA...

Αλλά, φυσικά, αυτός δεν είναι ο τρόπος επίλυσης περιστατικών απόδοσης. "Μπορεί να μην μας καταλαβαίνουν" (Με). Πρέπει να το καταλάβουμε.
Λοιπόν, ας σκάψουμε. Ίσως αυτό να συσσωρευτεί ως αποτέλεσμα.

ξεκίνησε η επένδυση

Τι μπορεί λοιπόν να δει κανείς αμέσως με γυμνό μάτι, χωρίς καν να καταφύγει στη βοήθεια του ΕΞΗΓΗΣΗΣ.
1) Δεν χρησιμοποιούνται JOIN. Αυτό είναι κακό, ειδικά αν ο αριθμός των συνδέσεων είναι περισσότερες από μία.
2) Αλλά αυτό που είναι ακόμη χειρότερο - ένα συσχετισμένο υποερώτημα, επιπλέον, με τη συγκέντρωση. Αυτό είναι πολύ κακό.
Αυτό είναι κακό, φυσικά. Αλλά αυτό είναι μόνο από τη μια πλευρά. Από την άλλη, αυτό είναι πολύ καλό, γιατί το πρόβλημα έχει σαφώς λύση και το αίτημα μπορεί να βελτιωθεί.
Μην πας στον μάντη (Γ).
Το σχέδιο ερωτήματος δεν είναι τόσο περίπλοκο, αλλά αρκετά ενδεικτικό:
Σχέδιο εκτέλεσης

Το πιο ενδιαφέρον και χρήσιμο, ως συνήθως, στην αρχή και στο τέλος.
Ένθετος βρόχος (κόστος=935.84..479763226.18 σειρές=3322 πλάτος=135) (πραγματικός χρόνος=31.536..8220420.295 σειρές=8111656 βρόχοι=1)
Χρόνος προγραμματισμού: 3.807ms
Χρόνος εκτέλεσης: 8222351.640ms
Ο χρόνος εκτέλεσης είναι περισσότερο από 2 ώρες.

Ψεύτικες υποθέσεις που πήραν χρόνο

Υπόθεση 1- Ο βελτιστοποιητής είναι λάθος, χτίζει λάθος σχέδιο.

Για να οπτικοποιήσουμε το σχέδιο εκτέλεσης, θα χρησιμοποιήσουμε τον ιστότοπο https://explain.depesz.com/. Ωστόσο, ο ιστότοπος δεν έδειξε τίποτα ενδιαφέρον ή χρήσιμο. Με την πρώτη και τη δεύτερη ματιά - τίποτα που θα μπορούσε πραγματικά να βοηθήσει. Εκτός εάν - Η πλήρης σάρωση είναι ελάχιστη. Προχώρα.

Υπόθεση 2-Κρούση στη βάση από την πλευρά του αυτόματου υποπίεσης, πρέπει να απαλλαγείτε από τα φρένα.

Αλλά, οι δαίμονες του αυτόματου κενού συμπεριφέρονται καλά, δεν υπάρχουν μακροχρόνιες διαδικασίες. Οποιοδήποτε σοβαρό φορτίο - όχι. Πρέπει να ψάξετε για κάτι άλλο.

Υπόθεση 3-Οι στατιστικές είναι ξεπερασμένες, πρέπει να υπολογίσετε εκ νέου ό,τι πετάει

Και πάλι, όχι αυτό. Τα στατιστικά είναι ενημερωμένα. Κάτι που, δεδομένης της έλλειψης προβλημάτων με την αυτόματη ηλεκτρική σκούπα, δεν προκαλεί έκπληξη.

Ξεκινά η βελτιστοποίηση

Ο βασικός πίνακας 'wdata' σίγουρα δεν είναι μικρός, σχεδόν 3 εκατομμύρια εγγραφές.
Και σε αυτό το τραπέζι πηγαίνει το Full Scan.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") ΚΑΙ ((Subplan 1) = s."SPENT_DATE"))
-> Seq Scan σε wdata w (κόστος=0.00..574151.49 σειρές=26886249 πλάτος=46) (πραγματικός χρόνος=0.005..8153.565 σειρές=26873950 βρόχοι=1)
Λειτουργούμε ως πρότυπο: «ας κάνουμε ένα ευρετήριο και όλα πετούν».
Δημιούργησε ευρετήριο στο πεδίο "SPENT_ID"
Σαν άποτέλεσμα:
Σχέδιο εκτέλεσης ερωτήματος με χρήση ευρετηρίου

Λοιπόν, βοήθησε;
Ήταν: 8 222 351.640 ms (λίγο πάνω από 2 ώρες)
Εγινε: 6 985 431.575 ms (σχεδόν 2 ώρες)
Σε γενικές γραμμές, τα ίδια μήλα, πλάγια όψη.
Ας θυμηθούμε τα κλασικά:
«Έχεις το ίδιο, αλλά χωρίς φτερά; Θα αναζητήσει».

Κατ 'αρχήν, αυτό θα μπορούσε να ονομαστεί καλό αποτέλεσμα, καλά, όχι καλό, αλλά αποδεκτό. Τουλάχιστον, παρέχετε μια μεγάλη αναφορά στον πελάτη που περιγράφει πόσα έχουν γίνει και γιατί αυτό που γίνεται είναι καλό.
Ωστόσο, η τελική απόφαση είναι ακόμα μακριά. Πολύ μακριά.

Και τώρα το πιο ενδιαφέρον - συνεχίζουμε να βελτιστοποιούμε, θα γυαλίσουμε το ερώτημα

Βήμα πρώτο - χρησιμοποιήστε το JOIN

Ξαναγραμμένο ερώτημα, τώρα μοιάζει με αυτό (καλά τουλάχιστον πιο όμορφο):
Ερώτημα χρησιμοποιώντας JOINSELECT
σελ. "PARAMETER_ID" ως parameter_id,
σελ. "PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" ΩΣ RTD_value,
w. "LOWER_SPEC_LIMIT" ΩΣ κατώτερο_όριο_προδιαγραφών,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS spent_name,
s"SPENT_DATE" AS spent_date,
απόσπασμα (έτος από "SPENT_DATE") ΩΣ έτος,
απόσπασμα (μήνας από "SPENT_DATE") ως μήνα,
s"REPORT_NAME" AS report_name,
σελ. "STPM_NAME" AS stpm_name,
σ."CUSTOMERPARAM_NAME" AS πελάτηparam_name
FROM wdata w INNER JOIN ξοδεύτηκε s ON w."SPENT_ID"=s."SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pmtr p ON σελ."PARAMETER_ID" = w"PARAMETER_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ spent_pd sp ON s."SPENT_ID" = sp"SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pd pd ON pd."PD_ID" = sp."PD_ID"
ΠΟΥ
s."SPENT_DATE" >= '2018-07-01' ΚΑΙ s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (ΕΠΙΛΟΓΗ ΜΕΓ.(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN ξοδεύτηκε s2 ON w2."SPENT_ID"=s2."SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ wdata w
ON w2."LRM" = w"LRM" );
Χρόνος προγραμματισμού: 2.486ms
Χρόνος εκτέλεσης: 1223680.326ms

Ιδού λοιπόν το πρώτο αποτέλεσμα.
Ήταν: 6 985 431.575 ms (σχεδόν 2 ώρες).
Εγινε: 1 223 680.326 ms (λίγο περισσότερο από 20 λεπτά).
Καλό αποτέλεσμα. Κατ' αρχήν, πάλι, θα ήταν δυνατό να σταματήσουμε εκεί. Αλλά τόσο χωρίς ενδιαφέρον, δεν μπορείτε να σταματήσετε.
IBO

Βήμα δεύτερο - Ξεφορτωθείτε το συσχετιζόμενο υποερώτημα

Αλλαγμένο κείμενο αιτήματος:
Κανένα συσχετισμένο υποερώτημαSELECT
σελ. "PARAMETER_ID" ως parameter_id,
σελ. "PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" ΩΣ RTD_value,
w. "LOWER_SPEC_LIMIT" ΩΣ κατώτερο_όριο_προδιαγραφών,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS spent_name,
s"SPENT_DATE" AS spent_date,
απόσπασμα (έτος από "SPENT_DATE") ΩΣ έτος,
απόσπασμα (μήνας από "SPENT_DATE") ως μήνα,
s"REPORT_NAME" AS report_name,
σελ. "STPM_NAME" AS stpm_name,
σ."CUSTOMERPARAM_NAME" AS πελάτηparam_name
FROM wdata w INNER JOIN ξοδεύτηκε s ON s."SPENT_ID" = w."SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pmtr p ON σελ."PARAMETER_ID" = w"PARAMETER_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ spent_pd sp ON s."SPENT_ID" = sp"SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pd pd ON pd."PD_ID" = sp."PD_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ (ΕΠΙΛΟΓΗ w2."LRM", MAX(s2"SPENT_DATE")
FROM ξοδευμένο s2 Εσωτερική ΕΓΓΡΑΦΗ wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
ΟΜΑΔΑ ΑΠΟ w2.LRM
) md on w. "LRM" = md. "LRM"
ΠΟΥ
s"SPENT_DATE" >= '2018-07-01' ΚΑΙ s."SPENT_DATE" <= '2018-09-30';
Χρόνος προγραμματισμού: 2.291ms
Χρόνος εκτέλεσης: 165021.870ms

Ήταν: 1 223 680.326 ms (λίγο περισσότερο από 20 λεπτά).
Εγινε: 165 021.870 ms (λίγο περισσότερο από 2 λεπτά).
Αυτό είναι ήδη αρκετά καλό.
Ωστόσο, όπως λένε οι Άγγλοι,Όμως, υπάρχει πάντα ένα αλλά". Ένα πολύ καλό αποτέλεσμα θα πρέπει αυτόματα να προκαλεί υποψίες. Κάτι δεν πάει καλά εδώ.

Η υπόθεση για τη διόρθωση του ερωτήματος προκειμένου να απαλλαγούμε από το συσχετιζόμενο υποερώτημα είναι σωστή. Χρειάζεται όμως μια μικρή προσαρμογή για να βγει σωστά το τελικό αποτέλεσμα.
Ως αποτέλεσμα, το πρώτο ενδιάμεσο αποτέλεσμα:
Επεξεργασμένο ερώτημα χωρίς συσχετισμένο υποερώτημαSELECT
σελ. "PARAMETER_ID" ως parameter_id,
σελ. "PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" ΩΣ RTD_value,
w. "LOWER_SPEC_LIMIT" ΩΣ κατώτερο_όριο_προδιαγραφών,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS spent_name,
s"SPENT_DATE" AS spent_date,
απόσπασμα (έτος από s. "SPENT_DATE") ΩΣ έτος,
απόσπασμα (μήνας από s. "SPENT_DATE") ως μήνα,
s"REPORT_NAME" AS report_name,
σελ. "STPM_NAME" AS stpm_name,
σ."CUSTOMERPARAM_NAME" AS πελάτηparam_name
FROM wdata w INNER JOIN ξοδεύτηκε s ON s."SPENT_ID" = w."SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pmtr p ON σελ."PARAMETER_ID" = w"PARAMETER_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ spent_pd sp ON s."SPENT_ID" = sp"SPENT_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ pd pd ON pd."PD_ID" = sp."PD_ID"
ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ ( ΕΠΙΛΟΓΗ w2."LRM", MAX(s2."SPENT_DATE") ΩΣ "SPENT_DATE"
FROM ξοδευμένο s2 Εσωτερική ΕΓΓΡΑΦΗ wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
ΟΜΑΔΑ ΑΠΟ w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" ΚΑΙ md."LRM" = w"LRM"
ΠΟΥ
s"SPENT_DATE" >= '2018-07-01' ΚΑΙ s."SPENT_DATE" <= '2018-09-30';
Χρόνος προγραμματισμού: 3.192ms
Χρόνος εκτέλεσης: 208014.134ms

Έτσι, αυτό που έχουμε ως αποτέλεσμα είναι το πρώτο αποδεκτό αποτέλεσμα, το οποίο δεν ντρεπόμαστε να δείξουμε στον πελάτη:
Ξεκίνησε με: 8 222 351.640 ms (πάνω από 2 ώρες)
Επιτεύχθηκε: 1 ms (λίγο περισσότερο από 223 λεπτά).
Αποτέλεσμα (ενδιάμεσο): 208 014.134 ms (λίγο περισσότερο από 3 λεπτά).

Εξαιρετικό αποτέλεσμα.

Σύνολο

Αυτό θα μπορούσε να είχε σταματήσει.
ΑΛΛΑ…
Η όρεξη έρχεται με το φαγητό. Ο δρόμος θα κατακτηθεί περπατώντας. Οποιοδήποτε αποτέλεσμα είναι ενδιάμεσο. Σταμάτησε νεκρός. Και τα λοιπά.
Ας συνεχίσουμε με τη βελτιστοποίηση.
Υπέροχη ιδέα. Ειδικά αν σκεφτεί κανείς ότι ο πελάτης δεν ήταν καν εναντίον του. Και μάλιστα έντονα - για.

Έτσι, ήρθε η ώρα να επανασχεδιάσεις τη βάση δεδομένων. Η ίδια η δομή αιτήματος δεν μπορεί πλέον να βελτιστοποιηθεί (αν και, όπως αποδείχθηκε αργότερα, υπάρχει μια επιλογή για όλα να πετάξουν πραγματικά). Αλλά τώρα για να κάνουμε βελτιστοποίηση και ανάπτυξη του σχεδιασμού της βάσης δεδομένων, αυτή είναι ήδη μια πολλά υποσχόμενη ιδέα. Και το πιο σημαντικό ενδιαφέρον. Και πάλι, θυμηθείτε τα νιάτα. Άλλωστε, δεν έγινα αμέσως DBA, μεγάλωσα από προγραμματιστές (basic, assembler, si, si double plused, oracle, plsql). Ενδιαφέρον θέμα, φυσικά, για ξεχωριστά απομνημονεύματα ;-).
Ωστόσο, ας μην παρεκκλίνουμε.

Ετσι,

Και ίσως η τομή να μας βοηθήσει;
Spoiler - "Ναι, βοήθησε, και στη βελτιστοποίηση της απόδοσης, συμπεριλαμβανομένων."

Αλλά αυτή είναι μια εντελώς διαφορετική ιστορία...

Συνεχίζεται…

Πηγή: www.habr.com