Postgres munduan, indizeak funtsezkoak dira datu-basearen biltegian (heap izenekoa) modu eraginkorrean nabigatzeko. Postgres-ek ez du horretarako clustering onartzen, eta MVCC arkitekturak tupla beraren bertsio askorekin amaitzen zaitu. Horregatik, oso garrantzitsua da aplikazioak onartzen dituzten indize eraginkorrak sortu eta mantentzeko gai izatea.
Indizeen erabilera optimizatzeko eta hobetzeko aholku batzuk ekartzen dizkizuet arreta.
Oharra: behean agertzen diren kontsultek aldatu gabe funtzionatzen dute .
Estaldura-indizeak erabiltzea
Ikus dezagun erabiltzaile inaktiboen helbide elektronikoak berreskuratzeko eskaera. Taulan customer zutabe bat dago active, eta kontsulta erraza da:
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)
Kontsultak taula osoa eskaneatzeko sekuentzia deitzen du customer. Sortu dezagun zutabean aurkibide bat 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)
Lagunduta, ondorengo eskaneamendua " bihurtu daindex scan". Horrek esan nahi du Postgres-ek indizea arakatuko duela "idx_cust1", eta, ondoren, jarraitu taula-pila bilatzen beste zutabe batzuen balioak irakurtzeko (kasu honetan, zutabea email) eskaerak behar duela.
PostgreSQL 11-ek estaldura-indizeak sartu zituen. Indizean bertan zutabe gehigarri bat edo gehiago sartzeko aukera ematen dute - haien balioak indizearen datu biltegian gordetzen dira.
Ezaugarri hau erabili eta indizearen barruan posta elektronikoaren balioa gehituko bagenu, Postgres-ek ez luke balioaren taula-pila batean bilatu beharko email. Ea funtzionatzen duen:
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"-k esaten digu orain kontsultak indizea soilik behar duela, eta horrek taularen pila irakurtzeko diskoko I/O guztiak saihesten laguntzen du.
Gaur egun, estaldura-indizeak B zuhaitzetarako soilik daude eskuragarri. Hala ere, kasu honetan mantentze-esfortzua handiagoa izango da.
Indize Partzialak erabiltzea
Indize partzialek taulako errenkaden azpimultzo bat soilik indexatzen dute. Honi esker, indizearen tamaina gorde dezakezu eta azterketak azkarrago egin ditzakezu.
Demagun Kaliforniako gure bezeroentzako helbide elektronikoen zerrenda bat lortu behar dugula. Eskaera hau izango da:
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)Zein indize arruntek emango diguten:
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)
eskaneatu address indize-eskaneaz ordezkatu da idx_address1, eta gero pila eskaneatu zen address.
Hau maiz kontsulta bat denez eta optimizatu behar denez, indize partziala erabil dezakegu, eremua duten helbideak dituzten errenkadak soilik indexatzen dituena. β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)
Orain eskaera bakarrik irakurtzen da idx_address2 eta ez du mahaia ukitzen address.
Balio anitzeko indizeak erabiltzea
Indexatu behar diren zutabe batzuek baliteke datu-mota eskalarrik ez edukitzea. Zutabe motak bezalakoak jsonb, arrays ΠΈ tsvector esanahi konposatuak edo anitzak dituzte. Zutabe horiek indexatu behar badituzu, normalean zutabe horietan banakako balio guztiak bilatu behar dituzu.
Saia gaitezen arrakastarik gabeko klipak dituzten film guztien izenburuak bilatzen. Taulan film izeneko testu-zutabe bat dago special_features. Film batek "propietate berezi hau" badu, zutabeak testu-matrize formako elementu bat dauka Behind The Scenes. Horrelako film guztiak bilatzeko, "Behind The Scenes" helbidean dauden errenkada guztiak hautatu behar ditugu edozein array balioak special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Euste-operatzailea @> eskuineko aldea ezkerraldeko azpimultzoa den egiaztatzen du.
Kontsulta plana:
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)Horrek 67ko kostua duen heap eskaneatzea eskatzen du.
Ea B-zuhaitz indize arrunt batek lagunduko digun:
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)Indizea ez zen kontuan hartu ere egin. B-zuhaitz indizeak ez du indizeak dituen balioetan elementu indibidualen existentziari buruz.
GIN indizea behar dugu.
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 indizeak balio indibidualak balio konposatuekin mapatzea onartzen du, eta ondorioz, kontsulta-planaren kostuaren erdia baino gehiago sortzen da.
Bikoiztutako indizeak kentzea
Indizeak denboran zehar pilatzen dira, eta batzuetan indize berri batek aurrekoetako baten definizio bera izan dezake. Katalogoaren ikuspegia erabil dezakezu gizakiek irakur daitezkeen SQL indizearen definizioak lortzeko pg_indexes. Definizio berdinak ere erraz aurki ditzakezu:
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)
Supersetaren indizeak
Gerta daiteke indize asko pilatzea, eta horietako batek beste indizeek indexatzen duten zutabeen gain-multzo bat indexatzen du. Hau desiragarria izan daiteke ala ez; baliteke supermultzo batek indizea soilik eskaneatzea eragin dezake, eta hori ona da, baina baliteke leku gehiegi okupatzea, edo supermultzoak optimizatu nahi zuen kontsulta jada ez da erabiltzen.
Horrelako indizeen definizioa automatizatu behar baduzu, hasi zaitezke mahaitik pg_catalog.
Erabili gabeko indizeak
Datu-baseak erabiltzen dituzten aplikazioek eboluzionatzen duten heinean, erabiltzen dituzten kontsultak ere eboluzionatzen dute. Aurretik gehitutako indizeak ezin izango ditu kontsultak erabili. Indize bat eskaneatzen den bakoitzean, estatistiken kudeatzaileak eta sistemaren katalogoaren ikuspegian ohartzen da pg_stat_user_indexes balioa ikus dezakezu idx_scan, kontagailu metatua dena. Balio honen jarraipena denbora-tarte batean (esan hilabete batean) ideia ona emango du zein indize erabiltzen ez diren eta ken daitezkeen.
Hona hemen eskemako indize guztien egungo eskaneatu zenbaketak lortzeko eskaera β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)Blokeo gutxiagorekin indizeak berreraikitzen
Askotan indizeak birsortu behar dira, adibidez puztuta daudenean, eta birsortzeak eskaneatzea bizkortu dezake. Indizeak ere hondatu daitezke. Indize-parametroak aldatzeak berriro sortzea ere eska dezake.
Gaitu indize paraleloak sortzea
PostgreSQL 11n, B-Tree indizea sortzea aldi berean da. Sortze prozesua bizkortzeko, hainbat langile paralelo erabil daitezke. Hala ere, ziurtatu konfigurazio-ezarpen hauek behar bezala ezarrita daudela:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Balio lehenetsiak txikiegiak dira. Egokiena, kopuru hauek prozesadore-nukleoen kopuruarekin batera handitu beharko lirateke. Irakurri gehiago atalean .
Atzeko indizea sortzea
Aurkibidea atzeko planoan sor dezakezu aukera erabiliz CONCURRENTLY komandoak CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXIndizea sortzeko prozedura hau ohikoarekin desberdina da, ez duelako taula blokeatu behar, eta, beraz, ez ditu idazketa eragiketak blokeatzen. Bestalde, denbora gehiago behar da eta baliabide gehiago kontsumitzen ditu.
Postgres-ek aukera malgu asko eskaintzen ditu indizeak eta irtenbideak sortzeko edozein kasu berezirako, eta datu-basea kudeatzeko moduak ere eskaintzen ditu zure aplikazioa izugarri hazten bada. Aholku hauek zure kontsultak azkar eta zure datu-basea eskalatzeko prest lagunduko dizutela espero dugu.
Iturria: www.habr.com