ProHoster > Blogi > Haldamine > Kasutame tÀielikult Postgres'i indeksite vÔimalusi

Kasutame tÀielikult Postgres'i indeksite vÔimalusi

Kasutame tÀielikult Postgres'i indeksite vÔimalusi
PostgreSQLis on oluline indekseed, et tagada tĂ”hus andmebaasi salvestusruumi navigeerimine (mida nimetatakse «kupaks», heap). PostgreSQL ei toeta selle jaoks klasterdamist ning MVCC arhitektuur toob kaasa selle, et ĂŒhe ja sama tupakuse kohta koguneb palju versioone. SeetĂ”ttu on ÀÀrmiselt oluline osata luua ja hallata tĂ”husaid indekse rakenduste toetamiseks.

Pakun teile mÔned nÔuanded indeksite optimeerimiseks ja tÔhusamaks kasutamiseks.

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

Katab indekseid (Covering Indexes)

Vaadakem pÀringut, et vÀlja tÔmmata e-kirjad mitteaktiivsetelt kasutajatelt. Tabelis customer on veerg active, ja pÀring osutub lihtsaks:

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Àringul toimub tabeli tÀielik jÀrjestikune skannimine. customerLoome 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)

See aitas, tulevane skannimine muutus «indeksiskannimiseks«. See tÀhendab, et PostgreSQL skannib indeksi «idx_cust1«, ja seejÀrel jÀtkab see andmete lugemist tabeli kupast, et lugeda teisi veerge (antud juhul veerg email), mis on pÀringus vajalik.

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

Kui me kasutaksime seda vÔimalust ja lisaksime indeksi piiresse e-kirja vÀÀrtuse, siis ei peaks PostgreSQL otsima vÀÀrtust tabeli kupast. emailVaadakem, 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)

«Ainult indeksi skannimine» ĂŒtleb meile, et pĂ€ringule piisab nĂŒĂŒd ainult indeksist, mis aitab vĂ€ltida kĂ”iki kettaselise sisendi/vĂ€ljundi operatsioone tabeli kupast lugemisel.

TĂ€na on katteindeksid saadaval ainult B-puude jaoks. Kuid sel juhul on toetustegevuse vajadused suuremad.

Osaliste indeksite kasutamine

Osalised indeksid indekseerivad ainult tabeli ridade alamkogumi. See vÔimaldab indeksite mahtu vÀhendada ja skaneerimist kiirendada.

Oletame, et peame saama meie Kalifornia klientide e-posti aadresside loendi. KĂŒsitlus oleks jĂ€rgmine:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
kuna sellel on pĂ€ringuplaan, mis hĂ”lmab ĂŒhendatud tabelite 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';
                              PÄRINGUPLAAN
----------------------------------------------------------------------
 Hash Join  (kulu=15.65..32.22 read=9 laius=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (kulu=0.00..14.99 read=599 laius=34)
   ->  Hash  (kulu=15.54..15.54 read=9 laius=4)
         ->  Seq Scan on address a  (kulu=0.00..15.54 read=9 laius=4)
               Filter: (district = 'California'::text)
(6 rida)

Mida me saame tavalisest indeksist:

pagila=# CREATE INDEX idx_address1 ON address(district);
Loo indeks
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      PÄRINGUPLAAN
---------------------------------------------------------------------------------------
 Hash Join  (kulu=12.98..29.55 read=9 laius=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (kulu=0.00..14.99 read=599 laius=34)
   ->  Hash  (kulu=12.87..12.87 read=9 laius=4)
         ->  Bitmap Heap Scan on address a  (kulu=4.34..12.87 read=9 laius=4)
               Uuesti kontrollitud tingimus: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (kulu=0.00..4.34 read=9 laius=0)
                     Indeksi tingimus: (district = 'California'::text)
(8 rida)

Skaneerimine aadress on asendatud indeksi skaneerimisega idx_address1, ja seejÀrel skannitakse kuhi aadress.

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

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
Loo indeks
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           PÄRINGUPLAAN
------------------------------------------------------------------------------------------------
 Hash Join  (kulu=12.38..28.96 read=9 laius=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (kulu=0.00..14.99 read=599 laius=34)
   ->  Hash  (kulu=12.27..12.27 read=9 laius=4)
         ->  Index Only Scan using idx_address2 on address a  (kulu=0.14..12.27 read=9 laius=4)
(5 rida)

NĂŒĂŒd loeb pĂ€ring ainult idx_address2 ja ei puuduta tabelit aadress.

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

MĂ”ned veerud, mida tuleb indekseerida, ei pruugi sisaldada skalaartĂŒĂŒpide andmeid. Veeru tĂŒĂŒbid nagu jsonb, massive ja tsvector vĂ”ivad sisaldada komposiit- vĂ”i mitme vÀÀrtuse kogumeid. Kui soovite indekseerida selliseid veerge, peate tavaliselt otsima kĂ”igi nende veergude eraldi vÀÀrtuste jĂ€rgi.

Proovime leida kĂ”ik filmide nimed, mis sisaldavad kaadreid ebaĂ”nnestunud dublaaĆŸidest. Tabelis film on tekstiveerg nimega special_features. Kui filmil on see «eriline omadus», siis veerus on tekstimassiiv Behind The Scenes. KĂ”igi selliste filmide leidmiseks peame valima kĂ”ik read, kus on «Behind The Scenes» kĂ”ikide massiivi vÀÀrtuste jaoks special_features:

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

Sisuoperaator (containment operator) @> kontrollib, kas parem osa on vasaku osa alamkogum.

PĂ€ringu 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 nĂ”uab tĂ€ielikku kĂŒhveldamist maksaga 67.

Vaadakem, kas tavaline B-puu indeks 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 ei arvestatud isegi. B-puu indeks ei tea eraldi elementide olemasolust indeksitavate vÀÀrtuste seas.

Me vajame GIN-indeksite.

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 sidumist indekseeritud komposiitvÀÀrtustega, mille tulemusena vĂ€heneb pĂ€ringu plaani hind rohkem kui poole vĂ”rra.

KĂ€ivitage duplikaatindeksid

Aja indeksid kogunevad ajas ja mĂ”nikord vĂ”ib uus indeks sisaldada sama mÀÀratlust, mis ĂŒks varasematest. Inimesele arusaadavate SQL-indeksite mÀÀratlemiseks saab kasutada kataloogivaadet pg_indexes. Samuti leiate kergesti sama mÀÀratlust:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
Ja siin on tulemus, kui see kÀivitatakse aktsia pagila andmebaasis:
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)

Ülemine indeks (Superset Indexes)

VĂ”ib juhtuda, et teil on palju indekseid, millest ĂŒks indekseerib veergude ĂŒlekomplekti, mille indeksid indekseerivad teised indeksid. See vĂ”ib olla soovitav vĂ”i mitte - ĂŒlekomplekt vĂ”ib viia indekseid kasutades skaneerimiseni, mis on hea, aga samas vĂ”ib see vĂ”tta liiga palju ruumi vĂ”i pĂ€ring, mille optimeerimiseks see ĂŒlekomplekt oli mĂ”eldud, enam ei kasutata.

Kui soovite selliste indeksite mÀÀratlemist automaatiseerida, vÔite alustada pg_index tabelist pg_catalog.

Kasutamata indeksid

Kuna rakendused, mis kasutavad andmebaase, arenevad, arenevad ka nende kasutatavad pĂ€ringud. Varasemate indeksite lisamine ei pruugi enam olla ĂŒhegi pĂ€ringu jaoks rakendatav. Iga indeksi skaneerimisel mĂ€rgib selle statistika haldur ja sĂŒsteemi katalooge esitavas vaates pg_stat_user_indexes saate vaadata vÀÀrtust idx_scan, mis on akumuleeriv loendur. Selle vÀÀrtuse jĂ€lgimine teatud ajavahemiku jooksul (ĂŒtleme, kuu) annab hea ĂŒlevaate sellest, milliseid indekse ei kasutata ja mis vĂ”ivad olla eemaldatud.

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

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
vÔimaliku vÀljundiga:
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 lukustamisega.

Sageli tuleb indekse uuesti luua, nÀiteks kui need suurenevad, ning 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 on B-Tree indeksi loomine konkurentsivÔimeline. Loomise protsessi kiirendamiseks vÔib kasutada mitmeid paralleelselt töötavaid töötajaid. Siiski veenduge, et need konfiguratsiooniparametrid on Ôigesti seadistatud:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Vaikimisi vÀÀrtused on liiga vĂ€ikesed. Ideaalis tuleks neid numbreid suurendada koos protsessori sĂŒdamike arvuga. Lisainfot leiate dokumentatsioon.

Indeksite taustal loomine.

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

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

See indeksi loomise protseduur erineb tavalisest selles, et see ei nĂ”ua tabeli lukustamist, mis tĂ€hendab, et see ei lukusta kirjutamistoiminguid. Teisest kĂŒljest vĂ”tab see rohkem aega ja tarbib rohkem ressursse.

Postgres pakub palju paindlikke vÔimalusi indeksite loomiseks ja erinevate erijuhtumite lahendamiseks, samuti lahendusi andmebaasi haldamiseks, kui teie rakendus kasvab plahvatuslikult. Loodame, et need nÀpunÀited aitavad teil pÀringud kiireks muuta ja andmebaasi skaleerimist ette valmistada.

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster