„Postgres“ pasaulyje indeksai yra būtini norint efektyviai naršyti duomenų bazės saugykloje (vadinama „krūva“). „Postgres“ nepalaiko jo grupavimo, o dėl MVCC architektūros atsiranda daug tos pačios eilutės versijų. Todėl labai svarbu turėti galimybę kurti ir palaikyti efektyvius indeksus, skirtus programoms palaikyti.
Štai keletas patarimų, kaip optimizuoti ir pagerinti indeksų naudojimą.
Pastaba: toliau pateiktos užklausos veikia nepakeistoje vietoje .
Aprėpties indeksų naudojimas
Pažiūrėkime į užklausą išgauti neaktyvių vartotojų el. pašto adresus. Lentelė customer yra kolona active, o užklausa paprasta:
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)
Užklausa iškviečia visą lentelės nuskaitymo seką customer. Sukurkime stulpelio rodyklę 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)
Tai padėjo, vėlesnis nuskaitymas virto "index scan“. Tai reiškia, kad Postgres nuskaitys indeksąidx_cust1“, tada tęskite paiešką lentelės krūvoje, kad perskaitytumėte kitų stulpelių (šiuo atveju stulpelio) reikšmes email), kurio reikia užklausai.
Apimantys indeksai pateikiami „PostgreSQL 11“. Jie leidžia įtraukti vieną ar daugiau papildomų stulpelių į patį indeksą - jų reikšmės saugomos indekso duomenų saugykloje.
Jei pasinaudotume šia funkcija ir indekse pridėtume el. pašto reikšmę, Postgres nereikėtų ieškoti vertės lentelės krūvoje. email. Pažiūrėkime, ar tai veiks:
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“ nurodo, kad užklausai dabar reikia tik indekso, kuris padeda išvengti visų disko įvesties / išvesties, kad būtų galima nuskaityti lentelės krūvą.
Dengimo indeksai šiuo metu galimi tik B medžiams. Tačiau šiuo atveju priežiūros pastangos bus didesnės.
Dalinių indeksų naudojimas
Daliniai indeksai indeksuoja tik lentelės eilučių poaibį. Tai išsaugo indeksų dydį ir pagreitina nuskaitymą.
Tarkime, kad norime gauti savo klientų el. pašto adresų Kalifornijoje sąrašą. Prašymas bus toks:
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)Ką mums duos įprasti indeksai:
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)
Nuskaityti address buvo pakeistas indekso nuskaitymu idx_address1ir tada nuskenavo krūvą address.
Kadangi tai dažna užklausa ir ją reikia optimizuoti, galime naudoti dalinį indeksą, kuris indeksuoja tik tas eilutes su adresais, kuriuose rajonas ‘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)
Dabar užklausa tik skaitoma idx_address2 ir neliečia stalo address.
Kelių reikšmių indeksų naudojimas
Kai kuriuose indeksuotinuose stulpeliuose gali nebūti skaliarinių duomenų tipo. Stulpelių tipai pvz jsonb, arrays и tsvector turi sudėtines arba kelias vertes. Jei jums reikia indeksuoti tokius stulpelius, paprastai turite ieškoti visų atskirų tų stulpelių verčių.
Pabandykime surasti visų filmų, kuriuose yra nesėkmingų filmų, pavadinimus. Lentelė film yra teksto stulpelis, vadinamas special_features. Jei filmas turi šią "ypatingą savybę", tada stulpelyje yra elementas kaip teksto masyvas Behind The Scenes. Norėdami ieškoti visų tokių filmų, turime pasirinkti visas eilutes su „Užkulisiais“, kada bet koks masyvo reikšmės special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Lizdų operatorius @> patikrina, ar dešinė pusė yra kairiosios pusės poaibis.
Prašyti plano:
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)Kuris reikalauja viso krūvos nuskaitymo, kurio kaina yra 67.
Pažiūrėkime, ar įprastas B medžio indeksas mums padeda:
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)Į indeksą net nebuvo atsižvelgta. B-medžio indeksas nežino apie atskirų elementų egzistavimą indeksuotose reikšmėse.
Mums reikia GIN indekso.
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 indeksas palaiko atskirų verčių susiejimą su indeksuotomis sudėtinėmis vertėmis, todėl užklausos plano kaina yra daugiau nei perpus mažesnė.
Atsikratykite pasikartojančių indeksų
Indeksai kaupiasi laikui bėgant, o kartais naujame indekse gali būti toks pat apibrėžimas kaip ir ankstesniame. Galite naudoti katalogo rodinį, kad gautumėte žmonėms suprantamus indeksų SQL apibrėžimus. pg_indexes. Taip pat galite lengvai rasti vienodus apibrėžimus:
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 indeksai
Gali atsitikti taip, kad gausite daug indeksų, iš kurių vienas indeksuoja stulpelių, indeksuojančių kitus indeksus, superrinkinį. Tai gali būti pageidautina arba ne – superrinkinys gali nuskaityti tik indeksą, o tai yra gerai, tačiau tai gali užimti per daug vietos arba užklausa, kurią turėjo optimizuoti superrinkinys, nebenaudojama.
Jei jums reikia automatizuoti tokių indeksų apibrėžimą, galite pradėti nuo nuo stalo pg_catalog.
Nenaudojami indeksai
Tobulėjant programoms, kurios naudoja duomenų bazes, keičiasi ir jų naudojamos užklausos. Anksčiau pridėti indeksai nebegali būti naudojami jokiai užklausai. Kiekvieną kartą, kai indeksas nuskaitomas, jį pažymi statistikos tvarkyklė ir sistemos katalogo rodinyje pg_stat_user_indexes galite pamatyti vertę idx_scan, kuris yra kaupiamasis skaitiklis. Šios vertės stebėjimas per tam tikrą laikotarpį (tarkime, mėnesį) leis gerai suprasti, kurie indeksai nenaudojami ir gali būti atmesti.
Čia yra užklausa, skirta gauti dabartinį visų schemoje esančių indeksų nuskaitymo skaičių ‘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)Indeksų atkūrimas naudojant mažiau spynų
Indeksus dažnai reikia atkurti, pavyzdžiui, kai jie išpūsti, o atkūrimas gali pagreitinti nuskaitymą. Taip pat indeksai gali būti sugadinti. Keičiant indekso parametrus taip pat gali tekti jį atkurti.
Įgalinti lygiagretų indekso kūrimą
„PostgreSQL 11“ B-Tree indekso kūrimas vyksta vienu metu. Norint paspartinti kūrimo procesą, galima pasitelkti kelis lygiagrečius darbuotojus. Tačiau įsitikinkite, kad šios konfigūracijos parinktys nustatytos teisingai:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Numatytosios reikšmės yra per mažos. Idealiu atveju šie skaičiai turėtų didėti kartu su procesoriaus branduolių skaičiumi. Daugiau skaitykite .
Fono indekso kūrimas
Naudodami parinktį galite sukurti rodyklę fone CONCURRENTLY komandos CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXŠi rodyklės kūrimo procedūra skiriasi nuo įprastos tuo, kad nereikalauja lentelės užrakto, todėl neblokuoja rašymo operacijų. Kita vertus, tai užima daugiau laiko ir sunaudoja daugiau išteklių.
„Postgres“ suteikia daug lankstumo kuriant indeksus ir būdus, kaip išspręsti bet kokius ypatingus atvejus, taip pat būdus, kaip valdyti duomenų bazę, jei jūsų programa sparčiai auga. Tikimės, kad šie patarimai padės greitai pateikti užklausas ir paruošti duomenų bazę, kad ją būtų galima išplėsti.
Šaltinis: www.habr.com