Postgres pasaulē indeksi ir būtiski, lai nodrošinātu efektīvu navigāciju datu bāzes krātuvē (ko sauc par "kaudzi"). Postgres neatbalsta klasteru veidošanu, un MVCC arhitektūra liek jums nonākt pie daudzām viena un tā paša korektora versijām. Tāpēc ir ļoti svarīgi spēt izveidot un uzturēt efektīvus indeksus lietojumprogrammu atbalstam.
Šeit ir daži padomi, kā optimizēt un uzlabot indeksu izmantošanu.
Piezīme: tālāk redzamie vaicājumi darbojas ar nepārveidotu .
Pārklājuma indeksu izmantošana
Apskatīsim pieprasījumu izvilkt neaktīvo lietotāju e-pasta adreses. Tabula customer ir kolonna active, un vaicājums ir vienkāršs:
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)
Vaicājums izsauc pilnu tabulas skenēšanas secību customer. Izveidosim indeksu kolonnā 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)
Tas palīdzēja, sekojošā skenēšana pārvērtās par "index scan". Tas nozīmē, ka Postgres skenēs indeksu "idx_cust1", un pēc tam turpiniet meklēt tabulas kaudzē, lai nolasītu citu kolonnu (šajā gadījumā kolonnas) vērtības email), kas ir nepieciešams vaicājumam.
Pārklājošie indeksi ir ieviesti programmā PostgreSQL 11. Tie ļauj pašā rādītājā iekļaut vienu vai vairākas papildu kolonnas - to vērtības tiek saglabātas indeksa datu krātuvē.
Ja mēs izmantotu šīs funkcijas priekšrocības un indeksam pievienotu e-pasta vērtību, Postgres vērtība nebūtu jāmeklē tabulas kaudzē. email. Apskatīsim, vai tas darbosies:
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' norāda, ka vaicājumam tagad ir nepieciešams tikai indekss, kas palīdz izvairīties no visa diska I/O, lai lasītu tabulas kaudzes.
Pārklājuma indeksi pašlaik ir pieejami tikai B-kokiem. Tomēr šajā gadījumā uzturēšanas darbs būs lielāks.
Daļējo indeksu izmantošana
Daļējie indeksi indeksē tikai tabulas rindu apakškopu. Tas ietaupa indeksu lielumu un padara skenēšanu ātrāku.
Pieņemsim, ka mēs vēlamies iegūt sarakstu ar mūsu klientu e-pasta adresēm Kalifornijā. Pieprasījums būs šāds:
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)Ko parastie indeksi mums dos:
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)
Skenēt address ir aizstāts ar indeksu skenēšanu idx_address1un pēc tam skenēja kaudzi address.
Tā kā šis ir biežs vaicājums un tas ir jāoptimizē, mēs varam izmantot daļēju indeksu, kas indeksē tikai tās rindas ar adresēm, kurās rajons ‘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)
Tagad vaicājums ir tikai lasāms idx_address2 un nepieskaras galdam address.
Vairāku vērtību indeksu izmantošana
Dažas indeksējamās kolonnas var nesaturēt skalāro datu tipu. Kolonnu veidi, piemēram jsonb, arrays и tsvector satur saliktas vai vairākas vērtības. Ja jums ir jāindeksē šādas kolonnas, parasti šajās kolonnās ir jāmeklē visas atsevišķās vērtības.
Mēģināsim atrast visu to filmu nosaukumus, kurās ir izgriezumi no neveiksmīgiem uzņemšanas gadījumiem. Tabula film ir teksta kolonna ar nosaukumu special_features. Ja filmai ir šis "īpašais īpašums", tad kolonna satur šo elementu kā teksta masīvu Behind The Scenes. Lai meklētu visas šādas filmas, mums ir jāatlasa visas rindas ar "Aiz ainas" kad jebkurš masīva vērtības special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Ligzdošanas operators @> pārbauda, vai labā puse ir kreisās puses apakškopa.
Pieprasīt plānu:
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)Kas pieprasa pilnas kaudzes skenēšanu ar izmaksām 67.
Apskatīsim, vai parastais B-koka indekss mums palīdz:
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)Indekss pat netika ņemts vērā. B-koka indekss nezina par atsevišķu elementu esamību indeksētajās vērtībās.
Mums ir nepieciešams GIN indekss.
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 indekss atbalsta atsevišķu vērtību kartēšanu pret indeksētajām saliktajām vērtībām, kā rezultātā vaicājumu plāna izmaksas ir vairāk nekā uz pusi mazākas.
Atbrīvošanās no indeksu dublikātiem
Indeksi laika gaitā uzkrājas, un dažreiz jauns indekss var saturēt tādu pašu definīciju kā kāds no iepriekšējiem. Varat izmantot kataloga skatu, lai iegūtu cilvēkiem lasāmas indeksu SQL definīcijas. pg_indexes. Varat arī viegli atrast identiskas definīcijas:
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 indeksi
Var gadīties, ka jūs nonākat pie daudziem indeksiem, no kuriem viens indeksē kolonnu superkopu, kas indeksē citus indeksus. Tas var būt vai nav vēlams — superkopa var izraisīt tikai indeksa skenēšanu, kas ir labi, taču tas var aizņemt pārāk daudz vietas, vai arī vaicājums, kuru superkopai bija paredzēts optimizēt, vairs netiek izmantots.
Ja jums ir nepieciešams automatizēt šādu indeksu definīciju, varat sākt ar no galda pg_catalog.
Nelietoti indeksi
Attīstoties lietojumprogrammām, kas izmanto datu bāzes, mainās arī to izmantotie vaicājumi. Iepriekš pievienotos indeksus vairs nevar izmantot nevienam vaicājumam. Katru reizi, kad indekss tiek skenēts, statistikas pārvaldnieks to atzīmē un sistēmas kataloga skatā pg_stat_user_indexes jūs varat redzēt vērtību idx_scan, kas ir kumulatīvs skaitītājs. Šīs vērtības izsekošana noteiktā laika periodā (piemēram, mēnesī) sniegs labu priekšstatu par to, kuri indeksi netiek izmantoti un kurus var atmest.
Šeit ir vaicājums, lai iegūtu visu shēmas indeksu pašreizējo skenēšanas skaitu ‘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)Indeksu atjaunošana ar mazākām slēdzenēm
Indeksi bieži ir jāpārveido, piemēram, kad tie kļūst uzpūsti, un atjaunošana var paātrināt skenēšanu. Arī indeksi var tikt bojāti. Mainot indeksa parametrus, var būt nepieciešama arī tā pārbūve.
Iespējot paralēlo indeksu izveidi
Programmā PostgreSQL 11 B-Tree indeksa izveide notiek vienlaikus. Lai paātrinātu izveides procesu, var izmantot vairākus paralēlus darbiniekus. Tomēr pārliecinieties, vai šīs konfigurācijas opcijas ir iestatītas pareizi:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Noklusējuma vērtības ir pārāk mazas. Ideālā gadījumā šiem skaitļiem vajadzētu palielināties līdz ar procesora kodolu skaitu. Vairāk lasiet sadaļā .
Fona indeksa izveide
Varat izveidot indeksu fonā, izmantojot opciju CONCURRENTLY komandām CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXŠī indeksa izveides procedūra atšķiras no parastās ar to, ka tai nav nepieciešama tabulas bloķēšana, un tāpēc tā nebloķē rakstīšanas darbības. No otras puses, tas aizņem vairāk laika un patērē vairāk resursu.
Postgres nodrošina lielu elastību, veidojot indeksus un veidus, kā atrisināt jebkurus īpašus gadījumus, kā arī veidus, kā pārvaldīt datubāzi, ja jūsu lietojumprogramma strauji pieaug. Mēs ceram, ka šie padomi palīdzēs ātri iegūt jūsu vaicājumus un sagatavot datubāzi mērogošanai.
Avots: www.habr.com