ProHoster > Blogi > Haldamine > Kasutame PostgreSQL-i indekseerimise kÔiki vÔimalusi

Kasutame PostgreSQL-i indekseerimise kÔiki vÔimalusi

Kasutame PostgreSQL-i indekseerimise kÔiki vÔimalusi
Postgreses on indeksid ÀÀrmiselt olulised andmebaasi salvestamise (tuntud ka kui 'heap') efektiivsel navigeerimisel. Postgres ei toeta sellele klasterdamist ning MVCC arhitektuur toob kaasa selle, et teil koguneb sama tupikute versioone. SeetÔttu on vÀga oluline osata luua ja hallata efektiivseid indexeid rakenduste toetamiseks.

Esitan teile mÔned nÀpunÀited indeksite optimeerimiseks ja tÔhusamaks kasutamiseks.

MÀrkus: allpool toodud pÀringud töötavad muutmata pagila andmebaasi nÀidisel.

Katvuse indeksite kasutamine (Covering Indexes)

Vaadakem pÀringut, et saada kÀtte mitteaktiivsete kasutajate e-posti aadressid. Tabelis customer 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 nÔuab tabeli tÀielikku jÀrjestikust skaneerimist customer. Loome indeksi veeru jaoks 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)

Aitas, edasine skaneerimine muutus «indeksi skaneerimiseks«. See tÀhendab, et Postgres skaneerib indeksi «idx_cust1«, ja seejÀrel jÀtkab tabeli hunnikus otsimist, et lugeda teiste veergude vÀÀrtusi (antud juhul veeru email), mis on pÀringuks vajalik.

PostgreSQL 11-s lisandus katvad indeksid. Need vĂ”imaldavad indeksi enda sisse lisada ĂŒhe vĂ”i mitu tĂ€iendavat veergu — nende vÀÀrtused salvestatakse indeksi andmete hoidlas.

Kui me kasutaksime seda vÔimalust ja lisaksime indeksi sisse e-posti vÀÀrtuse, siis Postgresel ei oleks vaja otsida tabeli hunnikust vÀÀrtust email. Vaatame, kas see töötab:

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Ă€ringu jaoks on nĂŒĂŒd piisav vaid ĂŒks indeks, mis aitab vĂ€ltida kĂ”iki ketta sisend-vĂ€ljund operatsioone tabeli rikka lugemiseks.

TĂ€na on katvad indeksid saadaval ainult B-puude jaoks. Kuid sel juhul on hoolduskohustused suuremad.

Osaliste indeksite kasutamine

Osalised indeksid indekseerivad vaid tabeli rida alamhulga. See aitab indeksite suurust sÀÀsta ja skaneerimist kiiremini teostada.

Oletame, et peame saama nimekirja meie Kaliforniast pÀrit klientide e-posti aadressidest. PÀring oleks jÀrgmine:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
mis sisaldab pĂ€ringukava, mis hĂ”lmab mĂ”lema ĂŒhendatud tabeli skaneerimist:
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 pakuvad meile tavain eksid:

pagila=# LOOMI INDIREKT idx_address1 ON address(district);
LOOMI INDIREKT
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      KÜSIMUSE PLANEERIMINE
---------------------------------------------------------------------------------------
 Hash Joint  (kulus=12.98..29.55 read=9 laiuse=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (kulus=0.00..14.99 read=599 laiuse=34)
   ->  Hash  (kulus=12.87..12.87 read=9 laiuse=4)
         ->  Bitmap Heap Scan on address a  (kulus=4.34..12.87 read=9 laiuse=4)
               Uuesti Kontrolli Tingimus: (district = 'California'::text)
               ->  Bitmap Indeksi Skaneerimine on idx_address1  (kulus=0.00..4.34 read=9 laiuse=0)
                     Indeksi Tingimus: (district = 'California'::text)
(8 read)

Skaneerimine address oli asendatud indeksi skaneerimisega idx_address1, ja seejÀrel skaneeriti hunnik address.

Kuna see on sagedane pĂ€ring ja seda tuleb optimeerida, saame kasutada osalist indeksit, mis indekseerib vaid need read aadresside kohta, kus piirkond ‘California’:

pagila=# LOOME INDEX idx_address2 ON address(address_id) WHERE district='California';
LOOME INDEX
pagila=# SELGITA SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           KÜSIMUSE PLANEERIMINE
------------------------------------------------------------------------------------------------
 Hash Join  (maksumus=12.38..28.96 read=9 laius=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (maksumus=0.00..14.99 read=599 laius=34)
   ->  Hash  (maksumus=12.27..12.27 read=9 laius=4)
         ->  Index Only Scan using idx_address2 on address a  (maksumus=0.14..12.27 read=9 laius=4)
(5 read)

NĂŒĂŒd kĂŒsib pĂ€ring ainult idx_address2 ja ei puutu tabelisse address.

Mitme vÀÀrtuse indeksite kasutamine (Multi-Value Indexes)

MĂ”ned veerud, mida tuleb indekseerida, ei pruugi sisaldada skalaarset andmetĂŒĂŒpi. TĂŒĂŒbid nagu jsonb, massivid ja tsvector vĂ”ivad sisaldada komposiit- vĂ”i mitme vÀÀrtusega. Kui peate selliseid veerge indekseerima, tuleb tavaliselt otsida kĂ”igi individuaalsete vÀÀrtuste kaudu nendes veergudes.

Proovime leida kĂ”igi filmide pealkirju, mis sisaldavad ebaĂ”nnestunud dubleerimisse lĂ”ike. Tabelis film on tekstiline veerg, mida nimetatakse special_features. Kui filmil on see "eriline omadus", siis veerus on elementi teksti massiivi kujul Behind The Scenes. KĂ”ikide nende filmide leidmiseks peame valima kĂ”ik read, kus on «Behind The Scenes» igaĂŒhega massivi vĂ€rdja special_features:

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

SĂŒntaks Operator @> kontrollib, kas parempoolne kĂŒlg on vasakpoolse kĂŒlje alamkogum.

KĂŒsimuste plaan:

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 kutsub tÀielikku partii skaneerimist, mille maksumus on 67.

Vaadake, kas tavapÀrane B-puu indeks aitab meid:

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 ei arvestatud. B-puu indeks ei tea, et indeksit sisaldavad vÀÀrtused sisaldavad loetletud elemente.

Me vajame GIN-indeksi.

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 erivÀÀrtuste vÔrdlemist indekseeritud komposiitvÀÀrtustega, mis vÀhendab pÀringu plaani maksumust rohkem kui poole vÔrra.

Vabaneme indeksite dubleerimisest

Indeksid kogunevad aja jooksul ja mĂ”nikord vĂ”ib uus indeks sisaldada sama mÀÀratlust kui ĂŒks varasematest. Inimesele arusaadavate SQL-i indeksimÀÀratluste saamiseks saab kasutada kataloogivaadet pg_indexes. Samuti leiate kergesti sama mÀÀratluse:

 VALIGE array_agg(indexname) AS indeksid, asenda(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
Ja siin on tulemus, kui kÀitada stock pagila andmebaasis:
pagila=#   VALIGE array_agg(indexname) AS indeksid, asenda(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indeksid                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | LOO INDEX  PUBLIC.payment_p2017_01 KASUTADES btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | LOO INDEX  PUBLIC.payment_p2017_02 KASUTADES btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | LOO INDEX  PUBLIC.payment_p2017_03 KASUTADES btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | LOO INDEX  PUBLIC.payment_p2017_04 KASUTADES btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | LOO INDEX  PUBLIC.payment_p2017_05 KASUTADES btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | LOO INDEX  PUBLIC.payment_p2017_06 KASUTADES btree (customer_id
(6 rida)

Üleminekuindeksid (Superset Indexes)

VĂ”ib juhtuda, et teil on palju indekse, millest ĂŒks indekseerib veergude ĂŒlemkogumi, mis indekseerivad teisi indekseid. See vĂ”ib olla soovitav vĂ”i mitte – ĂŒlemkogum vĂ”ib viia indekseid kasutades skaneerimiseni, mis on hea, kuid samas vĂ”ib see vĂ”tta liiga palju ruumi, vĂ”i pĂ€ringud, mille optimeerimiseks see ĂŒlemkogum mĂ”eldud oli, ei pruugi olla enam kasutusel.

Kui peate automateerima selliste indekste mÀÀramise, siis vÔite alustada pg_index tabelist pg_catalog.

Kasutamata indeksid

Kuna rakendused, mis kasutavad andmebaase, arenevad, arenevad ka nende pĂ€ringud. Varem lisatud indeksid vĂ”ivad enam mitte ĂŒhtegi pĂ€ringut puudutada. Iga indeksi skaneerimise ajal mĂ€rgib statistikahaldaja selle ning sĂŒsteemikatalooge pg_stat_user_indexes vĂ”ib vaadata vÀÀrtust idx_scan, mis on kumulatiivne loendur. Selle vÀÀrtuse jĂ€lgimine mingi ajavahemiku jooksul (ĂŒtleme kuu) annab hea ĂŒlevaate, millised indeksid ei ole kasutuses ja vĂ”ivad eemaldada.

Siin on pÀring, et saada kÔigi skeemi indeksite skaneerimise praeguseid loendureid 'public':

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
vÀljastatud niimoodi:
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 rida)

Indeksite uuesti loomine vÀiksema lukustuste arvuga

Tihti tuleb indekseid uuesti luua, nÀiteks siis, kui need suurenevad ja uuesti loomine vÔib kiirendada skaneerimist. Samuti vÔivad indeksid kahjustuda. Indeksi parameetrite muutmine vÔib samuti nÔuda selle uuesti loomist.

LĂŒlitame sisse indeksite paralleelse loomise

PostgreSQL 11-s saab B-Tree indeksi loomine toimuda konkurentsivÔimeliselt. Protsessi kiirendamiseks vÔib kasutada mitmeid samaaegselt töötavaid töötlusi. Siiski veenduge, et need konfiguratsiooniparametrid on Ôigesti seadistatud:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Vaikimisi vÀÀrtused on liiga madalad. Ideaalis tuleks neid arvu kÀrpimisega suurendada koos protsessorite arvu kasvuga. Lisainfot leiate dokumentatsioonis.

Taustal indekseerimise loomine

Saate luua indeksi taustal, kasutades parameetrit CONCURRENTLY kÀsklused CREATE INDEX:

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

See indeksi loomise protseduur erineb tavalisest, kuna see ei nĂ”ua tabeli lukustamist, mis tĂ€hendab, et see ei blokeeri kirjutamisoperatsioone. Teisest kĂŒljest kestab see kauem ja tarbib rohkem ressursse.

Postgres pakub palju paindlikke vÔimalusi indeksite loomiseks ja konkreetsete juhtumite lahendamiseks ning samuti viise andmebaasi haldamiseks juhul, kui teie rakenduse kasv on jÀrsk. Loodame, et need nÀpunÀited aitavad teil suurendada pÀringute kiirus ja muuta andmebaasi skaleerimise valmidus.

Allikas: habr.com

Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid | ProHoster