Emhlabeni we-Postgres, izinkomba zibalulekile ekuzulazuleni okusebenzayo kokugcinwa kwesizindalwazi (okubizwa ngokuthi "inqwaba"). I-Postgres ayikusekeli ukuqoqwa kwayo, futhi ukwakheka kwe-MVCC kubangela ukuthi ugcine unezinguqulo eziningi ze-tuple efanayo. Ngakho-ke, kubaluleke kakhulu ukwazi ukudala nokugcina izinkomba ezisebenzayo ukuze usekele izinhlelo zokusebenza.
Nawa amanye amathiphu okuthuthukisa nokwenza ngcono ukusetshenziswa kwezinkomba.
Qaphela: imibuzo eboniswe ngezansi isebenza kokungalungiswanga
Ukusebenzisa Izinkomba Zokumboza
Ake sibheke isicelo sokukhipha amakheli e-imeyili kubasebenzisi abangasebenzi. Ithebula customer
kukhona ikholomu active
, futhi umbuzo ulula:
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)
Umbuzo ucela ukulandelana kwetafula eligcwele customer
. Ake sakhe inkomba kukholamu 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)
Kwasiza, ukuskena okulandelayo kwaphenduka "index scan
". Lokhu kusho ukuthi i-Postgres izoskena inkomba "idx_cust1
", bese uqhubeka nokusesha inqwaba yetafula ukuze ufunde amanani amanye amakholomu (kulokhu, ikholomu email
) okudingwa umbuzo.
Izinkomba zokumboza zethulwa ku-PostgreSQL 11. Bakuvumela ukuthi ufake ikholomu eyodwa noma ngaphezulu kunkomba ngokwayo - amanani abo agcinwa esitolo sedatha yenkomba.
Uma besingasebenzisa lesi sici futhi sengeze inani le-imeyili ngaphakathi kwenkomba, khona-ke i-Postgres ngeke idinge ukuthi iseshe inani lenqwaba yetafula. email
. Ake sibone ukuthi lokhu kuzosebenza yini:
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
' isitshela ukuthi umbuzo manje udinga kuphela inkomba, esiza ukugwema yonke idiski I/O ukufunda inqwaba yetafula.
Izinkomba zokumboza okwamanje zitholakala kuphela ezihlahleni ze-B. Kodwa-ke, kulokhu, umzamo wokulondoloza uzoba phezulu.
Ukusebenzisa Izikhombo Eziyingxenye
Ingxenye yezinkomba inkomba kuphela isethi engaphansi yemigqa kuthebula. Lokhu kusindisa usayizi wezinkomba futhi kwenza ukuskena kusheshe.
Ake sithi sifuna ukuthola uhlu lwamakheli e-imeyili amakhasimende ethu ase-California. Isicelo sizoba kanje:
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)
Yiziphi izinkomba ezijwayelekile ezizosinika zona:
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)
Iskena address
ithathelwe indawo yi-index scan idx_address1
bese uskena inqwaba address
.
Njengoba lona kuwumbuzo ovamile futhi udinga ukuthuthukiswa, singasebenzisa inkomba eyingxenye, ekhomba kuphela leyo migqa enamakheli lapho isifunda β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)
Manje umbuzo ufundwa kuphela idx_address2
futhi alithinti itafula address
.
Ukusebenzisa ama-Multi-Value Indexes
Amanye amakholomu azokhonjwa angeke aqukathe uhlobo lwedatha yesikali. Izinhlobo zekholomu ezifana jsonb
, arrays
ΠΈ tsvector
aqukethe amanani ayinhlanganisela noma amaningi. Uma udinga ukukhomba amakholomu anjalo, ngokuvamile kufanele ucinge kuwo wonke amanani angawodwana kulawo makholomu.
Ake sizame ukuthola izihloko zawo wonke amafilimu aqukethe ukusikeka kokuthatha okungaphumelelanga. Ithebula film
kukhona ikholomu yombhalo ebizwa special_features
. Uma imuvi inalokhu "impahla ekhethekile", khona-ke ikholomu iqukethe i-elementi njengohlu lombhalo Behind The Scenes
. Ukusesha wonke amafilimu anjalo, sidinga ukukhetha yonke imigqa ethi "Behind The Scenes" nini Π»ΡΠ±ΡΡ
amanani afanayo special_features
:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
U-opharetha okhiqiza isidleke @>
ihlola ukuthi uhlangothi lwesokudla luyisethi engaphansi yohlangothi lwesobunxele.
Cela uhlelo:
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)
Okucela ukuskenwa kwenqwaba egcwele ngezindleko ezingama-67.
Ake sibone ukuthi inkomba evamile ye-B-tree iyasisiza yini:
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)
Inkomba ayizange icatshangwe. Inkomba yesihlahla esingu-B ayiqapheli ukuba khona kwezakhi ngazinye kumanani anenkomba.
Sidinga inkomba ye-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)
Inkomba ye-GIN isekela ukudwetshwa kwevelu eyodwa ukuya kumanani ayinhlanganisela anenkomba, okuholela ezindlekweni zohlelo lombuzo ezingaphezu kukahhafu.
Ukuqeda izinkomba eziyimpinda
Izinkomba ziyanqwabelana ngokuhamba kwesikhathi, futhi ngezinye izikhathi inkomba entsha ingase iqukathe incazelo efanayo nenye yangaphambili. Ungasebenzisa ukubuka kwekhathalogi ukuze uthole izincazelo zezinkomba ze-SQL ezifundeka umuntu. pg_indexes
. Ungathola kalula izincazelo ezifanayo:
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)
I-Superset Indexes
Kungenzeka ukuthi ugcine unezinkomba eziningi, enye yazo ekhomba isethi enkulu yamakholomu ekhomba ezinye izinkomba. Lokhu kungase kufiseleke noma kungafiselekiβi-superset ingase ibangele ukuskena kwenkomba kuphela, okuhle, kodwa kungase kuthathe isikhala esiningi, noma umbuzo i-superset eyayihloselwe ukuwuthuthukisa awusasetshenziswa.
Uma udinga ukwenza ngokuzenzakalelayo incazelo yezinkomba ezinjalo, ungaqala ngazo pg_catalog
.
Izinkomba ezingasetshenzisiwe
Njengoba izinhlelo zokusebenza ezisebenzisa imininingwane yolwazi zishintsha, kanjalo nemibuzo eziyisebenzisayo. Izinkomba ezengezwe ngaphambilini ngeke zisasetshenziswa inoma yimuphi umbuzo. Ngaso sonke isikhathi inkomba iskenwa, imakwa ngumphathi wezibalo, nasekubukeni kwekhathalogi yesistimu pg_stat_user_indexes
ungabona inani idx_scan
, okuyikhawunta eqoqekayo. Ukulandelela leli nani phakathi nenkathi yesikhathi (isho inyanga) kuzonikeza umbono omuhle wokuthi yiziphi izinkomba ezingasetshenziswa futhi ezingase zehliswe.
Nawu umbuzo wokuthola izibalo zokuskena zamanje zazo zonke izinkomba ku-schema β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)
Ukwakha kabusha izinkomba ezinamalokhi ambalwa
Izinkomba ngokuvamile zidinga ukwakhiwa kabusha, isibonelo lapho zikhukhumala, futhi ukwakha kabusha kungasheshisa ukuskena. Futhi izinkomba zingonakala. Ukushintsha amapharamitha enkomba kungase kudinge ukwakhiwe kabusha.
Nika amandla ukudalwa kwenkomba ehambisanayo
Ku-PostgreSQL 11, ukudala inkomba ye-B-Tree kuyafana. Ukusheshisa inqubo yokudala, izisebenzi eziningana ezihambisanayo zingasetshenziswa. Nokho, qiniseka ukuthi lezi zinketho zokumisa zisethwe ngendlela efanele:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;
Amanani azenzakalelayo mancane kakhulu. Ngokufanelekile, lezi zinombolo kufanele zikhuphuke kanye nenani lama-processor cores. Funda okungakumbi ngo-
Ukudalwa kwenkomba yengemuva
Ungakha inkomba ngemuva usebenzisa inketho CONCURRENTLY
imiyalo CREATE INDEX
:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX
Le nqubo yokudala inkomba ihluke kwejwayelekile ngoba ayidingi ukukhiya etafuleni, ngakho-ke ayivimbeli imisebenzi yokubhala. Ngakolunye uhlangothi, kuthatha isikhathi esiningi futhi kudla izinsiza ezengeziwe.
I-Postgres inikeza ukuguquguquka okuningi kokwenza izinkomba nezindlela zokuxazulula noma yimaphi amacala akhethekile, kanye nezindlela zokuphatha isizindalwazi uma kwenzeka uhlelo lwakho lokusebenza lukhula ngokuqhuma. Sithemba ukuthi lawa macebiso azokusiza ukuthi uthole imibuzo yakho ngokushesha futhi isizindalwazi sakho silungele ukukala.
Source: www.habr.com