PostgreSQL Antipatterns: "Πρέπει να υπάρχει μόνο ένα!"

Στην SQL, περιγράφετε «τι» θέλετε να επιτύχετε, όχι «πώς» πρέπει να εκτελεστεί. Ως εκ τούτου, το πρόβλημα της ανάπτυξης ερωτημάτων SQL στο στυλ "όπως ακούγεται είναι πώς γράφεται" παίρνει τη θέση της τιμής του, μαζί με χαρακτηριστικά υπολογισμού συνθηκών σε SQL.

Σήμερα, χρησιμοποιώντας εξαιρετικά απλά παραδείγματα, ας δούμε σε τι μπορεί να οδηγήσει αυτό στο πλαίσιο χρήσης GROUP/DISTINCT и LIMIT με αυτούς.

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

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

Λοιπόν, ίσως όχι τόσο θεαματικό, αλλά...

“Γλυκό ζευγάρι”: ΕΓΓΡΑΦΕΙ + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Θα ήταν ξεκάθαρο τι ήθελαν επιλέξτε εγγραφές X για τις οποίες υπάρχουν εγγραφές στο Y που σχετίζονται με την εκπληρωμένη συνθήκη. Έγραψε ένα αίτημα μέσω JOIN — πήρε μερικές τιμές pk αρκετές φορές (ακριβώς πόσες κατάλληλες καταχωρήσεις εμφανίστηκαν στο Y). Πώς να αφαιρέσετε; Σίγουρα DISTINCT!

Είναι ιδιαίτερα «ευχαριστικό» όταν για κάθε X-record υπάρχουν αρκετές εκατοντάδες σχετικές εγγραφές Y, και στη συνέχεια τα αντίγραφα αφαιρούνται ηρωικά...

Πώς να φτιάξεις? Αρχικά, συνειδητοποιήστε ότι το πρόβλημα μπορεί να τροποποιηθεί "επιλέξτε εγγραφές X για τις οποίες στο Y υπάρχει ΤΟΥΛΑΧΙΣΤΟΝ ΕΝΑ που σχετίζεται με την εκπληρωμένη συνθήκη" - Εξάλλου, δεν χρειαζόμαστε τίποτα από την ίδια την εγγραφή Y.

Ένθετο ΥΠΑΡΧΕΙ

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Ορισμένες εκδόσεις της PostgreSQL καταλαβαίνουν ότι στο EXISTS αρκεί να βρείτε την πρώτη καταχώρηση που εμφανίζεται, οι παλαιότερες όχι. Ως εκ τούτου, προτιμώ να αναφέρω πάντα LIMIT 1 μέσα EXISTS.

ΠΛΑΪΡΗ ΣΥΝΔΕΣΗ

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Η ίδια επιλογή επιτρέπει, εάν είναι απαραίτητο, την άμεση επιστροφή ορισμένων δεδομένων από τη συσχετισμένη εγγραφή Υ που βρέθηκε. Μια παρόμοια επιλογή συζητείται στο άρθρο "PostgreSQL Antipatterns: μια σπάνια εγγραφή θα φτάσει στη μέση ενός JOIN".

"Γιατί να πληρώσετε περισσότερα": DISTINCT [ON] + LIMIT 1

Ένα πρόσθετο πλεονέκτημα τέτοιων μετασχηματισμών ερωτημάτων είναι η δυνατότητα να περιοριστεί εύκολα η αναζήτηση για εγγραφές εάν χρειάζονται μόνο μία ή μερικές από αυτές, όπως στην ακόλουθη περίπτωση:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Τώρα διαβάζουμε το αίτημα και προσπαθούμε να καταλάβουμε τι προτείνεται να κάνει το DBMS:

• συνδέοντας τις πινακίδες
• μοναδικό από X.pk
• από τις υπόλοιπες καταχωρήσεις, επιλέξτε μία

Τι πήρες λοιπόν; "Μόνο μία είσοδος" από τα μοναδικά - και αν πάρουμε αυτό από τα μη μοναδικά, θα αλλάξει κάπως το αποτέλεσμα;.. «Και αν δεν υπάρχει διαφορά, γιατί να πληρώσουμε περισσότερα;»

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Και ακριβώς το ίδιο θέμα με GROUP BY + LIMIT 1.

"Απλώς πρέπει να ρωτήσω": σιωπηρή ΟΜΑΔΑ + ΟΡΙΟ

Παρόμοια πράγματα συμβαίνουν σε διαφορετικά έλεγχοι μη κενού υπογράφει ή CTE καθώς εξελίσσεται το αίτημα:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Συγκεντρωτικές συναρτήσεις (count/min/max/sum/...) εκτελούνται με επιτυχία σε ολόκληρο το σετ, ακόμη και χωρίς ρητές οδηγίες GROUP BY. Μόνο με LIMIT δεν είναι πολύ φιλικοί.

Ο προγραμματιστής μπορεί να σκεφτεί "Εάν υπάρχουν αρχεία εκεί, τότε δεν χρειάζομαι περισσότερα από LIMIT". Αλλά μην το κάνεις αυτό! Γιατί για τη βάση είναι:

• μετράνε τι θέλουν σύμφωνα με όλα τα αρχεία
• δώστε όσες γραμμές ζητήσουν

Ανάλογα με τις συνθήκες-στόχους, είναι σκόπιμο να γίνει μία από τις ακόλουθες αντικαταστάσεις:

• (count + LIMIT 1) = 0 επί NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 επί EXISTS(LIMIT 1)
• count >= N επί (SELECT count(*) FROM (... LIMIT N))

“Πόσο να κρεμαστεί σε γραμμάρια”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ένας αφελής προγραμματιστής μπορεί να πιστεύει ειλικρινά ότι το αίτημα θα σταματήσει να εκτελείται. μόλις βρούμε $1 από τις πρώτες διαφορετικές τιμές που συναντάμε.

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

Προς το παρόν πρώτα όλες οι εγγραφές θα ανακτηθούν, είναι μοναδικά και μόνο από αυτά θα επιστραφεί το ποσό που ζητήθηκε. Είναι ιδιαίτερα λυπηρό αν θέλαμε κάτι τέτοιο $ 1 = 4, και υπάρχουν εκατοντάδες χιλιάδες εγγραφές στον πίνακα...

Για να μην στεναχωριόμαστε μάταια, ας χρησιμοποιήσουμε ένα αναδρομικό ερώτημα Το "DISTINCT είναι για τους φτωχούς" από το PostgreSQL Wiki:

Πηγή: www.habr.com