Munyika yePostgres, indekisi dzakakosha kuti ufambe zvakanaka mudura rekuchengetedza (inonzi murwi). Postgres haitsigire kuungana kwayo, uye iyo MVCC yekuvaka inokonzeresa kuti iwe upedze neshanduro dzakawanda dzeiyo tuple. Naizvozvo, zvakakosha kwazvo kuti ugone kugadzira uye kuchengetedza indekisi inoshanda kutsigira maapplication.
Ini ndinounza kwauri mamwe matipi ekugadzirisa uye kuvandudza mashandisirwo e indexes.
Cherechedza: Mibvunzo inoratidzwa pazasi inoshanda pane isina kuvandudzwa .
Kushandisa Covering Indexes
Ngatitarisei chikumbiro chekudzoreredza email kero dzevashandisi vasingashande. Mutafura customer pane mbiru active, uye mubvunzo wacho uri nyore:
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)
Mubvunzo unodaidza iyo yakazara tafura scan sequence customer. Ngatigadzirei index pa column 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)
Yakabatsirwa, scan yakazotevera yakashandurwa kuita "index scan". Izvi zvinoreva kuti Postgres ichakambaira index "idx_cust1", uye woenderera mberi nekutsvaga murwi wetafura kuti uverenge hutsika hwemamwe makoramu (munyaya iyi, iyo column email) izvo chikumbiro chinoda.
PostgreSQL 11 yakaunza inovhara indexes. Ivo vanokutendera kuti ubatanidze imwe kana mamwe makoramu ekuwedzera mune index pachayo - maitiro avo anochengetwa muchitoro chedata.
Kana isu takashandisa chimiro ichi uye tikawedzera iyo email kukosha mukati meiyo index, saka Postgres yaisazoda kutarisa mumurwi wetafura kukosha. email. Ngationei kana izvi zvichishanda:
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" inotiudza kuti mubvunzo izvozvi unongoda index chete, iyo inobatsira kudzivirira dhisiki yese I / O kuverenga murwi wetafura.
Nhasi, ma indexes ekuvhara anowanikwa chete kune B-miti. Zvisinei, munyaya iyi kuedza kwekugadzirisa kuchave kwakakwirira.
Kushandisa Partial Indexes
Partial indexes indekisi chete chikamu chidiki chemitsara yetafura. Izvi zvinokutendera iwe kuchengetedza index saizi uye kuita scans nekukurumidza.
Ngatitii isu tinoda kuwana runyorwa rweemail kero yevatengi vedu muCalifornia. Chikumbiro chichava seizvi:
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)Ndeapi ma index index achatipa:
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)
Scan address yakatsiviwa ne index scanning idx_address1, uye ipapo murwi wacho wakaongororwa address.
Sezvo uyu uri mubvunzo unogara uchibvunzwa uye uchida kuvandudzwa, tinogona kushandisa chikamu che index, icho chinonongedza chete mitsetse ine kero umo nzvimbo yacho. β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)
Iye zvino chikumbiro chinoverenga chete idx_address2 uye haabati tafura address.
Kushandisa Multi-Value Indexes
Mamwe makoramu anoda kuiswa indexed anogona kunge asina scalar data type. Column mhando dzakadai jsonb, arrays ΠΈ tsvector zvine zvirevo zvakasanganiswa kana zvakawanda. Kana iwe uchida kunyoresa makoramu akadaro, iwe kazhinji unofanirwa kutsvaga ese ega ega hunhu mumakoramu iwayo.
Ngatiedzei kutsvaga mazita emafirimu ese ane zvimedu kubva kune asina kubudirira kutora. Mutafura film pane chinyorwa chinyorwa chinonzi special_features. Kana bhaisikopo riine iyi "special property", saka iyo column ine chinhu chiri muchimiro chemavara array Behind The Scenes. Kuti titsvage mafirimu akadaro, tinoda kusarudza mitsetse yose ine "Behind The Scenes" pa chero array values special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Containment operator @> inotarisa kana rutivi rworudyi ichikamu chiduku cherutivi rworuboshwe.
Chirongwa chemubvunzo:
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)Iyo inokumbira yakazara murwi scan ine mutengo we67.
Ngationei kana indekisi yenguva dzose B-muti ichatibatsira:
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)Iyo index haina kana kutariswa. Iyo B-muti index haina ruzivo nezve kuvepo kwezvinhu zvega muhunhu hwainonongedza.
Tinoda GIN index.
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)Iyo GIN indekisi inotsigira kumepu hunhu hwemunhu kune indexed composite kukosha, zvichikonzera inopfuura hafu yemutengo wechirongwa chemubvunzo.
Kubvisa duplicate indexes
Indekisi dzinoungana nekufamba kwenguva, uye dzimwe nguva indekisi nyowani inogona kunge iine tsananguro yakafanana neyeimwe yapfuura. Iwe unogona kushandisa iyo catalog maonero kuti uwane-inoverengeka SQL index tsananguro pg_indexes. Iwe unogona zvakare kuwana nyore tsananguro dzakafanana:
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
Zvinogona kuitika kuti iwe uunganidze indekisi dzakawanda, imwe yacho inoratidzira superset yemakoramu ayo mamwe indekisi index. Izvi zvinogona kana kusadiwa - superset inogona kukonzera index-chete scan, izvo zvakanaka, asi zvinogona kutora nzvimbo yakawandisa, kana kubvunza iyo superset yaida kukwirisa haichashandiswi.
Kana iwe uchida kuita otomatiki tsananguro yeindekisi dzakadaro, unogona kutanga nazvo kubva patafura pg_catalog.
Ma indexes asina kushandiswa
Sezvo maapplication anoshandisa dhatabhesi anoshanduka, saka mibvunzo yavanoshandisa. Ma indexes aimbowedzerwa anogona kunge asisashandiswe nechero mibvunzo. Pese painotariswa index, inocherechedzwa nemaneja wenhamba uye mune iyo system catalog view pg_stat_user_indexes unogona kuona kukosha idx_scan, inova cumulative counter. Kutevera kukosha uku nekufamba kwenguva (taura mwedzi) kunopa zano rakanaka rekuti ndeapi indexes asiri kushandiswa uye anogona kudonhedzwa.
Hechino chikumbiro chekutora yazvino scan kuverenga yeese indexes mu 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)Kuvakazve indexes nemakiyi mashoma
Ma indexes anowanzoda kugadzirwa patsva, semuenzaniso kana azvimba, uye kugadzira patsva kunogona kukurumidzira kuongorora. Indexes inogona zvakare kushatiswa. Kuchinja index parameter kungangoda kuti igadziriswezve.
Gonesa kugadzira indekisi inofambirana
MuPostgreSQL 11, B-Muti index kusikwa kwakafanana. Kuti ikurumidze kusika maitiro, vashandi vakati wandei vakafanana vanogona kushandiswa. Nekudaro, ita shuwa kuti izvi zvigadziriso zvigadziriso zvakaiswa nenzira kwayo:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Iwo default values ββidiki zvakanyanya. Nenzira yakanaka, nhamba idzi dzinofanira kuwedzera pamwe chete nehuwandu hwema processor cores. Verenga zvakawanda mu .
Background index kugadzira
Iwe unogona kugadzira index kumashure uchishandisa sarudzo CONCURRENTLY mirairo CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXIyi nzira yekugadzira index inosiyana neyakajairika pakuti haidi kukiya tafura, uye nekudaro haivharise mabasa ekunyora. Ukuwo, zvinotora nguva yakareba uye zvinopedza zviwanikwa zvakawanda.
Postgres inopa akawanda anochinjika sarudzo dzekugadzira indexes uye mhinduro kune chero akakosha kesi, uye zvakare inopa nzira dzekutonga dhatabhesi kana application yako ikakura zvakanyanya. Tinovimba kuti matipi aya achakubatsira kuita kuti mibvunzo yako ikurumidze uye dhatabhesi rako rigadzirire kuyera.
Source: www.habr.com