Në botën e Postgres, indekset janë kritike për lundrimin me efikasitet të ruajtjes së bazës së të dhënave (i quajtur grumbull). Postgres nuk e mbështet grupimin për të dhe arkitektura MVCC bën që të përfundoni me shumë versione të të njëjtit tuple. Prandaj, është shumë e rëndësishme të jeni në gjendje të krijoni dhe mbani indekse efikase për të mbështetur aplikacionet.
Unë sjell në vëmendjen tuaj disa këshilla për optimizimin dhe përmirësimin e përdorimit të indekseve.
Shënim: Pyetjet e paraqitura më poshtë funksionojnë të pandryshuara .
Përdorimi i Indekseve Mbuluese
Le të shohim një kërkesë për të tërhequr adresat e emailit për përdoruesit joaktivë. Ne tavoline customer ka një kolonë active, dhe pyetja është e thjeshtë:
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)
Pyetja thërret sekuencën e plotë të skanimit të tabelës customer. Le të krijojmë një indeks në kolonë 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)
Ndihmoi, skanimi i mëvonshëm u shndërrua në "index scan". Kjo do të thotë që Postgres do të zvarritet indeksin "idx_cust1", dhe më pas vazhdoni kërkimin e grumbullit të tabelës për të lexuar vlerat e kolonave të tjera (në këtë rast, kolona email) që kërkon kërkesa.
PostgreSQL 11 prezantoi indekset mbuluese. Ato ju lejojnë të përfshini një ose më shumë kolona shtesë në vetë indeksin - vlerat e tyre ruhen në dyqanin e të dhënave të indeksit.
Nëse e përdornim këtë veçori dhe shtonim vlerën e emailit brenda indeksit, atëherë Postgres nuk do të kishte nevojë të kërkonte në grumbullin e tabelës për vlerën email. Le të shohim nëse kjo funksionon:
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" na tregon se pyetja tani ka nevojë vetëm për indeksin, i cili ndihmon në shmangien e të gjithë hyrjeve/daljeve të diskut për të lexuar grumbullin e tabelës.
Sot, indekset mbuluese janë të disponueshme vetëm për pemët B. Megjithatë, në këtë rast përpjekja për mirëmbajtje do të jetë më e lartë.
Përdorimi i indekseve të pjesshme
Indekset e pjesshme indeksojnë vetëm një nëngrup të rreshtave të tabelës. Kjo ju lejon të kurseni madhësinë e indeksit dhe të kryeni skanime më shpejt.
Le të themi se duhet të marrim një listë të adresave të emailit për klientët tanë në Kaliforni. Kërkesa do të jetë si kjo:
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)Çfarë do të na japin indekset e rregullta:
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)
hetim address është zëvendësuar nga skanimi i indeksit idx_address1, dhe më pas grumbulli u skanua address.
Meqenëse kjo është një pyetje e shpeshtë dhe duhet të optimizohet, ne mund të përdorim një indeks të pjesshëm, i cili indekson vetëm ato rreshta me adresa në të cilat zona ‘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)
Tani kërkesa lexohet vetëm idx_address2 dhe nuk e prek tavolinën address.
Përdorimi i indekseve me shumë vlera
Disa kolona që duhet të indeksohen mund të mos përmbajnë një lloj të dhënash skalar. Llojet e kolonave si jsonb, arrays и tsvector përmbajnë kuptime të përbëra ose të shumëfishta. Nëse keni nevojë të indeksoni kolona të tilla, zakonisht duhet të kërkoni për të gjitha vlerat individuale në ato kolona.
Le të përpiqemi të gjejmë titujt e të gjithë filmave që përmbajnë klipe nga foto të pasuksesshme. Ne tavoline film ekziston një kolonë teksti e quajtur special_features. Nëse një film ka këtë "veti të veçantë", atëherë kolona përmban një element në formën e një grupi teksti Behind The Scenes. Për të kërkuar të gjithë filmat e tillë, duhet të zgjedhim të gjitha rreshtat me "Behind The Scenes" në ndonjë vlerat e vargjeve special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operatori i frenimit @> kontrollon nëse ana e djathtë është një nëngrup i anës së majtë.
Plani i pyetjeve:
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)E cila kërkon një skanim të plotë të grumbullit me një kosto prej 67.
Le të shohim nëse një indeks i rregullt i pemës B do të na ndihmojë:
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)Indeksi as që u mor në konsideratë. Indeksi i pemës B nuk ka asnjë ide për ekzistencën e elementeve individuale në vlerat që indekson.
Ne kemi nevojë për një indeks GIN.
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)Indeksi GIN mbështet hartëzimin e vlerave individuale me vlerat e përbëra të indeksuara, duke rezultuar në më shumë se gjysmën e kostos së planit të pyetjes.
Heqja e indekseve të kopjuara
Indekset grumbullohen me kalimin e kohës dhe ndonjëherë një indeks i ri mund të përmbajë të njëjtin përkufizim si një nga ato të mëparshmet. Ju mund të përdorni pamjen e katalogut për të marrë përkufizime të indeksit SQL të lexueshme nga njeriu pg_indexes. Ju gjithashtu mund të gjeni lehtësisht të njëjtat përkufizime:
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)
Indekset Superset
Mund të ndodhë që të grumbulloni shumë indekse, njëri prej të cilëve indekson një superbashkësi kolonash që indeksojnë indekset e tjera. Kjo mund të jetë ose jo e dëshirueshme - një superbashkësi mund të rezultojë në një skanim vetëm me indeks, gjë që është e mirë, por mund të zërë shumë hapësirë, ose pyetja që superseti kishte për qëllim të optimizonte nuk është më në përdorim.
Nëse keni nevojë të automatizoni përkufizimin e indekseve të tilla, mund të filloni me nga tavolina pg_catalog.
Indekset e papërdorura
Ndërsa evoluojnë aplikacionet që përdorin bazat e të dhënave, po ashtu zhvillohen edhe pyetjet që përdorin. Indekset e shtuara më parë nuk mund të përdoren më nga asnjë pyetje. Sa herë që një indeks skanohet, ai shënohet nga menaxheri i statistikave dhe në pamjen e katalogut të sistemit pg_stat_user_indexes ju mund të shihni vlerën idx_scan, i cili është një numërues kumulativ. Ndjekja e kësaj vlere për një periudhë kohore (të themi një muaj) do të japë një ide të mirë se cilët indekse nuk përdoren dhe mund të hiqen.
Këtu është një kërkesë për të marrë numërimin aktual të skanimit të të gjithë indekseve në skemë ‘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)Rindërtimi i indekseve me më pak bravë
Indekset shpesh duhet të rikrijohen, për shembull kur fryhen, dhe rikrijimi mund të përshpejtojë skanimin. Indekset gjithashtu mund të korruptohen. Ndryshimi i parametrave të indeksit mund të kërkojë gjithashtu rikrijimin e tij.
Aktivizo krijimin e indeksit paralel
Në PostgreSQL 11, krijimi i indeksit B-Tree është i njëkohshëm. Për të përshpejtuar procesin e krijimit, mund të përdoren disa punëtorë paralelë. Sidoqoftë, sigurohuni që këto cilësime të konfigurimit të jenë vendosur saktë:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Vlerat e paracaktuara janë shumë të vogla. Në mënyrë ideale, këto numra duhet të rriten së bashku me numrin e bërthamave të procesorit. Lexo më shumë në .
Krijimi i indeksit të sfondit
Mund të krijoni indeksin në sfond duke përdorur opsionin CONCURRENTLY komandat CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXKjo procedurë e krijimit të indeksit ndryshon nga ajo e zakonshme në atë që nuk kërkon mbylljen e tabelës, dhe për këtë arsye nuk bllokon operacionet e shkrimit. Nga ana tjetër, kërkon më shumë kohë dhe konsumon më shumë burime.
Postgres ofron shumë opsione fleksibël për krijimin e indekseve dhe zgjidhjeve për çdo rast të veçantë, dhe gjithashtu ofron mënyra për të menaxhuar bazën e të dhënave nëse aplikacioni juaj rritet në mënyrë shpërthyese. Shpresojmë që këto këshilla do t'ju ndihmojnë t'i bëni pyetjet tuaja të shpejta dhe bazën e të dhënave tuaja të gatshme për t'u shkallëzuar.
Burimi: www.habr.com