Në botën e Postgres, indekset janë jashtëzakonisht të rëndësishme për navigimin e efektshëm në ruajtjen e bazës së të dhënave (e cila quhet "heap"). Postgres nuk mbështet klasterizimin për të, dhe arkitektura MVCC çon në akumulimin e shumë versioneve të njëjta të të njëjtës regjistër. Prandaj, është shumë e rëndësishme të jeni në gjendje të krijoni dhe mbani indekset efektive për mbështetje të aplikacioneve.
Ju ofroj disa këshilla për optimizimin dhe përmirësimin e përdorimit të indekseve.
Shënim: kërkesat e treguara më poshtë funksionojnë në një .
PĂ«rdorimi i indekseve mbuluese (Covering Indexes)
Le të shqyrtojmë një kërkesë për të nxjerrë adresat e postës elektronike për përdoruesit e pasivizuar. Në tabelën customer ka një kolone aktiv, dhe kërkesa rezulton e thjeshtë:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN KĂRKESASH
-----------------------------------------------------------
Skanim Sequent në customer (kosto=0.00..16.49 radhë=15 gjerësi=32)
Filtri: (active = 0)
(2 radhë) Kërkesa thërret gjithë sekuencën e skanimit të tabelës. customerLe të krijojmë një indeks për kolonën aktiv:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
KRIJO INDĂKST
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN KĂRKESASH
-----------------------------------------------------------------------------
Skanim nga indeksi duke përdorur idx_cust1 mbi customer (kosto=0.28..12.29 radhë=15 gjerësi=32)
Kushti i Indeksit: (active = 0)
(2 radhë) Ndihmoi, skanimi i mëpasshëm u shndërrua në "index scan". Kjo do të thotë se Postgres do të skanojë indeksin "idx_cust1" dhe pastaj do të vazhdojë të kërkojë në heap-in e tabelës për të lexuar vlerat e koloneve të tjera (në këtë rast, kolonën email), që janë të nevojshme për kërkesë.
NĂ« PostgreSQL 11 u prezantuan indekset mbuluese. Ato lejojnĂ« tĂ« pĂ«rfshihen nĂ« indeks njĂ« ose disa kolona shtesĂ« â vlerat e tyre ruhen nĂ« ruajtjen e tĂ« dhĂ«nave tĂ« indeksit.
Nëse do të kishim përdorur këtë mundësi dhe do të kishim shtuar vlerën e postës elektronike brenda indeksit, atëherë Postgres nuk do të kishte nevojë të kërkonte në heap-in e tabelës për vlerën. emailTë shohim nëse kjo do të funksionojë:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
KRIJO INDĂKST
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN KĂRKESASH
----------------------------------------------------------------------------------
Skenimi vetëm nga indeksi duke përdorur idx_cust2 mbi customer (kosto=0.28..12.29 radhë=15 gjerësi=32)
Kushti i Indeksit: (active = 0)
(2 radhë) «Skanimi vetëm nga indeksi" na thotë se kërkesës tani i mjafton vetëm indeksi, duke ndihmuar për të shmangur të gjitha operacionet e hyrjes/daljes për të lexuar heap-in e tabelës.
Sot zbulimi i treguesve janë të disponueshëm vetëm për pemët B. Megjithatë, në këtë rast, përpjekjet për mbështetje do të jenë më të larta.
Përdorimi i treguesve të pjesshëm
Treguesit e pjesshëm indeksojnë vetëm një nëngrup të rreshtave të tabelës. Kjo lejon të kursehet hapësira e treguesve dhe të përshpejtohet skanimi.
Supozoni se kemi nevojë për të marrë një listë të adresave të postës elektronike të klientëve tanë nga Kaliforni. Kërkesa do të ishte si më poshtë:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
çka ka një plan kërkese që përfshin skanimin e të dyja 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 (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)
Filtri: (district = 'California'::text)
(6 rreshta)ĂfarĂ« do tĂ« na japin treguesit e zakonshĂ«m:
pagila=# CREATE INDEX idx_address1 ON address(district);
KRIJO TREGUES
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 (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)
Rikonfirmo Konditën: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Kondita e Treguesit: (district = 'California'::text)
(8 rreshta) Skanimi address u zëvendësua me skanimin e treguesit idx_address1, dhe pastaj u skanua grumbulli address.
Duke qenĂ« se kjo Ă«shtĂ« njĂ« kĂ«rkesĂ« e zakonshme dhe duhet optimizuar, mund tĂ« pĂ«rdorim njĂ« tregues tĂ« pjesshĂ«m, i cili indekson vetĂ«m ato rreshta me adresat nĂ« tĂ« cilat distrikti âCaliforniaâ:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
KRIJO TREGUES
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 (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 duke përdorur idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rreshta) Tani kërkesa lexon vetëm idx_address2 dhe nuk prek tabelën address.
Përdorimi i treguesve me shumë vlera (Multi-Value Indexes)
Disa kolona që duhet të indeksohen mund të mos përmbajnë tipin skalar të të dhënave. Tipet e kolonave si jsonb, arrays dhe tsvector mund të përmbajnë vlera të shumta ose komplekse. Nëse ju nevojitet të indeksoni këto kolona, zakonisht duhet të kërkoni në të gjitha vlerat e veçanta në këto kolona.
Le të provojmë të gjejmë titujt e të gjithë filmeve që përmbajnë skena nga dublat e dështuara. Në tabelën film ka një kolonë tekstuale, e quajtur special_features. Nëse filmi ka këtë "veçori të veçantë", atëherë në kolonë ndodhet një element në formën e një array tekstual Behind The Scenes. Për të gjetur të gjithë këta filma na nevojitet të zgjedhim të gjitha rreshtat që përmbajnë "Behind The Scenes" me çdo vlerë të array-t special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operatori i përmbajtjes (containment operator) @> kontrollon nëse ana e djathtë është 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ë të grumbullit me një kosto prej 67.
Le 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 shqyrtua fare. Indeksi B-tree nuk e kupton ekzistencën e elementeve të veçanta në vlerat e indeksohshme.
Na nevojitet 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 përputhjen e vlerave të veçanta me vlerat komplekse të indeksohshme, duke rezultuar në një ulje të kostos së planit të kërkesës për më shumë se dyfish.
Shkëputemi nga dublikimi i indekseve
Indekset grumbullohen me kalimin e kohës, dhe ndonjëherë indeksi i ri mund të përmbajë të njëjtin përkufizim si një nga ato të mëparshme. Për të marrë përcaktime SQL që janë më të lexueshme për njerëzit, mund të përdorni pamjen kataloguese pg_indexes. Gjithashtu do të jeni në gjendje 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;
Dhe ja rezultati kur ekzekutohet në bazën e të dhënave pagila standard:
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 radhë)
Indekset e Superset
Mund të ndodhë që të keni shumë indekse, një nga të cilat indekson një mbiqendër të kolonave që indeksojnë indekse të tjera. Kjo mund të jetë e dëshirueshme ose jo - mbiqendra mund të çojë në skanimin vetëm përmes indekseve, që është mirë, por mund të zërë shumë hapësirë, ose kërkesa për optimizimin e së cilës ishte parashikuar kjo mbiqendër, tashmë nuk përdoret më.
Nëse ju nevojitet të automatizoni identifikimin e tillë të indekseve, mund të filloni me nga tabela pg_catalog.
Indekset e pa përdorura
Me zhvillimin e aplikacioneve që përdorin baza të dhënash, zhvillohen gjithashtu edhe kërkesat që ato përdorin. Indekset e mëparshme të shtuar mund të mos përdoren më nga asnjë kërkesë. Gjatë çdo skanimi të indeksit, ai shënohet nga menaxheri i statistikave, dhe në pamjen e sistemit pg_stat_user_indexes mund të shihni vlerën idx_scan, e cila është një numërues kumulativ. Ndjekja e këtij vlerësimi për një periudhë kohe (p.sh., një muaj) do të ofrojë një pasqyrë të mirë mbi cilat indekse nuk përdoren dhe mund të fshihen.
KĂ«tu Ă«shtĂ« njĂ« kĂ«rkesĂ« pĂ«r tĂ« marrĂ« numĂ«ruesit aktualĂ« tĂ« skanimit tĂ« tĂ« gjitha indekseve nĂ« skemĂ«n âpublicâ:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
me dalje të tillë:
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)Rikrijimi i indekseve me më pak bllokime
Herë pas here indekset duhet të rikrijohen, për shembull, kur ato zmadhohen në dimensione, dhe rikrijimi mund të përshpejtojë skanimin. Gjithashtu, indekset mund të dëmtohen. Ndryshimi i parametrave të indekseve gjithashtu mund të kërkojë rikrijimin e tyre.
Aktivizimi i krijimit të indekseve paralel
Në PostgreSQL 11, krijimi i indeksi B-Tree është konkurent. Për të përshpejtuar procesin e krijimit, mund të përdoren disa punëtorë që funksionojnë paralel. Megjithatë, sigurohuni që këta parametra konfigurimi janë vendosur saktë:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Vlerat e parazgjedhura janë shumë të vogla. Idealisht, këto numra duhet të rriten së bashku me numrin e nyjeve të procesorit. Më shumë lexoni në .
Krijimi i indekseve në sfond
Mund tĂ« krijoni njĂ« indeks nĂ« sfond duke pĂ«rdorur parametrin CONCURRENTLY komandave KRIJO INDĂKS:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXKjo procedurë e krijimit të indeksit është ndryshe nga e zakonshmja në atë që ajo nuk kërkon bllokimin e tabelës, dhe kështu nuk bllokon operacionet e shkrimit. Nga ana tjetër, ajo merr më shumë kohë dhe konsumon më shumë burime.
Postgres ofron shumë mundësi fleksible për krijimin e indekseve dhe mënyra për të zgjidhur çdo rast të veçantë, si dhe ofron mënyra për të menaxhuar bazën e të dhënave për rastet e rritjes eksplozive të aplikacionit tuaj. Shpresojmë që këto këshilla t'ju ndihmojnë të bëni kërkesat më të shpejta dhe bazën e të dhënave gati për t'u skalitur.
Burimi: habr.com
