Í Postgres heiminum eru vísitölur nauðsynlegar fyrir skilvirka leiðsögn um gagnagrunnsgeymsluna (kallað „hrúga“). Postgres styður ekki þyrping fyrir það og MVCC arkitektúrinn veldur því að þú endar með margar útgáfur af sama tuple. Þess vegna er mjög mikilvægt að geta búið til og viðhaldið skilvirkum vísitölum til að styðja við forrit.
Hér eru nokkur ráð til að hagræða og bæta notkun á vísitölum.
Athugið: fyrirspurnirnar sem sýndar eru hér að neðan virka á óbreyttu .
Að nota yfirlitsvísitölur
Við skulum skoða beiðni um að draga út netföng fyrir óvirka notendur. Tafla customer það er dálkur active, og fyrirspurnin er einföld:
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)
Fyrirspurnin kallar fram alla töfluskannaröðina customer. Búum til vísitölu á dálki 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)
Það hjálpaði, skönnunin í kjölfarið breyttist í "index scan". Þetta þýðir að Postgres mun skanna vísitöluna "idx_cust1", og haltu síðan áfram að leita í töfluhrúgunni til að lesa gildi annarra dálka (í þessu tilviki, dálkinn email) sem fyrirspurnin þarfnast.
Þekkja vísitölur eru kynntar í PostgreSQL 11. Þeir leyfa þér að hafa einn eða fleiri dálka til viðbótar í vísitölunni sjálfri - gildi þeirra eru geymd í vísitölugagnageymslunni.
Ef við myndum nota þennan eiginleika og bæta við tölvupóstsgildinu inni í vísitölunni, þá þyrfti Postgres ekki að leita í hrúgu töflunnar að gildinu. email. Við skulum sjá hvort þetta virkar:
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' segir okkur að fyrirspurnin þurfi nú aðeins vísitöluna, sem hjálpar til við að forðast allt disk I/O til að lesa töfluhrúguna.
Þekjandi vísitölur eru sem stendur aðeins fáanlegar fyrir B-tré. Hins vegar, í þessu tilviki, verður viðhaldsátakið meira.
Að nota hlutavísitölur
Hlutavísitölur skráir aðeins undirmengi raða í töflu. Þetta sparar stærð vísitölu og gerir skannanir hraðari.
Segjum að við viljum fá lista yfir netföng viðskiptavina okkar í Kaliforníu. Beiðnin verður svona:
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)Það sem venjulegar vísitölur munu gefa okkur:
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)
Skanna address hefur verið skipt út fyrir vísitöluskönnun idx_address1og skannaði svo hrúguna address.
Þar sem þetta er tíð fyrirspurn og þarf að fínstilla, getum við notað hlutavísitölu, sem skráir aðeins þær línur með heimilisföng þar sem héraðið ‘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)
Nú les fyrirspurnin aðeins idx_address2 og snertir ekki borðið address.
Að nota marggilda vísitölur
Sumir dálkar sem á að verðtryggja gætu ekki innihaldið skalargagnategund. Dálkategundir eins og jsonb, arrays и tsvector innihalda samsett eða mörg gildi. Ef þú þarft að skrá slíka dálka, þarftu venjulega að leita í gegnum öll einstök gildi í þeim dálkum.
Við skulum reyna að finna titla allra mynda sem innihalda klippur úr misheppnuðum myndum. Tafla film það er textadálkur sem heitir special_features. Ef kvikmyndin hefur þennan „sérstaka eiginleika“ þá inniheldur dálkurinn frumefnið sem textafylki Behind The Scenes. Til að leita að öllum slíkum kvikmyndum þurfum við að velja allar línur með „Behind The Scenes“ hvenær allir fylkisgildi special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Hreiður rekstraraðili @> athugar hvort hægri hliðin sé hlutmengi af vinstri hliðinni.
Biðja um áætlun:
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)Sem biður um fulla hrúguskönnun sem kostar 67.
Við skulum sjá hvort venjulegur B-tré vísitala hjálpi okkur:
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)Vísitalan var ekki einu sinni tekin til greina. B-tré vísitalan er ekki meðvituð um tilvist einstakra þátta í verðtryggðu gildunum.
Við þurfum GIN vísitölu.
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 vísitalan styður kortlagningu einstakra gilda á móti verðtryggðum samsettum gildum, sem leiðir til kostnaðar við fyrirspurnaráætlun sem er meira en helmingur.
Að losna við tvíteknar vísitölur
Vísitölur safnast upp með tímanum og stundum getur ný vísitala innihaldið sömu skilgreiningu og ein af þeim fyrri. Þú getur notað vörulistayfirlitið til að fá lesanlegar SQL skilgreiningar á vísitölum. pg_indexes. Þú getur líka auðveldlega fundið sömu skilgreiningar:
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)
Ofursett vísitölur
Það getur gerst að þú endir með margar vísitölur, þar af ein sem skráir ofursett af dálkum sem skrá aðrar vísitölur. Þetta getur verið æskilegt eða ekki — ofursettið getur leitt til skanna eingöngu með vísitölu, sem er gott, en það getur tekið of mikið pláss eða fyrirspurnin sem ofursettinu var ætlað að fínstilla er ekki lengur notuð.
Ef þú þarft að gera sjálfvirkan skilgreiningu á slíkum vísitölum geturðu byrjað með frá borði pg_catalog.
Ónotaðar vísitölur
Eftir því sem forrit sem nota gagnagrunna þróast, þróast það líka með fyrirspurnirnar sem þeir nota. Vísitölur sem bætt var við fyrr má ekki lengur nota við hvaða fyrirspurn sem er. Í hvert skipti sem vísitala er skannuð er hún merkt af tölfræðistjóra og í kerfisskrárskjánum pg_stat_user_indexes þú getur séð verðmæti idx_scan, sem er uppsafnaður teljari. Með því að rekja þetta gildi yfir ákveðinn tíma (td mánuð) gefur það góða hugmynd um hvaða vísitölur eru ekki notaðar og gætu fallið niður.
Hér er fyrirspurn til að fá núverandi skannatölur af öllum vísitölum í skemanu ‘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)Endurbyggja vísitölur með færri læsingum
Oft þarf að endurbyggja vísitölur, til dæmis þegar þær verða uppblásnar, og endurbygging getur flýtt fyrir skönnuninni. Einnig geta vísitölur orðið fyrir skemmdum. Breyting á breytum vísitölunnar gæti einnig þurft að endurbyggja hana.
Virkja samhliða vísitölugerð
Í PostgreSQL 11 er búið til B-tré vísitölu samhliða. Til að flýta fyrir sköpunarferlinu er hægt að nota nokkra samhliða starfsmenn. Hins vegar skaltu ganga úr skugga um að þessir stillingarvalkostir séu rétt stilltir:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Sjálfgefin gildi eru of lítil. Helst ættu þessar tölur að hækka ásamt fjölda örgjörvakjarna. Lestu meira í .
Bakgrunnsvísitölugerð
Þú getur búið til vísitölu í bakgrunni með því að nota valkostinn CONCURRENTLY teymi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXÞessi aðferð til að búa til vísitölu er frábrugðin þeirri venjulegu að því leyti að hún krefst ekki læsingar á borðinu og hindrar því ekki skrifaðgerðir. Á hinn bóginn tekur það meiri tíma og eyðir meira fjármagni.
Postgres veitir mikinn sveigjanleika til að búa til vísitölur og leiðir til að leysa öll sérstök tilvik, sem og leiðir til að stjórna gagnagrunninum ef forritið þitt stækkar hratt. Við vonum að þessar ráðleggingar muni hjálpa þér að fá fyrirspurnir þínar hratt og gagnagrunninn þinn tilbúinn til að skala.
Heimild: www.habr.com