Lefatšeng la Postgres, li-index li bohlokoa bakeng sa ho tsamaisa ka nepo polokelong ea polokelo (e bitsoang "qubu"). Postgres ha e tšehetse ho e kopanya, 'me meralo ea MVCC e etsa hore u qetelle u e-na le mefuta e mengata ea tuple e tšoanang. Ka hona, ho bohlokoa haholo ho khona ho theha le ho boloka li-index tse sebetsang hantle ho ts'ehetsa lits'ebetso.
Mona ke malebela a ho ntlafatsa le ho ntlafatsa ts'ebeliso ea li-index.
Tlhokomeliso: Lipotso tse bontšitsoeng ka tlase li sebetsa ho e sa fetoloang .
Ho Sebelisa Litlhaloso Tse Koahelang
Ha re shebeng kopo ea ho ntša liaterese tsa lengolo-tsoibila bakeng sa basebelisi ba sa sebetseng. Tafole customer ho na le tšiea active, mme potso e bonolo:
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)
Potso e kenyelletsa tatelano e felletseng ea tafole customer. Ha re theheng index holim'a kholomo 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)
E thusitse, skena se latelang se ile sa fetoha "index scan". Sena se bolela hore Postgres e tla hlahloba index "idx_cust1", ebe o tsoela pele ho batla qubu ea tafole ho bala boleng ba likholomo tse ling (tabeng ena, kholomo email) seo potso e se hlokang.
Li-index tse koahelang li hlahisoa ho PostgreSQL 11. Li u lumella ho kenyelletsa kholomo e le 'ngoe kapa ho feta ho index ka boeona - litekanyetso tsa tsona li bolokiloe lebenkeleng la data la index.
Haeba re ka nka monyetla ka karolo ena mme ra eketsa boleng ba lengolo-tsoibila ka har'a index, Postgres e ne e ke ke ea hloka ho batla qubu ea tafole bakeng sa boleng. email. Ha re bone hore na sena se tla sebetsa:
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' e re bolella hore potso hona joale e hloka feela index, e thusang ho qoba disk I / O eohle ho bala qubu ea tafole.
Li-index tse koahelang li fumaneha feela bakeng sa lifate tsa B. Leha ho le joalo, tabeng ena, boiteko ba tlhokomelo bo tla ba bo phahameng.
Ho Sebelisa Li-Index tse sa Feleng
Karolo ea li-index e bonts'a karoloana feela ea mela e tafoleng. Sena se boloka boholo ba li-index mme se etsa hore likhakanyo li potlake.
Ha re re re batla ho fumana lethathamo la liaterese tsa imeile tsa bareki ba rona ba California. Kopo e tla ba tjena:
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)Li-index tse tloaelehileng li tla re fa eng:
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 e nketsoe sebaka ke index ea index idx_address1ebe o hlahloba qubu eo address.
Kaha sena ke potso e etsahalang khafetsa, 'me e hloka ho ntlafatsoa, re ka sebelisa index ea likarolo, e bonts'ang feela mela e nang le liaterese moo setereke. ‘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)
Hona joale potso e baloa feela idx_address2 mme ha e ame tafole address.
Ho Sebelisa Li-Index tsa Boleng bo bongata
Likholomo tse ling tse tla hlahisoa li kanna tsa se be le mofuta oa data oa scalar. Mefuta ea likholomo joalo ka jsonb, arrays и tsvector e na le boleng bo kopaneng kapa bo bongata. Haeba o hloka ho supa likholomo tse joalo, hangata o tlameha ho batla ka har'a boleng ba motho ka mong likholomong tseo.
Ha re leke ho fumana lihlooho tsa lifilimi tsohle tse nang le likhaello tse tsoang ho tse sa atleheng. Tafole film ho na le kholomo ea mongolo e bitsoang special_features. Haeba filimi e na le "thepa e khethehileng", joale kholomo e na le karolo e le lethathamo la mongolo Behind The Scenes. Ho batla lifilimi tsohle tse joalo, re hloka ho khetha mela eohle e nang le "Behind The Scenes" ha efe kapa efe lihlopha tsa boleng special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nesting operator @> e hlahloba hore na lehlakore le letona ke karoloana ea lehlakore le letšehali.
Kopa moralo:
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)E kopang tlhahlobo e felletseng ea qubu ka theko ea 67.
Ha re bone hore na index ea kamehla ea lifate tsa B e re thusa:
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)Index e ne e sa tsejoe le ho nkoa. Lenane la B-tree ha le tsebe ka boteng ba likarolo tsa motho ka mong ho boleng ba indexed.
Re hloka index ea 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)Lenane la GIN le ts'ehetsa ho etsa 'mapa oa boleng bo le bong khahlano le boleng ba likarolo tse thathamisitsoeng, e leng se bakang litšenyehelo tsa moralo oa lipotso tse fetang halofo.
Ho tlosa li-index tse kopitsoang
Li- index lia bokellana ha nako e ntse e ea, ’me ka linako tse ling index e ncha e ka ’na ea e-ba le tlhaloso e tšoanang le e ’ngoe ea tse fetileng. U ka sebelisa pono ea lethathamo la libuka ho fumana litlhaloso tsa SQL tse baloang ke batho tsa li-index. pg_indexes. U ka boela ua fumana litlhaloso tse tšoanang habonolo:
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
Ho ka etsahala hore u qetelle u e-na le li-index tse ngata, tseo e 'ngoe ea tsona e bontšang li-columns tse bontšang li-index tse ling. Sena se ka 'na sa e-ba se lakatsehang kapa se se ke sa lakatseha-superset e ka' na ea fella ka li-index-feela scans, tse ntle, empa li ka nka sebaka se ngata haholo, kapa potso eo superset e neng e reretsoe ho e ntlafatsa ha e sa sebelisoa.
Haeba o hloka ho iketsetsa tlhaloso ea li-index tse joalo, u ka qala ka ho tloha tafoleng pg_catalog.
Li-index tse sa sebelisoeng
Ha lits'ebetso tse sebelisang li-database li ntse li fetoha, ho joalo le ka lipotso tseo ba li sebelisang. Li-index tse kentsoeng pejana li kanna tsa se hlole li sebelisoa ke potso efe kapa efe. Nako le nako ha index e hlahlojoa, e tšoauoa ke mookameli oa lipalo-palo, le ponong ea lethathamo la sistimi pg_stat_user_indexes u ka bona bohlokoa idx_scan, e leng khaontara e bokellaneng. Ho latela boleng bona ka nako e itseng (ha re re ka khoeli) ho tla fana ka mohopolo o motle oa hore na ke li-index life tse sa sebelisoeng mme li ka lahloa.
Mona ke potso ea ho fumana lipalo tsa hajoale tsa li-index tsohle ho 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)Ho aha li-index hape ka liloko tse fokolang
Hangata li-index li hloka ho tsosolosoa, ka mohlala ha li e-ba le malinyane, 'me ho tsosolosa ho ka potlakisa ho hlahloba. Hape li-index li ka senyeha. Ho fetola li-parameter tsa index ho ka 'na ha hloka hore e tsosolosoe.
Lumella ho etsa index ea parallel
Ho PostgreSQL 11, ho theha index ea B-Tree hoa tšoana. Ho potlakisa ts'ebetso ea pōpo, basebetsi ba 'maloa ba tšoanang ba ka sebelisoa. Leha ho le joalo, etsa bonnete ba hore likhetho tsena tsa tlhophiso li hlophisitsoe hantle:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Litekanyetso tsa kamehla li nyane haholo. Ka nepo, lipalo tsena li lokela ho eketseha hammoho le palo ea li-processor cores. Bala haholoanyane ho .
Tlhahiso ea index ea bokamorao
O ka etsa index ka morao o sebelisa khetho CONCURRENTLY litaelo CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXMokhoa ona oa ho theha index o fapane le o tloaelehileng ka hore ha o hloke senotlolo tafoleng, ka hona ha o thibele ts'ebetso ea ho ngola. Ka lehlakoreng le leng, ho nka nako e eketsehileng 'me ho ja lisebelisoa tse ngata.
Postgres e fana ka phetoho e ngata ea ho theha li-index le litsela tsa ho rarolla linyeoe leha e le life tse khethehileng, hammoho le litsela tsa ho laola database haeba kopo ea hau e ntse e hōla ka ho phatloha. Re tšepa hore malebela ana a tla u thusa ho fumana lipotso tsa hau kapele le hore database ea hau e itokisetse ho eketseha.
Source: www.habr.com