Postgres pasaulē indeksi ir ārkārtīgi svarīgi efektīvai navigācijai datu bāzes krātuvē (to sauc par «heap»). Postgres neatbalsta klasterizāciju un MVCC arhitektūra noved pie tā, ka jums uzkrājas daudz vienas un tās pašas rindiņas versiju. Tāpēc ir ļoti svarīgi spēt veidot un uzturēt efektīvus indeksus, lai atbalstītu lietojumprogrammas.
Piedāvāju jūsu uzmanībai vairākus padomus par indeksu optimizāciju un uzlabošanu.
Piezīme: zemāk redzamie vaicājumi darbojas neizmainītā .
Pārklājošo indeksu izmantošana (Covering Indexes)
Apskatīsim vaicājumu, lai iegūtu e-pasta adreses neaktīviem lietotājiem. Tabulā customer ir kolonna active, un vaicājums iznāk 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ājumā tiek izsaukti visi skenēšanas secība tabulā. customerIzveidosim indeksu kolonnai 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) Palīdzēja, nākamā skenēšana pārveidojās par «index scan«. Tas nozīmē, ka Postgres skenēs indeksu «idx_cust1«, un tad turpinās meklēt pa tabulas kaudzi, lai izlasītu citu kolonnu vērtības (šajā gadījumā, kolonnu email), kas nepieciešamas vaicājumam.
PostgreSQL 11 ieviesa pārklājošos indeksus. Tie ļauj iekļaut pašā indeksā vienu vai vairākas papildu kolonnas — to vērtības tiek saglabātas indeksa datu krātuvē.
Ja mēs izmantotu šo iespēju un pievienotu e-pasta vērtību indeksam, tad Postgres’ā vairs nebūtu jāmeklē tabulā heap vērtība email. Paskatīsimies, 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» mums saka, ka vaicājumam tagad ir pietiekams tikai viens indekss, kas palīdz izvairīties no visām diskoperācijām ievadīšanai/izvadīšanai, lai izlasītu tabulas kaudzi.
Šodien seguma indeksi ir pieejami tikai B-kokiem. Tomēr šajā gadījumā apkopšanas izmaksas būs augstākas.
Daļēju indeksu izmantošana
Daļējie indeksi indekse tikai apakškopu tabulas rindām. Tas ļauj ietaupīt indeksu izmērus un ātrāk veikt skenēšanu.
Pieņemam, ka mums nepieciešams saņemt klientu e-pasta adreses no Kalifornijas. 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';
kuram ir pieprasījuma plāns, kas ietver abu savienoto tabulu skenēšanu:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PIEPRASĪJUMA PLĀNS
----------------------------------------------------------------------
Hash Join (izmaksas=15.65..32.22 rindas=9 platums=32)
Hash Cond: (c.address_id = a.address_id)
-> Sec Scan uz customer c (izmaksas=0.00..14.99 rindas=599 platums=34)
-> Hash (izmaksas=15.54..15.54 rindas=9 platums=4)
-> Sec Scan uz address a (izmaksas=0.00..15.54 rindas=9 platums=4)
Filtrs: (district = 'California'::text)
(6 rindas)Ko mums sniegs parastie indeksi:
pagila=# CREATE INDEX idx_address1 ON address(district);
IZVEIDO 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';
PIEPRASĪJUMA PLĀNS
---------------------------------------------------------------------------------------
Hash Join (izmaksas=12.98..29.55 rindas=9 platums=32)
Hash Cond: (c.address_id = a.address_id)
-> Sec Scan uz customer c (izmaksas=0.00..14.99 rindas=599 platums=34)
-> Hash (izmaksas=12.87..12.87 rindas=9 platums=4)
-> Bitmap Heap Scan uz address a (izmaksas=4.34..12.87 rindas=9 platums=4)
Pārbaudes nosacījums: (district = 'California'::text)
-> Bitmap Index Scan uz idx_address1 (izmaksas=0.00..4.34 rindas=9 platums=0)
Indeksa nosacījums: (district = 'California'::text)
(8 rindas) Skenēšana address tika aizstāta ar indeksu skenēšanu idx_address1, pēc tam tika skenēta datu lapa address.
Tā kā šis ir biežs pieprasījums un to nepieciešams optimizēt, mēs varam izmantot daļēju indeksu, kas indeksē tikai tās rindas ar adresēm, kuras attiecās uz rajonu ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
IZVEIDO 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';
PIEPRASĪJUMA PLĀNS
------------------------------------------------------------------------------------------------
Hash Join (izmaksas=12.38..28.96 rindas=9 platums=32)
Hash Cond: (c.address_id = a.address_id)
-> Sec Scan uz customer c (izmaksas=0.00..14.99 rindas=599 platums=34)
-> Hash (izmaksas=12.27..12.27 rindas=9 platums=4)
-> Index Only Scan izmantojot idx_address2 uz address a (izmaksas=0.14..12.27 rindas=9 platums=4)
(5 rindas) Tagad pieprasījums lasa tikai idx_address2 un netraucē tabulu address.
Daudzkārtīgu indeksu izmantošana (Multi-Value Indexes)
Dažas kolonnas, kuras jāindeksē, var nesaturēt skalaru datu tipu. Kolonnām, piemēram, tādām kā jsonb, arrays un tsvector var būt kompozītas vai vairākkārtējas vērtības. Ja jums jāindeksē šādas kolonnas, parasti ir jāmeklē visos atsevišķajos vērtībās šajās kolonnās.
Mēģināsim atrast visu filmu nosaukumus, kuros ir iekļautas neveiksmju dubļu griezumi. Tabulā film ir teksta kolonna, kuru dēvē par special_features. Ja filmai ir šī «īpašā iezīme», tad kolonnā ir elements tekstu masīvā Behind The Scenes. Lai atrastu visas šādas filmas, mēs jāizvēlas visi rindiņas ar «Behind The Scenes» pie jebkādām masīva vērtībām special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Nostiprināšanas operators (containment operator) @> pārbauda, vai labā puse ir kreisās puses apakškopums.
Vaicājuma plāns:
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 pilnu kaudzes skenēšanu ar izmaksām 67.
Paskatīsimies, vai mums palīdzēs parastais B-koka indekss:
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 nenojauš par atsevišķiem elementiem indeksētajās vērtībās.
Mums 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 salīdzināšanu ar indeksētajām kompozīcijas vērtībām, tādējādi samazinot vaicājuma plāna izmaksas vairāk nekā divkārt.
Izvairāmies no indeksu dublēšanās
Indeksi uzkrājas laika gaitā, un dažreiz jauns indekss var saturēt to pašu definīciju kā viens no iepriekšējiem. Lai iegūtu cilvēkiem lasāmus SQL indeksu definīcijas, var izmantot katalogu skatījumu. pg_indexes. Jūs varēsit 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;
Un šeit ir rezultāts, kad tas tiek izpildīts uz krājumu pagila datubāzes:
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 jums uzkrājas daudz indeksu, no kuriem viens indeksē kolonnu superset, kas indeksē citus indeksus. Tas var būt gan vēlams, gan nē — superset var novest pie to indeksu skenēšanas, kas ir labi, taču tas var aizņemt pārāk daudz vietas vai pieprasījums, kura optimizācijai šis superset tika paredzēts, vairs netiek izmantots.
Ja jums nepieciešams automatizēt šādu indeksu noteikšanu, varat sākt ar no tabulas pg_catalog.
Nepieprasītie indeksi
Attīstoties lietojumprogrammām, kas izmanto datubāzes, attīstās arī tajās izmantotie pieprasījumi. Agrāk pievienotie indeksi var vairs netikt izmantoti nevienā pieprasījumā. Katru reizi, kad indekss tiek skenēts, to atzīmē statistikas pārvaldnieks, un sistēmas katalogu skatījumā pg_stat_user_indexes var apskatīt vērtību idx_scan, kas ir uzkrājošs skaitītājs. Šī rādītāja izsekošana noteiktā laika posmā (piemēram, mēnesī) sniegs labu priekšstatu par to, kuri indeksi netiek izmantoti un var tikt dzēsti.
Lūk, pieprasījums, lai iegūtu pašreizējos skaitītājus par visiem indeksi shēmā ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
rezultāts būs šāds:
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 rindiņas)Indeksu pārveidošana ar mazāku bloķēšanas apjomu
Bieži indeksi jāveido no jauna, piemēram, kad tie palielinās apjomā, un pārveidošana var paātrināt skenēšanu. Indeksi var arī tikt bojāti. Indeksa parametru maiņa var arī prasīt tā pārveidošanu.
Ieslēdzam paralēlu indeksu veidošanu
PostgreSQL 11 B-Tree indeksa izveide ir konkurētspējīga. Procesa paātrināšanai var izmantot vairākus paralēli strādājošus darbiniekus. Tomēr pārliecinieties, ka šie konfigurācijas parametri ir iestatīti 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ā šie skaitļi jāpalielina kopā ar procesoru kodolu skaitu. Vairāk lasiet .
Fona indeksa izveide
Jūs varat izveidot indeksu fona režīmā, izmantojot parametru CONCURRENTLY komandas 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 tā neprasa bloķēt tabulu, tādējādi nebloķējot rakstīšanas operācijas. No otras puses, tā aizņem vairāk laika un patērē vairāk resursu.
Postgres piedāvā daudz elastīgu iespēju indeksu izveidošanai un risinājumu jebkuru specifisku gadījumu risināšanai, kā arī sniedz datubāzu pārvaldības metodes jūsu lietojumprogrammas strauja pieauguma gadījumā. Ceram, ka šie padomi palīdzēs jums padarīt pieprasījumus ātrākus un datubāzi gatavu mērogot.
Avots: habr.com
