Në botën e Postgres, indeksët janë jashtëzakonisht të rëndësishëm për navigimin efikas në ruajtjen e të dhënave (quhet "heap"). Postgres nuk mbështet klasterizimin për të, dhe arkitektura MVCC bën që të grumbullohen shumë versione të të njëjtit tuple. Prandaj, është shumë e rëndësishme të dihet si të krijoni dhe menaxhoni indekset efikase për të mbështetur aplikacionet.
Para jush, po ofroj disa këshilla për optimizimin dhe përmirësimin e përdorimit të indekseve.
Vërejtje: kërkesat e paraqitura më poshtë funksionojnë në një .
PĂ«rdorimi i indekseve mbuluese (Covering Indexes)
Le të shqyrtojmë një kërkesë për të nxjerrë adresat e emailit për përdoruesit e pasivizuar. Në tabelën customer ka një kolonë active, dhe kërkesa rezulton të jetë e thjeshtë:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN KĂRKESE
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows) Në kërkesë thirret skanimi i plotë i tabelës customer. Le të krijojmë një indeks për kolonën active:
pagila=# KRIJO INDEX idx_cust1 NĂ customer(active);
KRIJO INDEX
pagila=# SHPJEGO ZGJIDH email NGA customer KUJ aktiv=0;
PLANI I KĂRKIMI
-----------------------------------------------------------------------------
Skano Indeksi duke përdorur idx_cust1 në customer (kostot=0.28..12.29 rreshta=15 gjerësi=32)
Kushti i Indeksit: (aktiv = 0)
(2 rreshta) Ndihmoi, skanimi në vazhdim u shndërrua në "skanimi i indeksit". Kjo do të thotë se Postgres do ta skanojë indeksin "idx_cust1" dhe pastaj do të vazhdojë kërkimin në grumbullin e tabelës për të lexuar vlerat e kolonave të tjera (në këtë rast, kolonën email), që shpërndahet me kërkesën.
NĂ« PostgreSQL 11 janĂ« futur indekset mbuluese. Ato lejojnĂ« qĂ« tĂ« pĂ«rfshihen njĂ« ose disa kolona shtesĂ« nĂ« vetĂ« indeksin â vlerat e tyre ruhen nĂ« magazinĂ«n e tĂ« dhĂ«nave tĂ« indeksit.
Nëse do të përdornim këtë mundësi dhe do të shtonim vlerën e emailit brenda indeksit, atëherë Postgres do të mos ketë nevojë të kërkojë në grumbullin e tabelës për vlerën email. Le të shohim nëse do të funksionojë:
pagila=# KRIJO INDEX idx_cust2 NĂ customer(active) PĂRFSHIN (email);
KRIJO INDEX
pagila=# SHPJEGO ZGJIDH email NGA customer KUJ aktiv=0;
PLANI I KĂRKIMI
----------------------------------------------------------------------------------
Skanimi i Indeksit vetëm duke përdorur idx_cust2 në customer (kostot=0.28..12.29 rreshta=15 gjerësi=32)
Kushti i Indeksit: (aktiv = 0)
(2 rreshta) «Skanimi i Indeksit vetëm» na tregon se tani një indeks i vetëm mjafton për kërkesën, duke ndihmuar që të shmangim të gjitha operacionet e hyrjes/daljes për të lexuar një tabelë të madhe.
Sot, indekset mbuluese janë të disponueshme vetëm për pemët B. Megjithatë, në këtë rast, përpjekjet për mirëmbajtjen do të jenë më të larta.
Përdorimi i indikseve përdoruese
Indekset përdoruese indeksojnë vetëm një nënngjyrë të rreshtave të tabelës. Kjo kursen hapësirën e indeksit dhe përshpejton skanimin.
Supozoni se duam të marrim një listë të adresave të postës elektronike të klientëve tanë nga Kaliforni. Kërkesa do të jetë:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
kurse ka një plan kërkese që përfshin skanimet e të dy tabelave që janë të bashkuara:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLANI I KĂRKESĂS
----------------------------------------------------------------------
Hash Join (kostot=15.65..32.22 rreshta=9 gjerësi=32)
Hash Cond: (c.address_id = a.address_id)
-> Sekuenciale Scan mbi klientin c (kostot=0.00..14.99 rreshta=599 gjerësi=34)
-> Hash (kostot=15.54..15.54 rreshta=9 gjerësi=4)
-> Sekuenciale Scan mbi adresën a (kostot=0.00..15.54 rreshta=9 gjerësi=4)
Filtri: (distrikti = 'California'::tekst)
(6 rreshta)ĂfarĂ« do na japin indekset e zakonshme:
pagila=# KRIJO INDIEVE idx_address1 NCĂ address(district);
KRIJO INDIEVE
pagila=# EXPLORO ZGJIDH c.email NGA klient c
pagila-# BASHKO adres a NCĂ c.address_id = a.address_id
pagila-# KU a.district = 'California';
PLAN I KĂRKIMIT
---------------------------------------------------------------------------------------
Bashkim Hash (kostot=12.98..29.55 rreshta=9 gjerësi=32)
Kushti Hash: (c.address_id = a.address_id)
-> Skano Sekuencial në klient c (kostot=0.00..14.99 rreshta=599 gjerësi=34)
-> Hash (kostot=12.87..12.87 rreshta=9 gjerësi=4)
-> Skano Bimbat Heap mbi adresë a (kostot=4.34..12.87 rreshta=9 gjerësi=4)
Rishiko Kushti: (distrikti = 'California'::teksti)
-> Skano Indeksin e Bimbat mbi idx_address1 (kostot=0.00..4.34 rreshta=9 gjerësi=0)
Kushti i Indeksit: (distrikti = 'California'::teksti)
(8 rreshta) Skano adresë u zëvendësua me skanimin e indeksit idx_address1, dhe pastaj u skanuar kupa adresë.
MeqenĂ«se ky Ă«shtĂ« njĂ« kĂ«rkesĂ« e zakonshme dhe duhet optimizuar, mund tĂ« pĂ«rdorim njĂ« indeks tĂ« pjesshĂ«m qĂ« indekson vetĂ«m ato rreshta me adresat ku distriktohet âCaliforniaâ:
pagila=# KRIJO INDĂX idx_address2 NĂ address(address_id) KU DISTRIGUAR='California';
KRIJO INDĂX
pagila=# SHPJEGIMI ZGJIDH c.email NGA blerësi c
pagila-# BASHKOHU address a NĂ c.address_id = a.address_id
pagila-# KU a.district = 'California';
PLANI I PYETJES
------------------------------------------------------------------------------------------------
Bashkimi Hash (kosto=12.38..28.96 rreshta=9 gjerësi=32)
Kushti Hash: (c.address_id = a.address_id)
-> Ska Scan në blerës c (kosto=0.00..14.99 rreshta=599 gjerësi=34)
-> Hash (kosto=12.27..12.27 rreshta=9 gjerësi=4)
-> Skano vetëm me indeks duke përdorur idx_address2 në address a (kosto=0.14..12.27 rreshta=9 gjerësi=4)
(5 rreshta) Tani kërkesa lexon vetëm idx_address2 dhe nuk prekur tabelën adresë.
Përdorimi i indekseve me shumë vlera (Multi-Value Indexes)
Disa kolona që duhet të indeksohen mund të mos përmbajnë një tip të dhënash skalar. Tipet e kolonave si jsonb, arrays dhe tsvector mund të përmbajnë vlera përbërëse ose shumëfish. Nëse duhet të indeksoni këto kolona, zakonisht duhet të kërkoni për të gjitha vlerat e veçanta në këto kolona.
Le të provojmë të gjejmë titujt e të gjitha filmave që përmbajnë prerje nga dubllet e dështuar. Në tabelën film ka një kolonë tekstuale të quajtur special_features. Nëse filmi ka këtë "veçori speciale", atëherë në kolonë përmban një element në formë të një varg tekstual Pas Scene. Për të gjetur të gjitha këto filma, duhet të zgjedhim të gjitha rreshtat me «Prapa skenave» në çdo vlerë të array-t special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operatori i përfshirjes (containment operator) @> kontrollon nëse ana e djathtë është një nëngrup i anës së majtë.
Plani i kërkesës:
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)I cili kërkon një skanim të plotë me kosto 67.
Të shohim nëse na ndihmon një indeks i zakonshëm B-tree:
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 nuk u mor në konsideratë. Indeksi B-tree nuk është i vetëdijshëm për ekzistencën e elementeve të veçanta në vlerat e indeksuara.
Na nevojitet një indeks GIN.
pagila=# KRIJO INDHEKSIN idx_film2 NĂ film PĂRDORIN GIN(special_features);
KRIJO INDHEKS
pagila=# EXPLAIN ZGJIDH TITULLIN NGA film
pagila-# KU EQ SPECIAL_FEATURES @> '{"Pas Skeneve"}';
PLANI I KERKESĂS
---------------------------------------------------------------------------
Skana e Heap-it me Bitmap në film (kosto=8.04..23.58 rreshta=5 gjerësi=15)
Kontrolli i Rihapjes: (special_features @> '{"Pas Skeneve"}'::tekst[])
-> Skana e Indeksit me Bitmap në idx_film2 (kosto=0.00..8.04 rreshta=5 gjerësi=0)
Kushti i Indeksit: (special_features @> '{"Pas Skeneve"}'::tekst[])
(4 rreshta)Indeksi GIN mbështet përshtatjen e vlerave të veçanta me vlerat e përmbledhura të indeksuara, si rezultat, kostoja e planit të kërkesës do të zvogëlohet më shumë se dyfish.
Shkëputemi nga përsëritja e indekseve
Indeksat grumbullohen me kalimin e kohës, dhe ndonjëherë indeksi i ri mund të ketë të njëjtin përkufizim si një nga të kaluarit. Për definicionet SQL të lehtësuara për njeriun, mund të përdorni pamjen katalogjike pg_indexes. Ju gjithashtu do të mund të gjeni lehtësisht definicione të njëjta:
Zgjidhni array_agg(indexname) SI indekset, zëvendësoni(indexdef, indexname, '') SI defn
NGA pg_indexes
GRUPI NGA defn
KRAHASO numri(*) > 1;
Dhe ja rezultati kur ekzekutohet në bazën e të dhënave pagila:
pagila=# Zgjidhni array_agg(indexname) SI indekset, zëvendësoni(indexdef, indexname, '') SI defn
pagila-# NGA pg_indexes
pagila-# GRUPI NGA defn
pagila-# KRAHASO numri(*) > 1;
indekset | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | KRIJO INDĂKSI NĂ public.payment_p2017_01 DHE btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | KRIJO INDĂKSI NĂ public.payment_p2017_02 DHE btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | KRIJO INDĂKSI NĂ public.payment_p2017_03 DHE btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | KRIJO INDĂKSI NĂ public.payment_p2017_04 DHE btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | KRIJO INDĂKSI NĂ public.payment_p2017_05 DHE btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | KRIJO INDĂKSI NĂ public.payment_p2017_06 DHE btree (customer_id
(6 radhë)
Indekset e mbi-grupeve (Superset Indexes)
Mund tĂ« ndodhĂ« qĂ« tĂ« keni shumĂ« indekse, njĂ«ri prej tĂ« cilĂ«ve indekson njĂ« nĂ«nshumĂ« kolonash qĂ« indeksojnĂ« indekse tĂ« tjera. Kjo mund tĂ« jetĂ« e dĂ«shiruashme, por gjithashtu edhe jo â nĂ«nshuma mund tĂ« çojĂ« nĂ« skanimin vetĂ«m pĂ«rmes indekseve, qĂ« Ă«shtĂ« mirĂ«, por nĂ« tĂ« njĂ«jtĂ«n kohĂ« mund tĂ« pĂ«rdorĂ« shumĂ« hapĂ«sirĂ«, ose kĂ«rkesa pĂ«r optimizimin e sĂ« cilĂ«s u krijua kjo nĂ«nshumĂ«, tashmĂ« nuk pĂ«rdoret mĂ«.
Nëse keni nevojë të automatizoni identifikimin e këtyre indekseve, mund të filloni nga nga tabela pg_catalog.
Indekset e papërdorura
Me zhvillimin e aplikacioneve që përdorin bazat e të dhënave, zhvillohen gjithashtu kërkesat që ato përdorin. Indekset e shtuar më parë mund të mos aplikohen më nga asnjë kërkesë. Në çdo skanimin e indeksit, ai shënohet nga menaxheri i statistikave, dhe në pamjen e katalogut sistemor pg_stat_user_indexes mund të shikohet vlera idx_scan, e cila është një numërues akumulues. Ndjekja e kësaj vlerë për një periudhë kohore (të themi, një muaj) do t'ju japë një ide të mirë se cilat indekse nuk përdoren dhe mund të hiqen.
Kjo Ă«shtĂ« njĂ« kĂ«rkesĂ« pĂ«r tĂ« marrĂ« numrat aktualĂ« tĂ« skanimeve tĂ« tĂ« gjitha indekseve nĂ« skemĂ«n âpublicâ:
Zgjidhni relname, indexrelname, idx_scan
Nga pg_catalog.pg_stat_user_indexes
Kujdes schemaname = 'public';
me daljen si kjo:
pagila=# Zgjidhni relname, indexrelname, idx_scan
pagila-# Nga pg_catalog.pg_stat_user_indexes
pagila-# Ku 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 rreshta)Rikrijimi i indekseve me më pak bllokime
Shpesh indekset duhet të rikrijohen, për shembull, kur ato fryhen në përmasa, dhe rikrijimi mund të përshpejtojë skanimin. Gjithashtu indekset mund të dëmtohen. Ndryshimi i parametrave të indeksit gjithashtu mund të kërkojë rikrijimin e tij.
Aktivizojmë krijimin paralel të indekseve
Në PostgreSQL 11, krijimi i indeksit B-Tree është konkurent. Për të shpejtuar procesin e krijimit, mund të përdoren disa punëtorë që punojnë paralelisht. Sigurohuni që këto parametra konfigurimi të jenë caktuar në mënyrë të saktë:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Vlerat e paracaktuar janë shumë të vogla. Në mënyrë ideale, këto numra duhet të rriten me numrin e bërthamave të procesorit. Lexoni më shumë në .
Krijimi i indekseve në sfond
Mund të krijoni një indeks në sfond duke përdorur parametrin CONCURRENTLY komandat CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXKjo procedurë e krijimit të indeksit është e ndryshme nga e zakonshmja, pasi nuk kërkon bllokimin e tabelës dhe, për rrjedhojë, nuk bllokon operacionet e shkruar. Nga ana tjetër, ajo merr më shumë kohë dhe konsumon më shumë burime.
Postgres ofron shumë mundësi fleksibile për krijimin e indekseve dhe rrugëve të zgjidhjes për çdo rast të veçantë, si dhe ofron mënyra për menaxhimin e bazës së të dhënave në rast të rritjes explosive të aplikacionit tuaj. Shpresojmë që këto këshilla do t'ju ndihmojnë të bëni kërkesat të shpejta dhe bazën e të dhënave të gatshme për t'u shkallëzuar.
Burimi: habr.com
