Kwihlabathi le-Postgres, izalathisi zibaluleke kakhulu ekuhambeni ngokufanelekileyo ukugcinwa kwedatha (ebizwa ngokuba yimfumba). I-Postgres ayikuxhasi ukuhlanganisana kwayo, kwaye ulwakhiwo lwe-MVCC lubangela ukuba ugqibe ngeenguqulelo ezininzi ze-tuple efanayo. Ngoko ke, kubaluleke kakhulu ukukwazi ukudala nokugcina izalathisi ezisebenzayo zokuxhasa izicelo.
Ndizisa kwingqwalasela yakho iingcebiso zokuphucula kunye nokuphucula ukusetyenziswa kwezalathisi.
Qaphela: Imibuzo eboniswe ngezantsi isebenza ekungalungiswanga .
Ukusebenzisa Izalathisi Ezigubungelayo
Masijonge kwisicelo sokufumana kwakhona iidilesi ze-imeyile kubasebenzisi abangasebenziyo. Etafileni customer kukho ikholamu active, kwaye 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 ubiza ulandelelwano olupheleleyo lwetafile yokuskena customer. Masenze isalathiso kwikholamu 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)
Uncedo, iskena esilandelayo sajika saba "index scan". Oku kuthetha ukuba iPostgres iyakugaqa kwisalathiso "idx_cust1", kwaye emva koko uqhubeke nokukhangela kwimfumba yetafile ukuze ufunde amaxabiso eminye imihlathi (kule meko, ikholamu email) esifunwa sisicelo.
I-PostgreSQL 11 yazisa izalathisi zokugquma. Bakuvumela ukuba ubandakanye ikholamu enye okanye ngaphezulu kwisalathiso ngokwaso - ixabiso labo ligcinwe kwisitoreji sedatha yesalathisi.
Ukuba sisebenzise eli nqaku kwaye songeza ixabiso le-imeyile ngaphakathi kwesalathiso, emva koko iPostgres ayizukufuna ukujonga kwimfumba yetafile ngexabiso. email. Makhe sibone ukuba oku kuyasebenza:
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" isixelela ukuba umbuzo ngoku ufuna kuphela isalathisi, esinceda ukunqanda yonke idiski ye-I/O ukufunda imfumba yetafile.
Namhlanje, izalathisi zokugquma zifumaneka kuphela kwimithi ye-B. Nangona kunjalo, kule meko inzame yokugcina iya kuba phezulu.
Ukusebenzisa Izalathisi Eziyinxalenye
Izalathisi ezingaphelelanga zesalathiso kuphela iseti esezantsi yemigca yetafile. Oku kukuvumela ukuba ugcine ubungakanani besalathisi kwaye wenze iskeni ngokukhawuleza.
Masithi kufuneka sifumane uluhlu lweedilesi ze-imeyile kubathengi bethu baseCalifornia. Isicelo siya kuba ngolu hlobo:
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)Zeziphi izalathisi eziqhelekileyo eziza kusinika:
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)
Skena address indawo yayo ithathwe sisalathisi sokuskena idx_address1, emva koko imfumba yaskenwa address.
Kuba lo ngumbuzo oqhelekileyo kwaye udinga ukuphuculwa, sinokusebenzisa isalathiso esingaphelelanga, esisalathisa kuphela loo migca ineedilesi ezikuyo indawo. β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)
Ngoku isicelo sifundeka kuphela idx_address2 kwaye ayichukumisi itafile address.
Ukusebenzisa ii-Multi-Value Indexes
Eminye imiqolo efuna isalathisi isenokungaqulathanga uhlobo lwedatha ye-scalar. Iindidi zekholamu ezifana jsonb, arrays ΠΈ tsvector ziqulathe iintsingiselo ezimbaxa okanye ezininzi. Ukuba ufuna ukwenza izalathiso zemihlathi enjalo, kuya kufuneka ukhangele onke amaxabiso kuloo miqolo.
Makhe sizame ukufumana izihloko zazo zonke iifilimu eziqulathe iikliphu ezivela kumanyathelo angaphumelelanga. Etafileni film kukho umhlathi wokubhaliweyo obizwa special_features. Ukuba ifilimu inalo "ipropati ekhethekileyo", ngoko ikholomu iqulethe into ngendlela yombhalo wesicatshulwa Behind The Scenes. Ukukhangela zonke iifilimu ezinjalo, kufuneka sikhethe yonke imiqolo ethi "Emva kweSigcawu" apha naluphi na uluhlu lwamaxabiso special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Umsebenzisi wogcino @> ijonga ukuba icala lasekunene liseti esezantsi yecala lasekhohlo.
Isicwangciso sombuzo:
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)Ecela ukuskena imfumba epheleleyo ngexabiso lama-67.
Makhe sibone ukuba isalathiso esiqhelekileyo somthi ongu-B siya kusinceda na:
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)Isalathiso asizange siqwalaselwe. Isalathiso somthi we-B asinalo nofifi malunga nobukho bezinto ezizimeleyo kumaxabiso azalathisayo.
Sifuna isalathisi se-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)Isalathiso se-GIN sixhasa ukwenza imephu amaxabiso omntu ngamnye ukuya kumaxabiso ahlanganisiweyo, okukhokelela ngaphezulu kwesiqingatha seendleko zesicwangciso sombuzo.
Ukususa izalathisi eziphindiweyo
Izalathisi ziyaqokelelana ekuhambeni kwexesha, kwaye ngamanye amaxesha isalathiso esitsha sisenokuba nengcaciso efanayo nenye yezo zangaphambili. Ungasebenzisa imboniselo yekhathalogu ukufumana iinkcazelo zesalathiso zeSQL ezifundekayo ngabantu pg_indexes. Unokufumana ngokulula iinkcazo 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)
Superset Indexes
Kunokwenzeka ukuba uqokelele izalathisi ezininzi, enye yazo izalathisa isethi ephezulu yeekholamu zezinye izalathiso. Oku kunganqweneleka okanye kunganqweneleki - i-superset ingabangela isalathisi-kuphela iskena, esilungileyo, kodwa sinokuthatha indawo eninzi kakhulu, okanye umbuzo owawucetywe ukuba i-superset iwukhulise awusasetyenziswa.
Ukuba ufuna ukuzenzekelayo inkcazo yezalathisi ezinjalo, ungaqala ngazo etafileni pg_catalog.
Izalathisi ezingasetyenziswanga
Njengoko izicelo ezisebenzisa uvimba weenkcukacha ziguquka, ngokunjalo nemibuzo abayisebenzisayo. Izalathisi ebezifakwe ngaphambili azinakuphinda zisetyenziswe yiyo nayiphi na imibuzo. Ngalo lonke ixesha isalathiso siskenwa, siphawulwa ngumphathi weenkcukacha-manani nakwimbono yekhathalogu yenkqubo pg_stat_user_indexes uyakwazi ukubona ixabiso idx_scan, into yokubala eyongezelekayo. Ukulandelela eli xabiso kwithuba lexesha (masithi inyanga) kuya kunika umbono olungileyo wokuba zeziphi na izalathisi ezingasetyenziswanga kwaye zinokuyehliswa.
Nasi isicelo sokufumana amanani ovavanyo lwangoku lwazo zonke izalathisi kwi-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 kwakhona izalathisi kunye nezitshixo ezimbalwa
Izalathisi zihlala zifuna ukuphinda zenziwe kwakhona, umzekelo xa zidumbile, kwaye ukuyilwa kwakhona kunokukhawulezisa ukuskena. Izalathisi nazo zinokonakala. Ukutshintsha iiparamitha zesalathiso kunokufuna ukwenziwa kwayo kwakhona.
Yenza isalathisi esinxuseneyo
Kwi-PostgreSQL 11, ukudalwa kwesalathisi se-B-Tree kuhambelana. Ukukhawulezisa inkqubo yokudala, abasebenzi abaninzi abahambelanayo banokusetyenziswa. Nangona kunjalo, qiniseka ukuba olu seto lolungelelwaniso lusetwe ngokuchanekileyo:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Amaxabiso angagqibekanga mancinci kakhulu. Ngokufanelekileyo, la manani kufuneka anyuke kunye nenani le-processor cores. Funda ngakumbi kwi .
Ukwenziwa kwesalathiso esingasemva
Unokwenza isalathisi ngasemva usebenzisa ukhetho CONCURRENTLY imiyalelo CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXLe nkqubo yokudala isalathiso iyahluka kwisiqhelo kuba ayifuni ukutshixa itafile, kwaye ke ayivali imisebenzi yokubhala. Ngakolunye uhlangothi, kuthatha ixesha elide kwaye kudla izixhobo ezininzi.
I-Postgres ibonelela ngeendlela ezininzi eziguquguqukayo ekudaleni izalathisi kunye nezisombululo kuzo naziphi na iimeko ezikhethekileyo, kwaye ikwabonelela ngeendlela zokulawula i-database ukuba isicelo sakho sikhula ngokukhawuleza. Siyathemba ukuba ezi ngcebiso ziya kukunceda wenze imibuzo yakho ikhawuleze kwaye idatabase yakho ilungele ukukala.
umthombo: www.habr.com