Fid-dinja Postgres, l-indiċi huma essenzjali għal navigazzjoni effiċjenti tal-ħażna tad-database (imsejħa "borġ"). Postgres ma jappoġġjax clustering għaliha, u l-arkitettura MVCC tikkawża li tispiċċa b'ħafna verżjonijiet tal-istess tuple. Għalhekk, huwa importanti ħafna li tkun kapaċi toħloq u żżomm indiċi effiċjenti biex tappoġġja l-applikazzjonijiet.
Hawn huma xi suġġerimenti għall-ottimizzazzjoni u t-titjib tal-użu tal-indiċi.
Nota: il-mistoqsijiet murija hawn taħt jaħdmu fuq mhux modifikat .
L-użu ta' Indiċijiet li jkopru
Ejja nħarsu lejn talba biex jiġu estratti indirizzi tal-email għal utenti inattivi. Tabella customer hemm kolonna active, u l-mistoqsija hija sempliċi:
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)
Il-mistoqsija tinvoka s-sekwenza sħiħa tal-iskannjar tat-tabella customer. Ejja noħolqu indiċi fuq kolonna 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)
Għen, l-iskann sussegwenti nbidel fi "index scan". Dan ifisser li Postgres se jiskennja l-indiċi "idx_cust1", u mbagħad kompli tfittex il-munzell tal-mejda biex taqra l-valuri ta 'kolonni oħra (f'dan il-każ, il-kolonna email) li l-mistoqsija teħtieġ.
L-indiċi li jkopru huma introdotti f'PostgreSQL 11. Huma jippermettulek tinkludi kolonna addizzjonali waħda jew aktar fl-indiċi nnifsu - il-valuri tagħhom huma maħżuna fil-maħżen tad-dejta tal-indiċi.
Jekk ħadu vantaġġ minn din il-karatteristika u żidna l-valur tal-email ġewwa l-indiċi, allura Postgres ma jkunx jeħtieġ li jfittex il-valur fil-munzell tal-mejda. email. Ejja naraw jekk dan hux se jaħdem:
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' tgħidilna li l-mistoqsija issa teħtieġ biss indiċi, li jgħin biex jiġi evitat l-I/O tad-diska kollha biex taqra l-munzell tal-mejda.
L-indiċi li jkopru bħalissa huma disponibbli biss għas-siġar B. Madankollu, f'dan il-każ, l-isforz ta 'manutenzjoni se jkun ogħla.
Użu ta' Indiċijiet Parzjali
Indiċi parzjali indiċi biss subsett tar-ringieli f'tabella. Dan jiffranka d-daqs tal-indiċi u jagħmel l-iskans aktar mgħaġġel.
Ejja ngħidu li rridu nġibu lista tal-indirizzi tal-email tal-klijenti tagħna fil-Kalifornja. It-talba se tkun hekk:
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)Liema indiċi ordinarji se jagħtuna:
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)
Skennja address ġie sostitwit minn skan tal-indiċi idx_address1u mbagħad skennjat il-borġ address.
Peress li din hija mistoqsija frekwenti u teħtieġ li tiġi ottimizzata, nistgħu nużaw indiċi parzjali, li jindika biss dawk ir-ringieli b'indirizzi li fihom id-distrett ‘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)
Issa l-mistoqsija taqra biss idx_address2 u ma tmissx il-mejda address.
Bl-użu ta' Indiċijiet ta' Multi-Valur
Xi kolonni li għandhom jiġu indiċjati jista' ma jkunx fihom tip ta' data skalari. Tipi ta 'kolonna simili jsonb, arrays и tsvector fihom valuri komposti jew multipli. Jekk għandek bżonn tindika kolonni bħal dawn, ġeneralment ikollok tfittex il-valuri individwali kollha f'dawk il-kolonni.
Ejja nippruvaw insibu t-titli tal-films kollha li fihom qatgħat minn teħid mingħajr suċċess. Tabella film hemm kolonna tat-test imsejħa special_features. Jekk il-film għandu din il-"proprjetà speċjali", allura l-kolonna fiha l-element bħala firxa ta 'test Behind The Scenes. Biex tfittex films bħal dawn kollha, irridu nagħżlu r-ringieli kollha b'"Behind The Scenes" meta kwalunkwe valuri array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operatur tat-tbejjit @> jiċċekkja jekk in-naħa tal-lemin hijiex subsett tan-naħa tax-xellug.
Talba pjan:
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)Li titlob heap scan sħiħ bi spiża ta’ 67.
Ejja naraw jekk indiċi B-siġra regolari jgħinna:
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)L-indiċi lanqas ma ġie kkunsidrat. L-indiċi tas-siġra B mhuwiex konxju tal-eżistenza ta 'elementi individwali fil-valuri indiċjati.
Għandna bżonn indiċi 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)L-indiċi GIN jappoġġa l-immappjar ta 'valuri uniċi kontra valuri komposti indiċjati, li jirriżulta fi spiża tal-pjan ta' mistoqsija li titnaqqas aktar min-nofs.
Jwarrbu l-indiċi duplikati
L-indiċijiet jakkumulaw maż-żmien, u xi drabi indiċi ġdid jista’ jkun fih l-istess definizzjoni bħal waħda minn dawk preċedenti. Tista 'tuża l-veduta tal-katalgu biex tikseb definizzjonijiet SQL ta' indiċi li jinqraw mill-bniedem. pg_indexes. Tista’ ssib ukoll definizzjonijiet identiċi faċilment:
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)
Indiċijiet Superset
Jista 'jiġri li tispiċċa b'ħafna indiċi, li wieħed minnhom indiċi superset ta' kolonni li indiċi indiċi oħra. Dan jista' jkun jew ma jkunx mixtieq—is-superset jista' jirriżulta fi skans bl-indiċi biss, li huwa tajjeb, iżda jista' jieħu wisq spazju, jew il-mistoqsija li s-superset kien maħsub biex jottimizza ma tibqax tintuża.
Jekk għandek bżonn awtomat id-definizzjoni ta 'indiċi bħal dawn, tista' tibda bihom mit-tabella pg_catalog.
Indiċi mhux użati
Hekk kif l-applikazzjonijiet li jużaw databases jevolvu, hekk ukoll il-mistoqsijiet li jużaw. L-indiċi miżjuda qabel ma jistgħux jintużaw aktar minn kwalunkwe mistoqsija. Kull darba li indiċi jiġi skennjat, huwa mmarkat mill-maniġer tal-istatistika, u fil-veduta tal-katalgu tas-sistema pg_stat_user_indexes tista 'tara l-valur idx_scan, li huwa counter kumulattiv. It-traċċar ta’ dan il-valur fuq perjodu ta’ żmien (jiġifieri xahar) jagħti idea tajba ta’ liema indiċijiet mhumiex qed jintużaw u jistgħu jitneħħew.
Hawnhekk hawn mistoqsija biex tikseb l-għadd kurrenti tal-iskanjar tal-indiċi kollha fl-iskema ‘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)Bini mill-ġdid ta' indiċi b'inqas serraturi
L-indiċi ħafna drabi jeħtieġ li jerġgħu jinbnew, pereżempju meta jsiru minfuħin, u l-bini mill-ġdid jista 'jħaffef l-iskannjar. Ukoll l-indiċi jistgħu jiġu korrotta. It-tibdil tal-parametri tal-indiċi jista' wkoll jeħtieġ li tinbena mill-ġdid.
Ippermetti l-ħolqien ta' indiċi parallel
F'PostgreSQL 11, il-ħolqien ta' indiċi B-Tree huwa konkorrenti. Biex jitħaffef il-proċess tal-ħolqien, jistgħu jintużaw diversi ħaddiema paralleli. Madankollu, kun żgur li dawn l-għażliet ta 'konfigurazzjoni huma ssettjati b'mod korrett:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Il-valuri awtomatiċi huma żgħar wisq. Idealment, dawn in-numri għandhom jiżdiedu flimkien man-numru ta 'qlub tal-proċessuri. Aqra aktar fi .
Ħolqien ta' indiċi ta' sfond
Tista 'toħloq indiċi fl-isfond billi tuża l-għażla CONCURRENTLY timijiet CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDin il-proċedura ta 'ħolqien ta' indiċi hija differenti minn dik tas-soltu peress li ma teħtieġx lock fuq il-mejda, u għalhekk ma timblokkax operazzjonijiet ta 'kitba. Min-naħa l-oħra, tieħu aktar ħin u tikkonsma aktar riżorsi.
Postgres jipprovdi ħafna flessibbiltà għall-ħolqien ta' indiċi u modi biex issolvi kwalunkwe każ speċjali, kif ukoll modi biex timmaniġġja d-database f'każ li l-applikazzjoni tiegħek tikber b'mod esplodenti. Nittamaw li dawn il-pariri jgħinuk tikseb il-mistoqsijiet tiegħek malajr u d-database tiegħek lesta biex tiskala.
Sors: www.habr.com