ProHoster > Blogi > Haldamine > Kasutades kÔiki PostgreSQL-i indeksite funktsioone

Kasutades kÔiki PostgreSQL-i indeksite funktsioone

Kasutades kÔiki PostgreSQL-i indeksite funktsioone
Postgresi maailmas on indeksid andmebaasi salvestusruumi (mida nimetatakse "hunnikuks") tĂ”husaks navigeerimiseks hĂ€davajalikud. Postgres ei toeta selle jaoks klasterdamist ja MVCC arhitektuur pĂ”hjustab sama korteeĆŸi paljude versioonide loomise. SeetĂ”ttu on vĂ€ga oluline, et oleks vĂ”imalik luua ja sĂ€ilitada rakenduste toetamiseks tĂ”husaid indekseid.

Siin on mÔned nÀpunÀited indeksite kasutamise optimeerimiseks ja tÀiustamiseks.

MÀrkus: allpool nÀidatud pÀringud töötavad muutmata kujul pagila nÀidisandmebaas.

Katteindeksite kasutamine

Vaatame taotlust passiivsete kasutajate meiliaadresside eraldamiseks. Tabel customer seal on veerg active, ja pÀring on lihtne:

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)

PÀring kutsub vÀlja kogu tabeli kontrollimise jada customer. Loome veerule indeksi 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)

See aitas, jÀrgnev skannimine muutus "index scan". See tÀhendab, et Postgres kontrollib indeksit "idx_cust1" ja seejÀrel jÀtkake tabelikuhja otsimist, et lugeda teiste veergude vÀÀrtusi (antud juhul veeru email), mida pÀring vajab.

Katteindekseid tutvustatakse PostgreSQL 11-s. Need vĂ”imaldavad teil indeksisse endasse lisada ĂŒhe vĂ”i mitu tĂ€iendavat veergu - nende vÀÀrtused salvestatakse indeksi andmesalve.

Kui kasutaksime seda funktsiooni Àra ja lisaksime e-posti vÀÀrtuse indeksisse, ei peaks Postgres vÀÀrtust tabelikuhjast otsima. email. Vaatame, kas see toimib:

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' ĂŒtleb meile, et pĂ€ring vajab nĂŒĂŒd ainult indeksit, mis aitab vĂ€ltida kogu ketta sisend-/vĂ€ljundit tabelihunniku lugemiseks.

Katteindeksid on praegu saadaval ainult B-puude jaoks. Kuid sel juhul on hooldustöö suurem.

Osaliste indeksite kasutamine

Osalised indeksid indekseerivad ainult tabeli ridade alamhulka. See sÀÀstab indeksite suurust ja muudab skannimise kiiremaks.

Oletame, et tahame saada Californias asuvate klientide e-posti aadresside loendit. Taotlus oleks jÀrgmine:

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)

Mida tavalised indeksid meile annavad:

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)

Skaneerimine address on asendatud indeksi skannimisega idx_address1ja seejÀrel skaneeris hunnikut address.

Kuna see pĂ€ring on sagedane ja vajab optimeerimist, saame kasutada osalist indeksit, mis indekseerib ainult need read aadressidega, milles ringkond ‘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)

NĂŒĂŒd pĂ€ring ainult loeb idx_address2 ja ei puuduta lauda address.

Mitme vÀÀrtuse indeksite kasutamine

MĂ”ned indekseeritavad veerud ei pruugi sisaldada skalaarset andmetĂŒĂŒpi. VeerutĂŒĂŒbid nagu jsonb, arrays Đž tsvector sisaldavad liit- vĂ”i mitut vÀÀrtust. Kui teil on vaja selliseid veerge indekseerida, peate tavaliselt otsima lĂ€bi kĂ”ik nende veergude ĂŒksikud vÀÀrtused.

Proovime leida kÔigi ebaÔnnestunud vÔtete lÔikeid sisaldavate filmide pealkirjad. Tabel film seal on tekstiveerg nimega special_features. Kui filmil on see "eriomadus", sisaldab veerg elementi tekstimassiivina Behind The Scenes. KÔigi selliste filmide otsimiseks peame valima kÔik read "Behind The Scenes" kui kÔik massiivi vÀÀrtused special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Pesastusoperaator @> kontrollib, kas parem pool on vasaku kĂŒlje alamhulk.

Taotlege plaani:

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)

Mis nÔuab tÀielikku kuhja skannimist maksumusega 67.

Vaatame, kas tavaline B-puu indeks meid aitab:

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)

Indeksit isegi ei arvestatud. B-puu indeks ei ole teadlik ĂŒksikute elementide olemasolust indekseeritud vÀÀrtustes.

Meil on vaja GIN-indeksit.

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-indeks toetab ĂŒksikute vÀÀrtuste vastendamist indekseeritud liitvÀÀrtustega, mille tulemuseks on pĂ€ringuplaani maksumus, mis vĂ€heneb enam kui poole vĂ”rra.

Duplikaatindeksitest vabanemine

Indeksid kogunevad aja jooksul ja mÔnikord vÔib uus indeks sisaldada sama mÀÀratlust, mis eelmistest. Inimesele loetavate SQL-i indeksite definitsioonide hankimiseks saate kasutada kataloogivaadet. pg_indexes. Samuti saate hÔlpsalt leida identseid mÀÀratlusi:

 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 indeksid

VĂ”ib juhtuda, et teil on palju indekseid, millest ĂŒks indekseerib veergude superkomplekti, mis indekseerib teisi indekseid. See vĂ”ib olla soovitav, aga ei pruugi – superkomplekt vĂ”ib anda tulemuseks ainult indeksiga skaneerimise, mis on hea, kuid see vĂ”ib vĂ”tta liiga palju ruumi vĂ”i pĂ€ringut, mida superkomplekt pidi optimeerima, enam ei kasutata.

Kui teil on vaja selliste indeksite mÀÀratlemist automatiseerida, vÔite alustada pg_index laualt pg_catalog.

Kasutamata indeksid

Kuna andmebaase kasutavad rakendused arenevad, arenevad ka nende kasutatavad pĂ€ringud. Varem lisatud indekseid ei saa enam ĂŒheski pĂ€ringus kasutada. Iga kord, kui indeksit skannitakse, mĂ€rgib statistikahaldur selle ja sĂŒsteemikataloogi vaates pg_stat_user_indexes nĂ€ete vÀÀrtust idx_scan, mis on kumulatiivne loendur. Selle vÀÀrtuse jĂ€lgimine teatud aja jooksul (nĂ€iteks kuu jooksul) annab hea ettekujutuse sellest, milliseid indekseid ei kasutata ja milliseid indekseid vĂ”idakse Ă€ra jĂ€tta.

Siin on pĂ€ring kĂ”igi skeemi indeksite praeguste skannimiste arvu saamiseks ‘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)

Indekside taastamine vÀhemate lukkudega

Indeksid tuleb sageli uuesti ĂŒles ehitada, nĂ€iteks kui need paisuvad, ja taastamine vĂ”ib skannimist kiirendada. Samuti vĂ”ivad indeksid rikkuda. Indeksi parameetrite muutmine vĂ”ib samuti nĂ”uda selle ĂŒmberehitamist.

Luba paralleelindeksi loomine

PostgreSQL 11-s on B-puu indeksi loomine samaaegne. Loomise kiirendamiseks saab kasutada mitut paralleelset töötajat. Siiski veenduge, et need konfiguratsioonisuvandid on Ôigesti seadistatud:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

VaikevÀÀrtused on liiga vÀikesed. Ideaalis peaksid need arvud suurenema koos protsessori tuumade arvuga. Loe lÀhemalt sisse dokumentatsioon.

Taustaindeksi loomine

Saate luua taustal indeksi, kasutades valikut CONCURRENTLY meeskonnad CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

See indeksi loomise protseduur erineb tavapĂ€rasest selle poolest, et see ei nĂ”ua tabeli lukustamist ega blokeeri seetĂ”ttu kirjutamistoiminguid. Teisest kĂŒljest vĂ”tab see rohkem aega ja kulutab rohkem ressursse.

Postgres pakub palju paindlikkust indeksite ja erijuhtumite lahendamise viiside loomiseks ning andmebaasi haldamiseks juhuks, kui teie rakendus kasvab plahvatuslikult. Loodame, et need nÀpunÀited aitavad teil oma pÀringud kiiresti saada ja andmebaasi skaleerimiseks valmis saada.

Allikas: www.habr.com

Lisa kommentaar