I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > Ukusebenzisa zonke izici zezinkomba ku-PostgreSQL

Ukusebenzisa zonke izici zezinkomba ku-PostgreSQL

Ukusebenzisa zonke izici zezinkomba ku-PostgreSQL
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 pagila isampula database.

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_address1bese 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_inkomba etafuleni 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- imibhalo.

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

Engeza amazwana