Περιοδικά, προκύπτει το έργο της αναζήτησης σχετικών δεδομένων χρησιμοποιώντας ένα σύνολο κλειδιών. μέχρι να λάβουμε τον απαιτούμενο συνολικό αριθμό εγγραφών.
Το πιο «πραγματικό» παράδειγμα είναι να εμφανιστεί Τα 20 παλαιότερα προβλήματα, παρατίθεται στον κατάλογο των εργαζομένων (για παράδειγμα, μέσα σε ένα τμήμα). Για διάφορους «πίνακες εργαλείων» διαχείρισης με σύντομες περιλήψεις των περιοχών εργασίας, ένα παρόμοιο θέμα απαιτείται αρκετά συχνά.
Σε αυτό το άρθρο θα εξετάσουμε την υλοποίηση στην PostgreSQL μιας «αφελούς» λύσης σε ένα τέτοιο πρόβλημα, ενός «εξυπνότερου» και πολύ περίπλοκου αλγορίθμου "βρόχος" σε SQL με συνθήκη εξόδου από τα δεδομένα που βρέθηκαν, το οποίο μπορεί να είναι χρήσιμο τόσο για γενική ανάπτυξη όσο και για χρήση σε άλλες παρόμοιες περιπτώσεις.
Ας πάρουμε ένα σύνολο δεδομένων δοκιμής από . Για να αποτρέψετε τις εμφανιζόμενες εγγραφές να "πηδούν" από καιρό σε καιρό όταν συμπίπτουν οι ταξινομημένες τιμές, αναπτύξτε το ευρετήριο θέματος προσθέτοντας ένα πρωτεύον κλειδί. Ταυτόχρονα, αυτό θα του δώσει αμέσως μοναδικότητα και θα μας εγγυηθεί ότι η σειρά ταξινόμησης είναι ξεκάθαρη:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Όπως ακούγεται, έτσι γράφεται
Αρχικά, ας σκιαγραφήσουμε την απλούστερη έκδοση του αιτήματος, περνώντας τα αναγνωριστικά των ερμηνευτών :
SELECT
*
FROM
task
WHERE
owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
task_date, id
LIMIT 20;
Λίγο λυπηρό - παραγγείλαμε μόνο 20 δίσκους, αλλά το Index Scan μας το επέστρεψε 960 γραμμές, που στη συνέχεια έπρεπε επίσης να ταξινομηθεί... Ας προσπαθήσουμε να διαβάζουμε λιγότερο.
unnest + ARRAY
Η πρώτη σκέψη που θα μας βοηθήσει είναι αν χρειαστούμε μόνο 20 ταξινομημένα εγγραφές, μετά απλά διαβάστε όχι περισσότερα από 20 ταξινομημένα με την ίδια σειρά για το καθένα κλειδί. Καλός, κατάλληλος δείκτης (owner_id, task_date, id) έχουμε.
Ας χρησιμοποιήσουμε τον ίδιο μηχανισμό για την εξαγωγή και την "εξάπλωση σε στήλες" εγγραφή ολοκληρωμένου πίνακα, όπως λέμε . Μπορούμε επίσης να εφαρμόσουμε δίπλωμα σε έναν πίνακα χρησιμοποιώντας τη συνάρτηση ARRAY():
WITH T AS (
SELECT
unnest(ARRAY(
SELECT
t
FROM
task t
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 20 -- ограничиваем тут...
)) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
(r).*
FROM
T
ORDER BY
(r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже
Α, πολύ καλύτερα ήδη! 40% ταχύτερα και 4.5 φορές λιγότερα δεδομένα Έπρεπε να το διαβάσω.
Υλοποίηση εγγραφών πίνακα μέσω CTEΕπιτρέψτε μου να επιστήσω την προσοχή σας στο γεγονός ότι σε ορισμένες περιπτώσεις Μια προσπάθεια άμεσης εργασίας με τα πεδία μιας εγγραφής μετά την αναζήτησή της σε ένα υποερώτημα, χωρίς να την «τυλίξετε» σε ένα CTE, μπορεί να οδηγήσει σε "πολλαπλασιάστε" το InitPlan ανάλογο με τον αριθμό των ίδιων πεδίων:
SELECT
((
SELECT
t
FROM
task t
WHERE
owner_id = 1
ORDER BY
task_date, id
LIMIT 1
).*);Result (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
Buffers: shared hit=16
InitPlan 1 (returns $0)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 2 (returns $1)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_1 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 3 (returns $2)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_2 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4"
InitPlan 4 (returns $3)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_3 (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
Η ίδια εγγραφή "αναζήτησε" 4 φορές... Μέχρι την PostgreSQL 11, αυτή η συμπεριφορά εμφανίζεται τακτικά και η λύση είναι να την "τυλίξετε" σε ένα CTE, το οποίο είναι ένα απόλυτο όριο για το βελτιστοποιητή σε αυτές τις εκδόσεις.
Αναδρομικός συσσωρευτής
Στην προηγούμενη έκδοση, συνολικά διαβάσαμε 200 γραμμές για χάρη των απαιτούμενων 20. Όχι 960, αλλά ακόμα λιγότερο - είναι δυνατόν;
Ας προσπαθήσουμε να χρησιμοποιήσουμε τη γνώση που χρειαζόμαστε σύνολο 20 εγγραφές. Δηλαδή, θα επαναλάβουμε την ανάγνωση δεδομένων μόνο μέχρι να φτάσουμε το ποσό που χρειαζόμαστε.
Βήμα 1: Λίστα εκκίνησης
Προφανώς, η λίστα "στόχων" μας με 20 εγγραφές θα πρέπει να ξεκινά με τις "πρώτες" εγγραφές για ένα από τα κλειδιά owner_id μας. Επομένως, πρώτα θα βρούμε τέτοια "πολύ πρώτο" για κάθε ένα από τα κλειδιά και το προσθέτουμε στη λίστα, ταξινομώντας το με τη σειρά που θέλουμε - (ημερομηνία_εργασίας, id).
Βήμα 2: Βρείτε τις «επόμενες» καταχωρήσεις
Τώρα αν πάρουμε την πρώτη καταχώρηση από τη λίστα μας και ξεκινήσουμε "βήμα" περαιτέρω κατά μήκος του δείκτη διατηρώντας το κλειδί owner_id, τότε όλες οι εγγραφές που βρέθηκαν είναι ακριβώς οι επόμενες στην επιλογή που προκύπτει. Φυσικά, μόνο μέχρι να περάσουμε το κλειδί του κοντακιού δεύτερη εγγραφή στη λίστα.
Αν αποδειχτεί ότι «περάσαμε» το δεύτερο ρεκόρ, τότε η τελευταία καταχώριση που διαβάστηκε θα πρέπει να προστεθεί στη λίστα αντί για την πρώτη (με το ίδιο ιδιοκτήτη_αναγνωριστικό), μετά από το οποίο κάνουμε εκ νέου ταξινόμηση της λίστας ξανά.
Δηλαδή, πάντα καταλαβαίνουμε ότι η λίστα δεν έχει περισσότερες από μία καταχωρίσεις για κάθε ένα από τα κλειδιά (αν οι καταχωρήσεις εξαντληθούν και δεν "σταυρώσουμε", τότε η πρώτη καταχώρηση από τη λίστα απλά θα εξαφανιστεί και δεν θα προστεθεί τίποτα ), και αυτοί πάντα ταξινομημένο με αύξουσα σειρά του κλειδιού εφαρμογής (ημερομηνία_εργασίας, id).
Βήμα 3: φιλτράρισμα και «ανάπτυξη» εγγραφών
Σε ορισμένες από τις σειρές της αναδρομικής επιλογής μας, ορισμένες εγγραφές rv είναι διπλότυπα - πρώτα βρίσκουμε όπως "διάβαση των ορίων της 2ης καταχώρισης της λίστας" και μετά την αντικαθιστούμε ως την 1η από τη λίστα. Επομένως, το πρώτο περιστατικό πρέπει να φιλτραριστεί.
Το τρομακτικό τελικό ερώτημα
WITH RECURSIVE T AS (
-- #1 : заносим в список "первые" записи по каждому из ключей набора
WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
WITH T AS (
SELECT
(
SELECT
r
FROM
task r
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 1
) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
FROM
T
)
SELECT
list
, list[1] rv
, FALSE not_cross
, 0 size
FROM
wrap
UNION ALL
-- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
SELECT
CASE
-- если ничего не найдено для ключа 1-й записи
WHEN X._r IS NOT DISTINCT FROM NULL THEN
T.list[2:] -- убираем ее из списка
-- если мы НЕ пересекли прикладной ключ 2-й записи
WHEN X.not_cross THEN
T.list -- просто протягиваем тот же список без модификаций
-- если в списке уже нет 2-й записи
WHEN T.list[2] IS NULL THEN
-- просто возвращаем пустой список
'{}'
-- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
ELSE (
SELECT
coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
FROM
unnest(T.list[3:] || X._r) r
)
END
, X._r
, X.not_cross
, T.size + X.not_cross::integer
FROM
T
, LATERAL(
WITH wrap AS ( -- "материализуем" record
SELECT
CASE
-- если все-таки "перешагнули" через 2-ю запись
WHEN NOT T.not_cross
-- то нужная запись - первая из спписка
THEN T.list[1]
ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
SELECT
_r
FROM
task _r
WHERE
owner_id = (rv).owner_id AND
(task_date, id) > ((rv).task_date, (rv).id)
ORDER BY
task_date, id
LIMIT 1
)
END _r
)
SELECT
_r
, CASE
-- если 2-й записи уже нет в списке, но мы хоть что-то нашли
WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
TRUE
ELSE -- ничего не нашли или "перешагнули"
coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
END not_cross
FROM
wrap
) X
WHERE
T.size < 20 AND -- ограничиваем тут количество
T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
(rv).*
FROM
T
WHERE
not_cross; -- берем только "непересекающие" записи
Έτσι, εμείς αντάλλαξε το 50% των αναγνώσεων δεδομένων για το 20% του χρόνου εκτέλεσης. Δηλαδή, εάν έχετε λόγους να πιστεύετε ότι η ανάγνωση μπορεί να διαρκέσει πολύ (για παράδειγμα, τα δεδομένα συχνά δεν βρίσκονται στη μνήμη cache και πρέπει να πάτε στο δίσκο για αυτό), τότε με αυτόν τον τρόπο μπορείτε να εξαρτηθείτε λιγότερο από την ανάγνωση .
Σε κάθε περίπτωση, ο χρόνος εκτέλεσης αποδείχθηκε καλύτερος από ό, τι στην "αφελή" πρώτη επιλογή. Αλλά ποια από αυτές τις 3 επιλογές να χρησιμοποιήσετε εξαρτάται από εσάς.
Πηγή: www.habr.com