Di cîhana Postgres de, index ji bo navîgasyonek bikêrhatî ya hilanîna databasê (bi navê "heap") pêdivî ye. Postgres ji bo wê kombûnê piştgirî nake, û mîmariya MVCC dibe sedem ku hûn bi gelek guhertoyên heman tîrêjê biqedin. Ji ber vê yekê, pir girîng e ku meriv bikaribe ji bo piştgirîkirina serîlêdanan indexên bikêr biafirîne û biparêze.
Li vir çend serişteyên ji bo xweşbînkirin û başkirina karanîna îndeksan hene.
Nîşe: Pirsên ku li jêr têne xuyang kirin li ser neguhêrbar dixebitin .
Bikaranîna Indeksên Vegirtinê
Ka em li daxwazek ji bo derxistina navnîşanên e-nameyê ji bo bikarhênerên neçalak binêrin. Mêz customer stûnek heye active, û pirs hêsan e:
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)
Lêpirsîn rêzika şopandina tabloya tevahî vedixwîne customer. Werin em li ser stûnek indexek çêbikin 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)
Ew arîkar kir, şanoya paşîn veguherî "index scan". Ev tê vê wateyê ku Postgres dê navnîşê bişopîne "idx_cust1", û dûv re li ser komê tabloyê lêgerîna xwe bidomînin da ku nirxên stûnên din bixwînin (di vê rewşê de, stûn email) ku pirs hewce dike.
Indeksên vegirtinê di PostgreSQL 11 de têne destnîşan kirin. Ew dihêlin ku hûn yek an çend stûnên pêvek di nav pêvekê de bixwe - nirxên wan di dikana daneya pêvekê de têne hilanîn.
Ger em vê taybetmendiyê bikar bînin û nirxa e-nameyê di hundurê pêvekê de zêde bikin, wê hingê Postgres hewce nake ku li ser nirxa tabloyê bigere. email. Ka em bibînin ka ev ê bixebite:
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' ji me re vedibêje ku pirs naha tenê pêdivî bi pêvekek heye, ku ji hemî dîskê I/O dûr dikeve da ku berhevoka tabloyê bixwîne.
Indeksên vegirtinê niha tenê ji bo darên B-yê hene. Lêbelê, di vê rewşê de, hewldana lênêrînê dê bilindtir be.
Bikaranîna Indeksên Parçeyî
Indeksên qismî di tabloyê de tenê komek rêzên rêzan nîşan didin. Ev mezinahiya îndeksan xilas dike û şopan zûtir dike.
Ka em bibêjin ku em dixwazin navnîşek navnîşanên e-nameya xerîdarên xwe li California bistînin. Daxwaz dê wiha be:
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)Nîşaneyên asayî dê çi bidin me:
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)
Skanîn address li şûna şanoya indexê hatiye idx_address1û dûv re girs skenand address.
Ji ber ku ev pirsek pir caran ye û pêdivî ye ku were xweşbîn kirin, em dikarin pêdekek qismî bikar bînin, ku tenê wan rêzên bi navnîşanên ku navçe tê de ye nîşan dide. ‘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)
Niha pirs tenê dixwîne idx_address2 û dest nade maseyê address.
Bikaranîna Indeksên Pir-Nirx
Dibe ku hin stûnên ku werin îndeks kirin celebek daneya skalar nebin. Cureyên stûnên mîna jsonb, arrays и tsvector nirxên pêkhatî an pirjimar hene. Heke hûn hewce ne ku stûnên weha navnîş bikin, bi gelemperî pêdivî ye ku hûn li hemî nirxên kesane yên di wan stûnan de bigerin.
Werin em hewl bidin ku sernavên hemî fîlmên ku di nav wan de qutkirinên neserkeftî hene bibînin. Mêz film stûnek nivîsê heye ku jê re tê gotin special_features. Ger fîlim xwediyê vê "taybetmendiya taybetî" be, wê hingê di stûnê de hêmanek wekî rêzek nivîsê heye Behind The Scenes. Ji bo lêgerîna hemî fîlmên weha, pêdivî ye ku em hemî rêzikên bi "Behind The Scenes" dema ku hilbijêrin her nirxên array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operatorê hêlînê @> kontrol dike ka aliyê rastê beşek ji aliyê çepê ye.
Plana daxwaznameyê:
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)Ya ku bi lêçûnek 67 skanek tevdehek daxwaz dike.
Ka em bibînin ka nîşanek B-dara birêkûpêk ji me re dibe alîkar:
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)Endeks jî nehat hesibandin. Indeksa dara B ji hebûna hêmanên takekesî yên di nirxên pêvekirî de nizane.
Pêdiviya me bi navnîşek GIN heye.
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)Indeksa GIN piştgirî dide nexşeya nirxên yekane li ser nirxên pêkhatî yên pêvekirî, ku di encamê de lêçûnek plansaziyek pirsê ku ji nîvî zêdetir e.
Rakirina navnîşên dubare
Indeks bi demê re kom dibin, û carinan dibe ku navnîşek nû heman pênase wekî yek ji yên berê hebe. Hûn dikarin dîtina katalogê bikar bînin da ku pênaseyên SQL-ya ku ji hêla mirovan ve têne xwendin bistînin. pg_indexes. Her weha hûn dikarin bi hêsanî pênaseyên wekhev bibînin:
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)
Indeksên Superset
Dikare biqewime ku hûn bi gelek îndeksan re biqedin, yek ji wan stûnek stûnek ku îndeksên din indeks dike destnîşan dike. Dibe ku ev xwestek be an jî nebe - dibe ku superset bi tenê îskanên îndeksê encam bide, ku ev baş e, lê dibe ku ew pir cîh bigire, an jî pirsa ku superset ji bo xweşbîniyê hatî armanc kirin êdî nayê bikar anîn.
Heke hûn hewce ne ku pênasekirina pêvekên weha otomatîk bikin, hûn dikarin bi dest pê bikin ji sifrê pg_catalog.
Indeksên nebikaranîn
Her ku serîlêdanên ku databases bikar tînin pêşve diçin, pirsên ku ew bikar tînin jî pêşve diçin. Indeksên ku berê hatine zêdekirin dibe ku êdî ji hêla pirsê ve neyê bikar anîn. Her cara ku îndeksek tê şopandin, ew ji hêla rêveberê statîstîkê ve, û di dîtina kataloga pergalê de tê nîşankirin. pg_stat_user_indexes hûn dikarin nirxê bibînin idx_scan, ku jimarvanek komkirî ye. Şopandina vê nirxê di heyamekê de (bibêjin mehekê) dê ramanek baş bide ka kîjan navnîşan nayên bikar anîn û dikarin werin avêtin.
Li vir pirsek heye ku jimareyên şopandina heyî yên hemî navnîşên di şemayê de bistînin ‘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)Ji nû ve avakirina indexan bi kêm kilît
Indeks bi gelemperî hewce ne ku ji nû ve werin çêkirin, mînakî gava ku ew şîn dibin, û ji nû ve ava kirin dikare lêgerînê zûtir bike. Di heman demê de index dikarin xera bibin. Guhertina pîvanên indexê jî dibe ku ji nû ve avakirina wê hewce bike.
Afirandina index paralel çalak bike
Di PostgreSQL 11 de, afirandina indexek B-Tree hevdem e. Ji bo bilezkirina pêvajoya afirandinê, çend xebatkarên paralel dikarin werin bikar anîn. Lêbelê, pê ewle bine ku ev vebijarkên mîhengê rast hatine danîn:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Nirxên xwerû pir piçûk in. Bi îdeal, divê ev jimar digel hejmara navikên pêvajoyê zêde bibin. Zêdetir bixwînin .
Afirandina index Background
Hûn dikarin bi karanîna vebijarkê di paşîn de indexek biafirînin CONCURRENTLY ferman dike CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEv pêvajoya çêkirina îndeksê ji ya gelemperî cûda dibe ku ew li ser masê qeflek hewce nake, û ji ber vê yekê operasyonên nivîsandinê asteng nake. Ji aliyê din ve, ew bêtir dem digire û bêtir çavkaniyan dixwe.
Postgres ji bo afirandina navnîşan û awayên mijûlbûna bi her rewşên taybetî re, û her weha awayên birêvebirina databasê ji bo ku serîlêdana we bi tundî mezin bibe, gelek nermbûn peyda dike. Em hêvî dikin ku ev serişte dê ji we re bibin alîkar ku hûn pirsên xwe zû û databasa xwe ji bo pîvandinê amade bikin.
Source: www.habr.com