ProHoster > Blog > διαχείριση > Αντιπρότυπα PostgreSQL: CTE x CTE

Αντιπρότυπα PostgreSQL: CTE x CTE

Λόγω της δουλειάς μου, πρέπει να αντιμετωπίσω καταστάσεις όταν ένας προγραμματιστής γράφει ένα αίτημα και σκέφτεται "Η βάση είναι έξυπνη, μπορεί να χειριστεί τα πάντα μόνη της!«

Σε ορισμένες περιπτώσεις (εν μέρει από άγνοια των δυνατοτήτων της βάσης δεδομένων, εν μέρει από πρόωρες βελτιστοποιήσεις), αυτή η προσέγγιση οδηγεί στην εμφάνιση των «Φρανκενστάιν».

Αρχικά, θα δώσω ένα παράδειγμα τέτοιου αιτήματος:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

Για να αξιολογήσουμε ουσιαστικά την ποιότητα ενός αιτήματος, ας δημιουργήσουμε κάποιο αυθαίρετο σύνολο δεδομένων:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

Τελικά φαίνεται πως η ανάγνωση των δεδομένων χρειάστηκε λιγότερο από το ένα τέταρτο του χρόνου εκτέλεση ερωτήματος:

Αντιπρότυπα PostgreSQL: CTE x CTE[κοιτάξτε στο εξηγήστε.tensor.ru]

Ξεκολλώντας το κομμάτι-κομμάτι

Ας ρίξουμε μια πιο προσεκτική ματιά στο αίτημα και ας μπερδευτούμε:

  1. Γιατί είναι το WITH RECURSIVE εδώ αν δεν υπάρχουν αναδρομικά CTE;
  2. Γιατί να ομαδοποιήσετε τις ελάχιστες/μέγιστες τιμές σε ένα ξεχωριστό CTE εάν στη συνέχεια συνδέονται με το αρχικό δείγμα ούτως ή άλλως;
    +25% χρόνος
  3. Γιατί να χρησιμοποιήσετε ένα άνευ όρων «SELECT * FROM» στο τέλος για να επαναλάβετε το προηγούμενο CTE;
    +14% χρόνος

Σε αυτήν την περίπτωση, ήμασταν πολύ τυχεροί που επιλέχθηκε το Hash Join για τη σύνδεση και όχι το Nested Loop, γιατί τότε θα είχαμε λάβει όχι μόνο ένα πέρασμα σάρωσης CTE, αλλά 10K!

λίγα λόγια για τη σάρωση CTEΕδώ πρέπει να το θυμόμαστε Το CTE Scan είναι παρόμοιο με το Seq Scan - δηλαδή χωρίς ευρετηρίαση, αλλά μόνο πλήρη αναζήτηση, που θα απαιτούσε 10K x 0.3ms = 3000ms για κύκλους κατά cte_max ή 1K x 1.5ms = 1500ms κατά τον βρόχο με cte_bind!
Στην πραγματικότητα, τι θέλατε να πάρετε ως αποτέλεσμα; Ναι, συνήθως αυτή είναι η ερώτηση που τίθεται κάπου στο 5ο λεπτό της ανάλυσης ερωτημάτων «τριών ιστοριών».

Θέλαμε να κάνουμε έξοδο για κάθε μοναδικό ζεύγος κλειδιών min/max από ομάδα κατά key_a.
Ας το χρησιμοποιήσουμε λοιπόν για αυτό λειτουργίες παραθύρου:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

Αντιπρότυπα PostgreSQL: CTE x CTE
[κοιτάξτε στο εξηγήστε.tensor.ru]

Δεδομένου ότι η ανάγνωση δεδομένων και στις δύο επιλογές διαρκεί τα ίδια περίπου 4-5 ms, τότε όλος ο χρόνος μας κερδίζει -32% - αυτό είναι στην πιο αγνή του μορφή αφαιρέθηκε το φορτίο από τη βασική CPU, εάν ένα τέτοιο αίτημα εκτελείται αρκετά συχνά.

Γενικά, δεν πρέπει να πιέζετε τη βάση να «κουβαλήσει το στρογγυλό, να κυλήσει το τετράγωνο».

Πηγή: www.habr.com

Προσθέστε ένα σχόλιο