Na ụwa Postgres, indexes dị mkpa maka ịnyagharị nchekwa data nke ọma (nke a na-akpọ "obo"). Postgres anaghị akwado nchịkọta maka ya, na MVCC architecture na-eme ka ị nweta ọtụtụ nsụgharị nke otu tuple. Ya mere, ọ dị ezigbo mkpa inwe ike ịmepụta na ịnọgide na-enwe indexes dị mma iji kwado ngwa.
Nke a bụ ụfọdụ ndụmọdụ maka ịkwalite na melite ojiji nke ndeksi.
Mara: ajụjụ ndị egosiri n'okpuru na-arụ ọrụ na emezigharịghị .
Iji Ndekọ Mkpuchi
Ka anyị lelee arịrịọ iwepụta adreesị ozi-e maka ndị ọrụ anaghị arụ ọrụ. Tebụl customer enwere kọlụm active, na ajụjụ dị mfe:
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)
Ajụjụ a na-akpọ usoro nyocha tebụl zuru ezu customer. Ka anyị mepụta index na kọlụm 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)
Ọ nyere aka, nyocha nke sochirinụ ghọrọ "index scan". Nke a pụtara na Postgres ga-enyocha index "idx_cust1", wee gaa n'ihu na-enyocha ikpo tebụl iji gụọ ụkpụrụ nke kọlụm ndị ọzọ (na nke a, kọlụm. email) na ajụjụ a chọrọ.
Ewebata ndenye mkpuchi mkpuchi na PostgreSQL 11. Ha na-enye gị ohere itinye otu ma ọ bụ karịa ogidi ndị ọzọ na index n'onwe ya - a na-echekwa ụkpụrụ ha na ụlọ ahịa data index.
Ọ bụrụ na anyị ejiri njirimara a wee gbakwunye uru email dị n'ime ndeksi, mgbe ahụ Postgres agaghị achọ ịchọta okpokoro okpokoro maka uru ahụ. email. Ka anyị hụ ma nke a ga-arụ ọrụ:
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' na-agwa anyị na ajụjụ ugbu a chọrọ naanị ndeksi, nke na-enyere aka zere diski I/O niile iji gụọ ikpo tebụl.
Ndekọ mkpuchi mkpuchi dị ugbu a naanị maka osisi B. Otú ọ dị, na nke a, mgbalị mmezi ga-adị elu.
N'iji Index akụkụ
Index ndepụta akụkụ akụkụ naanị obere nke ahịrị ndị dị na tebụl. Nke a na-echekwa nha indexes wee mee nyocha ngwa ngwa.
Ka anyị kwuo na anyị chọrọ ịnweta ndepụta adreesị email ndị ahịa anyị na California. Arịrịọ a ga-adị ka nke a:
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)Kedu indexes nkịtị ga-enye anyị:
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)
Nyocha address ejirila nyocha index dochie ya idx_address1wee nyochaa obo ahụ address.
Ebe ọ bụ na nke a bụ ajụjụ a na-ajụkarị, ọ dịkwa mkpa ka emeziwanye ya, anyị nwere ike iji ndepụta ntụaka akụkụ, nke na-egosi naanị ahịrị ndị ahụ nwere adreesị nke mpaghara ahụ. ‘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)
Ugbu a ajụjụ a na-agụ naanị idx_address2 ma ghara imetụ tebụl ahụ aka address.
Iji Multi-Uru Index
Ụfọdụ ogidi ndị a ga-edepụta nwere ike ọ gaghị enwe ụdị data scalar. Ụdị kọlụm dị ka jsonb, arrays и tsvector nwere ụkpụrụ agwakọtara ma ọ bụ ọtụtụ. Ọ bụrụ na ịchọrọ ịdepụta ogidi ndị dị otú ahụ, ị ga-achọkarị ụkpụrụ niile dị na kọlụm ndị ahụ.
Ka anyị gbalịa ịchọta aha fim niile nwere mbelata site na ihe nkiri na-agaghị aga nke ọma. Tebụl film enwere ogidi ederede a na-akpọ special_features. Ọ bụrụ na ihe nkiri ahụ nwere "ihe onwunwe pụrụ iche" a, mgbe ahụ, kọlụm nwere mmewere dị ka nhazi ederede Behind The Scenes. Iji chọọ ihe nkiri niile dị otú ahụ, anyị kwesịrị ịhọrọ ahịrị niile nwere "Behind The Scenes" mgbe ọ bụla n'usoro ụkpụrụ special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Onye ọrụ nesting @> na-enyocha ma akụkụ aka nri bụ obere akụkụ nke akụkụ aka ekpe.
Amụma arịrịọ:
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)Nke na-arịọ nyocha ikpo okwu zuru oke yana ọnụ ahịa 67.
Ka anyị hụ ma ndepụta B-osisi mgbe niile na-enyere anyị aka:
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)E cheghịdị ndepụta ndeksi ahụ. Ndekọ B-osisi amaghị maka ịdị adị nke ihe ndị dị n'otu n'otu na ụkpụrụ indexed.
Anyị chọrọ ndeksi 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)Ndekọ GIN na-akwado nkewa otu ụkpụrụ megide ụkpụrụ agwakọtara, na-ebute ọnụ ahịa atụmatụ ajụjụ karịrị ọkara.
Na-ekpochapụ ndetu oyiri
Ndekọ ndeksi na-agbakọta ka oge na-aga, ma mgbe ụfọdụ ndepụta ndeksi ọhụrụ nwere ike ịnwe otu nkọwa dị ka otu n'ime ndị gara aga. Ị nwere ike iji nlele katalọgụ nweta nkọwa SQL nke mmadụ nwere ike ịgụ nke ndeksi. pg_indexes. Ị nwekwara ike ịchọta nkọwa ndị yiri ya:
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)
Ndepụta nke Superset
Ọ nwere ike ime na ị ga-eji ọtụtụ index, otu n'ime ha na-egosi otu nnukwu kọlụm ndị na-egosi ndektị ndị ọzọ. Nke a nwere ike ma ọ bụ na ọ gaghị abụ ihe a na-achọsi ike - superset nwere ike ịkpata nyocha naanị index, nke dị mma, mana ọ nwere ike were ohere buru ibu, ma ọ bụ a naghịzi eji ajụjụ nke superset bu n'obi kwalite.
Ọ bụrụ na ịchọrọ ịmegharị nkọwapụta nke index dị otú ahụ, ị nwere ike ibido ya site na tebụl pg_catalog.
Ndekọ ejighị ya
Ka ngwa ndị na-eji ọdụ data na-etolite, otu a ka ajụjụ ndị ha na-eji na-adị. Ajụjụ ọ bụla enwekwaghị ike iji ndeksi agbakwunyere na mbụ. Oge ọ bụla enyochara index, onye njikwa ọnụ ọgụgụ na-egosi ya, yana n'elele katalọgụ sistemụ. pg_stat_user_indexes ị nwere ike ịhụ uru idx_scan, nke bụ ngụkọta ọnụ ọgụgụ. Isochi uru a n'ime oge (kwuo otu ọnwa) ga-enye echiche dị mma nke index nke anaghị eji ma nwee ike ịdaba.
Nke a bụ ajụjụ iji nweta ọnụọgụ nyocha nke ugbu a nke index niile dị na 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)Nrụgharị index na obere mkpọchi
Index na-adịkarị mkpa ka e wughachi ya, dịka ọmụmaatụ mgbe ha na-afụ ụfụ, na iwughachi ya nwere ike ime ka nyocha ahụ dị ngwa. Ọzọkwa index nwere ike mebie. Ịgbanwe paramita index nwekwara ike ịchọ iwughachi ya.
Kwado imepụta ndeksi myirịta
Na PostgreSQL 11, imepụta ndeksi B-Osisi na-aga n'otu oge. Iji mee ka usoro okike dị ngwa, enwere ike iji ọtụtụ ndị ọrụ yiri ya. Agbanyeghị, hụ na edobere nhọrọ nhazi ndị a nke ọma:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Ụkpụrụ ndabara dị obere. Dị ka o kwesịrị, ọnụọgụ ndị a kwesịrị ịbawanye yana ọnụ ọgụgụ nke cores processor. Gụkwuo n'ime .
Okike ndabere ndabere
Ị nwere ike ịmepụta index n'azụ site na iji nhọrọ CONCURRENTLY otu CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXUsoro okike index a dị iche na nke a na-emebu na ọ dịghị achọ mkpọchi na tebụl, ya mere ọ dịghị egbochi ọrụ ide. N'aka nke ọzọ, ọ na-ewe oge ma na-erikwu ihe onwunwe.
Postgres na-enye ọtụtụ mgbanwe maka ịmepụta indexes na ụzọ isi dozie okwu ọ bụla pụrụ iche, yana ụzọ isi jikwaa nchekwa data ma ọ bụrụ na ngwa gị na-agbawa agbawa. Anyị na-atụ anya ndụmọdụ ndị a ga-enyere gị aka nweta ajụjụ gị ngwa ngwa yana nchekwa data gị dị njikere maka ọnụ ọgụgụ.
isi: www.habr.com