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 .
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 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 .
Taustaindeksi loomine
Saate luua taustal indeksi, kasutades valikut CONCURRENTLY meeskonnad CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXSee 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