Στον κόσμο της Postgres, τα ευρετήρια είναι απαραίτητα για την αποτελεσματική πλοήγηση στην αποθήκευση της βάσης δεδομένων (που ονομάζεται "σωρός"). Το Postgres δεν υποστηρίζει ομαδοποίηση για αυτό και η αρχιτεκτονική MVCC σας κάνει να καταλήξετε με πολλές εκδόσεις της ίδιας πλειάδας. Επομένως, είναι πολύ σημαντικό να μπορείτε να δημιουργείτε και να διατηρείτε αποτελεσματικά ευρετήρια για την υποστήριξη εφαρμογών.
Ακολουθούν ορισμένες συμβουλές για τη βελτιστοποίηση και τη βελτίωση της χρήσης των ευρετηρίων.
Σημείωση: τα ερωτήματα που εμφανίζονται παρακάτω λειτουργούν σε μη τροποποιημένο .
Χρήση ευρετηρίων κάλυψης
Ας δούμε ένα αίτημα εξαγωγής διευθύνσεων email για ανενεργούς χρήστες. Τραπέζι customer υπάρχει μια στήλη active, και το ερώτημα είναι απλό:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
Το ερώτημα καλεί την πλήρη ακολουθία σάρωσης πίνακα customer. Ας δημιουργήσουμε ένα ευρετήριο σε μια στήλη active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Βοήθησε, η επακόλουθη σάρωση μετατράπηκε σε "index scan". Αυτό σημαίνει ότι η Postgres θα σαρώσει το ευρετήριο "idx_cust1" και, στη συνέχεια, συνεχίστε την αναζήτηση στο σωρό του πίνακα για να διαβάσετε τις τιμές άλλων στηλών (σε αυτήν την περίπτωση, η στήλη email) που χρειάζεται το ερώτημα.
Τα ευρετήρια κάλυψης εισάγονται στο PostgreSQL 11. Σας επιτρέπουν να συμπεριλάβετε μία ή περισσότερες πρόσθετες στήλες στο ίδιο το ευρετήριο - οι τιμές τους αποθηκεύονται στο χώρο αποθήκευσης δεδομένων ευρετηρίου.
Εάν εκμεταλλευόμασταν αυτή τη δυνατότητα και προσθέταμε την τιμή email μέσα στο ευρετήριο, τότε η Postgres δεν θα χρειαζόταν να αναζητήσει την τιμή στο σωρό του πίνακα. email. Ας δούμε αν αυτό θα λειτουργήσει:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan' μας λέει ότι το ερώτημα τώρα χρειάζεται μόνο ένα ευρετήριο, το οποίο βοηθά στην αποφυγή όλων των εισόδων/εξόδων του δίσκου για την ανάγνωση του σωρού πίνακα.
Οι δείκτες κάλυψης είναι προς το παρόν διαθέσιμοι μόνο για Β-δέντρα. Ωστόσο, σε αυτή την περίπτωση, η προσπάθεια συντήρησης θα είναι μεγαλύτερη.
Χρήση μερικών ευρετηρίων
Μερικά ευρετήρια ευρετηριάζουν μόνο ένα υποσύνολο των σειρών σε έναν πίνακα. Αυτό εξοικονομεί το μέγεθος των ευρετηρίων και κάνει τις σαρώσεις πιο γρήγορες.
Ας υποθέσουμε ότι θέλουμε να λάβουμε μια λίστα με τις διευθύνσεις ηλεκτρονικού ταχυδρομείου των πελατών μας στην Καλιφόρνια. Το αίτημα θα είναι ως εξής:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)Τι θα μας δώσουν οι συνηθισμένοι δείκτες:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
Σάρωση address έχει αντικατασταθεί από σάρωση ευρετηρίου idx_address1και μετά σάρωση του σωρού address.
Δεδομένου ότι αυτό είναι ένα συχνό ερώτημα και πρέπει να βελτιστοποιηθεί, μπορούμε να χρησιμοποιήσουμε ένα μερικό ευρετήριο, το οποίο ευρετηριάζει μόνο εκείνες τις σειρές με διευθύνσεις στις οποίες η περιοχή ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
Τώρα το ερώτημα διαβάζει μόνο idx_address2 και δεν αγγίζει το τραπέζι address.
Χρήση δεικτών πολλαπλών τιμών
Ορισμένες στήλες που πρόκειται να ευρετηριαστούν ενδέχεται να μην περιέχουν βαθμωτό τύπο δεδομένων. Τύποι στηλών όπως jsonb, arrays и tsvector περιέχουν σύνθετες ή πολλαπλές τιμές. Εάν χρειάζεται να καταχωρίσετε τέτοιες στήλες, συνήθως πρέπει να αναζητήσετε όλες τις μεμονωμένες τιμές σε αυτές τις στήλες.
Ας προσπαθήσουμε να βρούμε τους τίτλους όλων των ταινιών που περιέχουν περικοπές από ανεπιτυχείς λήψεις. Τραπέζι film υπάρχει μια στήλη κειμένου που ονομάζεται special_features. Εάν η ταινία έχει αυτήν την "ειδική ιδιότητα", τότε η στήλη περιέχει το στοιχείο ως πίνακα κειμένου Behind The Scenes. Για να αναζητήσουμε όλες αυτές τις ταινίες, πρέπει να επιλέξουμε όλες τις σειρές με "Behind The Scenes" όταν οποιαδήποτε τιμές πίνακα special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Χειριστής ένθεσης @> ελέγχει εάν η δεξιά πλευρά είναι υποσύνολο της αριστερής πλευράς.
Ζητήστε σχέδιο:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Το οποίο ζητά μια πλήρη σάρωση σωρού με κόστος 67.
Ας δούμε αν ένας κανονικός δείκτης B-tree μας βοηθά:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Ο δείκτης δεν ελήφθη καν υπόψη. Ο δείκτης B-tree δεν γνωρίζει την ύπαρξη μεμονωμένων στοιχείων στις τιμές με ευρετήριο.
Χρειαζόμαστε έναν δείκτη GIN.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)Ο δείκτης GIN υποστηρίζει την αντιστοίχιση μεμονωμένων τιμών σε συνάρτηση με τις σύνθετες τιμές με ευρετήριο, με αποτέλεσμα ένα κόστος σχεδίου ερωτήματος που είναι περισσότερο από το μισό.
Απαλλαγή από διπλότυπα ευρετήρια
Τα ευρετήρια συσσωρεύονται με την πάροδο του χρόνου και μερικές φορές ένα νέο ευρετήριο μπορεί να περιέχει τον ίδιο ορισμό με έναν από τους προηγούμενους. Μπορείτε να χρησιμοποιήσετε την προβολή καταλόγου για να λάβετε αναγνώσιμους από τον άνθρωπο ορισμούς SQL ευρετηρίων. pg_indexes. Μπορείτε επίσης εύκολα να βρείτε πανομοιότυπους ορισμούς:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Δείκτες Superset
Μπορεί να συμβεί να καταλήξετε με πολλά ευρετήρια, ένα από τα οποία ευρετηριάζει ένα υπερσύνολο στηλών που ευρετηριάζει άλλα ευρετήρια. Αυτό μπορεί να είναι ή να μην είναι επιθυμητό - το υπερσύνολο μπορεί να έχει ως αποτέλεσμα σαρώσεις μόνο με ευρετήριο, κάτι που είναι καλό, αλλά μπορεί να καταλαμβάνει πολύ χώρο ή το ερώτημα που προοριζόταν να βελτιστοποιήσει το υπερσύνολο δεν χρησιμοποιείται πλέον.
Εάν χρειάζεται να αυτοματοποιήσετε τον ορισμό τέτοιων ευρετηρίων, μπορείτε να ξεκινήσετε με από το τραπέζι pg_catalog.
Αχρησιμοποίητα ευρετήρια
Καθώς οι εφαρμογές που χρησιμοποιούν βάσεις δεδομένων εξελίσσονται, το ίδιο συμβαίνει και με τα ερωτήματα που χρησιμοποιούν. Τα ευρετήρια που προστέθηκαν νωρίτερα δεν μπορούν πλέον να χρησιμοποιούνται από κανένα ερώτημα. Κάθε φορά που σαρώνεται ένα ευρετήριο, επισημαίνεται από τον διαχειριστή στατιστικών στοιχείων και στην προβολή καταλόγου συστήματος pg_stat_user_indexes μπορείτε να δείτε την τιμή idx_scan, που είναι ένας αθροιστικός μετρητής. Η παρακολούθηση αυτής της τιμής για μια χρονική περίοδο (ας πούμε έναν μήνα) θα δώσει μια καλή ιδέα για το ποια ευρετήρια δεν χρησιμοποιούνται και θα μπορούσαν να απορριφθούν.
Ακολουθεί ένα ερώτημα για να λάβετε τις τρέχουσες μετρήσεις σάρωσης όλων των ευρετηρίων στο σχήμα ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)Ανακατασκευή ευρετηρίων με λιγότερα λουκέτα
Τα ευρετήρια χρειάζονται συχνά ανακατασκευή, για παράδειγμα όταν φουσκώνουν και η αναδόμηση μπορεί να επιταχύνει τη σάρωση. Επίσης, τα ευρετήρια μπορεί να καταστραφούν. Η αλλαγή των παραμέτρων ευρετηρίου μπορεί επίσης να απαιτεί την ανακατασκευή του.
Ενεργοποίηση παράλληλης δημιουργίας ευρετηρίου
Στο PostgreSQL 11, η δημιουργία ενός ευρετηρίου B-Tree είναι ταυτόχρονη. Για να επιταχυνθεί η διαδικασία δημιουργίας, μπορούν να χρησιμοποιηθούν αρκετοί παράλληλοι εργάτες. Ωστόσο, βεβαιωθείτε ότι αυτές οι επιλογές διαμόρφωσης έχουν ρυθμιστεί σωστά:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Οι προεπιλεγμένες τιμές είναι πολύ μικρές. Στην ιδανική περίπτωση, αυτοί οι αριθμοί θα πρέπει να αυξάνονται μαζί με τον αριθμό των πυρήνων του επεξεργαστή. Διαβάστε περισσότερα στο .
Δημιουργία ευρετηρίου φόντου
Μπορείτε να δημιουργήσετε ένα ευρετήριο στο παρασκήνιο χρησιμοποιώντας την επιλογή CONCURRENTLY εντολές CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXΑυτή η διαδικασία δημιουργίας ευρετηρίου διαφέρει από τη συνηθισμένη στο ότι δεν απαιτεί κλείδωμα στο τραπέζι και επομένως δεν εμποδίζει τις λειτουργίες εγγραφής. Από την άλλη πλευρά, απαιτεί περισσότερο χρόνο και καταναλώνει περισσότερους πόρους.
Το Postgres παρέχει μεγάλη ευελιξία για τη δημιουργία ευρετηρίων και τρόπων επίλυσης οποιωνδήποτε ειδικών περιπτώσεων, καθώς και τρόπους διαχείρισης της βάσης δεδομένων σε περίπτωση που η αίτησή σας μεγαλώσει εκρηκτικά. Ελπίζουμε ότι αυτές οι συμβουλές θα σας βοηθήσουν να κάνετε τα ερωτήματά σας γρήγορα και τη βάση δεδομένων σας να είναι έτοιμη για κλίμακα.
Πηγή: www.habr.com