Dunida Postgres, tusmooyinku waxay lagama maarmaan u yihiin hagidda hufan ee kaydinta xogta (loo yaqaan "tuulo"). Postgres kuma taageerto kooxaynta, iyo qaab dhismeedka MVCC wuxuu kuu keenaa inaad la soo baxdo noocyo badan oo isku mid ah. Sidaa darteed, aad bay muhiim u tahay in la abuuro lana ilaaliyo tusmooyin hufan si loo taageero codsiyada.
Waa kuwan qaar ka mid ah talooyinka hagaajinta iyo hagaajinta isticmaalka tusmooyinka.
Fiiro gaar ah: weydiimaha hoos ku xusan waxay ka shaqeeyaan wax aan la beddelin .
Isticmaalka Tilmaamaha Daboolka
Aynu eegno codsiga lagu soo saarayo ciwaannada iimaylka ee isticmaalayaasha aan firfircoonayn. Miis customer tiir baa jira active, su'aashuna waa sahlan tahay:
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)
Weydiinta waxay ku baaqaysaa isku xigxiga iskaanka miiska oo buuxa customer. Aan ku abuurno tusmooyin tiir 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)
Way caawisay, baadhistii xigtay waxay isu rogtay "index scan" Tani waxay ka dhigan tahay in Postgres ay iskaankari doonto tusaha"idx_cust1", ka dibna sii wad raadinta miiska miiska si aad u akhrido qiyamka tiirarka kale (xaaladdan, tiirka email) in su'aashu u baahan tahay.
Tilmaamaha daboolaya waxaa lagu soo bandhigay PostgreSQL 11. Waxay kuu oggolaanayaan inaad ku darto hal ama dhowr tiir oo dheeraad ah oo ku jira index laftiisa - qiyamkooda waxaa lagu kaydiyaa kaydka xogta index.
Haddii aan ka faa'iidaysanay habkan oo aan ku darno qiimaha emailka gudaha tusaha, markaa Postgres uma baahna inay raadiso miiska miiska qiimaha. email. Aan aragno in tani ay shaqayn doonto:
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' waxay noo sheegaysaa in su'aashu hadda u baahan tahay tusmaynta kaliya, taas oo ka caawinaysa in laga fogaado dhammaan I/O diskka si loo akhriyo tuulmada miiska.
Tusmooyinka daboolaya ayaa hadda diyaar u ah geedaha B-ga oo keliya. Si kastaba ha ahaatee, kiiskan, dadaalka dayactirka ayaa noqon doona mid sare.
Isticmaalka Tusmooyinka Qaybta
Tusmooyinka qayb ka mid ah ayaa tilmaamaya kaliya qayb hoose oo ka mid ah safafka miiska. Tani waxay badbaadinaysaa cabbirka tusmooyinka waxayna ka dhigaysaa baaritaannada si degdeg ah.
Aynu nidhaahno waxaan rabnaa inaan helno liiska iimaylka macaamiisheena ee California. Codsigu wuxuu noqonayaa sidan:
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)Waa maxay tusmooyinka caadiga ah ee ina siin doona:
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)
Sawirka address waxaa lagu bedelay sawirka index idx_address1ka dibna sawiray tuubada address.
Maadaama ay tani tahay su'aal soo noqnoqota oo u baahan in la hagaajiyo, waxaan isticmaali karnaa tusaha qayb ka mid ah, kaas oo tilmaamaya kaliya safafka leh ciwaannada degmadu β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)
Hadda su'aashu waxay akhridaa oo keliya idx_address2 oo aan miiska taaban address.
Isticmaalka Tusmooyinka Qiimaha Badan
Tiirarka qaar ee la tilmaamayo waxa laga yaabaa inaanay ku jirin nooc xog miisaan leh. Noocyada safafka sida jsonb, arrays ΠΈ tsvector ka kooban qiimayaal isku dhafan ama dhowr ah. Haddii aad u baahan tahay in aad tilmaamto tiirarkaas, sida caadiga ah waa inaad ka dhex raadisaa dhammaan qiyamka gaarka ah ee tiirarkaas.
Aan isku dayno inaan helno cinwaanada dhammaan filimada ay ku jiraan jarista qaadashada aan lagu guulaysan. Miis film waxaa jira tiir qoraal ah oo la yiraahdo special_features. Haddii filimku leeyahay kan "hanti gaar ah", markaas tiirku waxa uu ka kooban yahay curiyaha sida hab qoraal ah Behind The Scenes. Si loo raadiyo dhammaan filimada noocaas ah, waxaan u baahannahay inaan dooranno dhammaan safafka leh "Behind The Scenes" marka nasiib qiimaha habaysan special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Hawlwadeenka buulka @> hubi haddii dhinaca midig yahay qayb-hoosaadka dhinaca bidix.
Codsiga qorshaha:
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)Kaas oo codsanaya iskaanka tuulan buuxa oo qiimahiisu yahay 67.
Aynu aragno haddii tusmada caadiga ah ee B-geed ay nagu caawiso:
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)Tusmada xitaa lama tixgelin. Tusmada geedka B-ga kama warqabo jiritaanka xubno gaar ah oo ku jira qiyamka la tilmaamay.
Waxaan u baahanahay index 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)Tusmada GIN waxay taageertaa khariidaynta qiyamka halliga ah ee ka dhanka ah qiyamka isku dhafan ee la tilmaamay, taasoo keentay kharashka qorshaha weydiinta oo ka badan kala badh.
Ka takhalusidda tusmooyinka nuqulka ah
Indexyadu waxay ururaan waqti ka dib, mararka qaarkoodna tusmada cusub ayaa laga yaabaa inay ku jirto qeexitaan la mid ah mid ka mid ah kuwii hore. Waxaad isticmaali kartaa aragtida buugaagta si aad u hesho qeexitaannada SQL-ga bini'aadmigu akhriyi karo. pg_indexes. Waxa kale oo aad si fudud u heli kartaa qeexitaanno isku mid ah:
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)
Tusmooyinka Superset
Waxa dhici karta in aad ku dhammaato tusmooyin badan, kuwaas oo mid ka mid ah uu tilmaamayo tiro tiro badan oo tilmaamaysa tusmooyin kale. Tani waxaa laga yaabaa ama laga yaabo inay noqoto mid la jecel yahay - superset-ka ayaa laga yaabaa inuu keeno baaritaanno index-kaliya ah, taas oo wanaagsan, laakiin waxay qaadan kartaa meel aad u badan, ama weydiinta in superset-ka loogu talagalay in lagu hagaajiyo lama isticmaalo.
Haddii aad u baahan tahay inaad si otomaatig ah u qeexdo tusmooyinka noocaas ah, waxaad ku bilaabi kartaa miiska ka soo pg_catalog.
Tilmaamayaal aan la isticmaalin
Sida codsiyada adeegsada xog-ururinta ay u horumaraan, sidaas oo kale way u kobcaan weydiimaha ay adeegsadaan. Tusmooyinka lagu daray mar hore lagama yaabo in la isticmaalo su'aal kasta. Mar kasta oo tusmada la sawiro, waxaa calaamadiya maamulaha tirakoobka, iyo muuqaalka nidaamka liiska pg_stat_user_indexes waxaad arki kartaa qiimaha idx_scan, kaas oo ah xisaabiye isugeyn ah. Raad-raacista qiimahan muddo wakhti ah (bille dheh) waxay fikrad fiican ka siin doontaa tusmooyinka aan la isticmaalin oo la tuuri karo.
Halkan waxaa ah su'aal si aad u hesho tirooyinka sawirka hadda jira ee dhammaan tusmooyinka shaxda β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)Dib u dhiska tusmooyinka leh qufulo yar
Tusmooyinka inta badan waxay u baahan yihiin dib-u-dhis, tusaale ahaan marka ay bararaan, dib-u-dhisidda waxay soo dedejin kartaa sawirka. Sidoo kale tusmooyinka waa la kharribi karaa. Beddelidda cabbirrada tusaha waxay sidoo kale u baahan kartaa dib-u-dhiskeeda.
Daar abuurista isbarbardhigga
Gudaha PostgreSQL 11, abuurista tusmada B-Tree waa isku mid. Si loo dedejiyo geeddi-socodka abuurista, shaqaale badan oo isbar-bar yaac ah ayaa la isticmaali karaa. Si kastaba ha ahaatee, hubi in xulashooyinka qaabeynta si sax ah loo dejiyay:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Qiimaha caadiga ah aad bay u yar yihiin. Fikrad ahaan, tirooyinkani waa inay kordhiyaan tirada kombuyuutarrada. Wax badan ka sii akhri .
Abuuritaanka tilmaanta asalka ah
Waxaad samayn kartaa index xagga dambe adigoo isticmaalaya ikhtiyaarka CONCURRENTLY amarrada CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXHabka abuurista tusmada ayaa ka duwan kan caadiga ah in uusan u baahnayn quful miiska saaran, sidaas darteed ma xannibayo hawlaha qorista. Dhanka kale, waxay qaadataa waqti badan waxayna isticmaashaa ilo badan.
Postgres waxay bixisaa dabacsanaan badan si loo abuuro tusmooyin iyo siyaabo lagu xalliyo kiis kasta oo gaar ah, iyo sidoo kale siyaabaha loo maareeyo xogta xogta haddii codsigaagu u kordho. Waxaan rajeyneynaa in talooyinkaan ay kaa caawin doonaan inaad si degdeg ah u hesho weydiimahaaga iyo xogtaada oo diyaar u ah in la cabbiro.
Source: www.habr.com