Postgres աշխարհում ինդեքսները կարևոր են տվյալների բազայի պահեստում արդյունավետ նավարկելու համար (կոչվում է «կույտ»): Postgres-ը չի աջակցում դրա համար կլաստերավորումը, և MVCC ճարտարապետությունը հանգեցնում է նրան, որ դուք հայտնվում եք նույն tuple-ի բազմաթիվ տարբերակների հետ: Հետևաբար, շատ կարևոր է, որ կարողանանք ստեղծել և պահպանել արդյունավետ ինդեքսներ՝ հավելվածներին աջակցելու համար:
Ահա որոշ խորհուրդներ՝ ինդեքսների օգտագործումը օպտիմալացնելու և բարելավելու համար:
Նշում. ստորև ներկայացված հարցումներն աշխատում են չփոփոխվածի վրա .
Օգտագործելով ծածկող ինդեքսներ
Եկեք նայենք ոչ ակտիվ օգտվողների համար էլփոստի հասցեներ հանելու խնդրանքին: Աղյուսակ customer կա սյունակ active, և հարցումը պարզ է.
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)
Հարցումը կանչում է աղյուսակի ամբողջական սկանավորման հաջորդականությունը customer. Եկեք ստեղծենք ինդեքս սյունակի վրա 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)
Դա օգնեց, հետագա սկանավորումը վերածվեց «index scan«. Սա նշանակում է, որ Postgres-ը սկանավորելու է ինդեքսը:idx_cust1«, այնուհետև շարունակեք որոնել աղյուսակի կույտը` կարդալու այլ սյունակների արժեքները (այս դեպքում՝ սյունակը email) որը պետք է հարցմանը:
Ծածկող ինդեքսները ներկայացված են PostgreSQL 11-ում: Նրանք թույլ են տալիս ներառել մեկ կամ մի քանի լրացուցիչ սյունակներ հենց ինդեքսի մեջ. դրանց արժեքները պահվում են ինդեքսի տվյալների պահեստում:
Եթե մենք օգտվեինք այս հատկությունից և ավելացնեինք էլփոստի արժեքը ինդեքսի ներսում, ապա Postgres-ը կարիք չէր ունենա որոնել աղյուսակի կույտը արժեքի համար: email. Տեսնենք, արդյոք սա կաշխատի.
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մեզ ասում է, որ հարցումն այժմ միայն ինդեքսի կարիք ունի, որն օգնում է խուսափել բոլոր սկավառակի I/O-ից՝ աղյուսակի կույտը կարդալու համար:
Ծածկման ինդեքսները ներկայումս հասանելի են միայն B-ծառերի համար: Այնուամենայնիվ, այս դեպքում պահպանման ջանքերն ավելի մեծ կլինեն:
Օգտագործելով մասնակի ինդեքսներ
Մասնակի ինդեքսները ինդեքսավորում են աղյուսակի տողերի միայն ենթաբազմությունը: Սա խնայում է ինդեքսների չափը և սկանավորումն ավելի արագ է դարձնում:
Ենթադրենք, մենք ցանկանում ենք ստանալ Կալիֆորնիայում մեր հաճախորդների էլ.փոստի հասցեների ցուցակը: Հարցումը կլինի հետևյալը.
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)Ի՞նչ կտա մեզ սովորական ինդեքսները.
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)
Ստուգել address փոխարինվել է ինդեքսի սկանավորումով idx_address1իսկ հետո սկանավորեց կույտը address.
Քանի որ սա հաճախակի հարցում է և պետք է օպտիմալացվի, մենք կարող ենք օգտագործել մասնակի ինդեքս, որը ինդեքսավորում է միայն այն տողերը հասցեներով, որոնցում գտնվում են թաղամասը ‘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)
Այժմ հարցումը միայն կարդում է idx_address2 և սեղանին չի դիպչում address.
Օգտագործելով բազմարժեք ինդեքսներ
Որոշ սյունակներ, որոնք պետք է ինդեքսավորվեն, կարող են չպարունակել տվյալների սկալային տեսակ: Սյունակների տեսակները, ինչպիսիք են jsonb, arrays и tsvector պարունակում է կոմպոզիտային կամ բազմակի արժեքներ: Եթե Ձեզ անհրաժեշտ է ինդեքսավորել նման սյունակները, ապա սովորաբար պետք է որոնել այդ սյունակների բոլոր առանձին արժեքները:
Փորձենք գտնել բոլոր ֆիլմերի վերնագրերը, որոնք պարունակում են կտրվածքներ անհաջող նկարներից: Աղյուսակ film կա տեքստային սյունակ, որը կոչվում է special_features. Եթե ֆիլմն ունի այս «հատուկ հատկությունը», ապա սյունակը պարունակում է տարրը որպես տեքստային զանգված Behind The Scenes. Բոլոր նման ֆիլմերը որոնելու համար մենք պետք է ընտրենք «Behind The Scenes»-ով բոլոր տողերը, երբ ցանկացած զանգվածի արժեքները special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Բնադրող օպերատոր @> ստուգում է, թե արդյոք աջ կողմը ձախ կողմի ենթաբազմություն է:
Հարցման պլան.
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)Որը պահանջում է ամբողջական կույտային սկանավորում 67 արժողությամբ:
Եկեք տեսնենք, թե արդյոք սովորական B-tree ինդեքսն օգնում է մեզ.
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)Ցուցանիշն անգամ չի դիտարկվել։ B-tree ինդեքսը տեղյակ չէ ինդեքսավորված արժեքներում առանձին տարրերի առկայության մասին:
Մեզ անհրաժեշտ է 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)GIN ինդեքսն աջակցում է առանձին արժեքների քարտեզագրմանը ինդեքսավորված կոմպոզիտային արժեքների դեմ, ինչը հանգեցնում է հարցման պլանի արժեքի, որն ավելի քան կիսով չափ կրճատվում է:
Ազատվել կրկնօրինակ ինդեքսներից
Ժամանակի ընթացքում ինդեքսները կուտակվում են, և երբեմն նոր ինդեքսը կարող է պարունակել նույն սահմանումը, ինչ նախորդներից մեկը: Դուք կարող եք օգտագործել կատալոգի տեսքը՝ ինդեքսների համար ընթեռնելի SQL սահմանումներ ստանալու համար: pg_indexes. Դուք կարող եք նաև հեշտությամբ գտնել նույնական սահմանումներ.
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 ինդեքսներ
Կարող է պատահել, որ դուք հայտնվեք բազմաթիվ ինդեքսներով, որոնցից մեկը ինդեքսավորում է սյունակների գերբազմություն, որոնք ինդեքսավորում են այլ ինդեքսներ: Սա կարող է ցանկալի լինել, կամ ոչ. սուպերհավաքույթը կարող է հանգեցնել միայն ինդեքսների սկանավորման, ինչը լավ է, բայց այն կարող է չափազանց շատ տարածք զբաղեցնել, կամ հարցումը, որը գերբազմաթիվը նախատեսված էր օպտիմալացնելու համար, այլևս չի օգտագործվում:
Եթե Ձեզ անհրաժեշտ է ավտոմատացնել նման ինդեքսների սահմանումը, կարող եք սկսել սեղանից pg_catalog.
Չօգտագործված ինդեքսներ
Տվյալների շտեմարաններ օգտագործող հավելվածների զարգանալուն զուգահեռ զարգանում են նաև նրանց կողմից օգտագործվող հարցումները: Ավելի վաղ ավելացված ինդեքսներն այլևս չեն կարող օգտագործվել որևէ հարցման կողմից: Ամեն անգամ, երբ ինդեքսը սկանավորվում է, այն նշվում է վիճակագրության կառավարչի կողմից և համակարգի կատալոգի տեսքով pg_stat_user_indexes դուք կարող եք տեսնել արժեքը idx_scan, որը կուտակային հաշվիչ է։ Այս արժեքին որոշակի ժամանակահատվածում (ասենք մեկ ամսվա) հետևելը լավ պատկերացում կտա, թե որ ինդեքսները չեն օգտագործվում և կարող են հանվել:
Ահա մի հարցում՝ սխեմայի բոլոր ինդեքսների ընթացիկ սկանավորման թվերը ստանալու համար ‘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)Ավելի քիչ կողպեքներով ինդեքսների վերակառուցում
Ինդեքսները հաճախ վերակառուցման կարիք ունեն, օրինակ, երբ դրանք փքվում են, և վերակառուցումը կարող է արագացնել սկանավորումը: Նաև ինդեքսները կարող են կոռումպացված լինել: Ցուցանիշի պարամետրերը փոխելը կարող է նաև պահանջել այն վերակառուցել:
Միացնել զուգահեռ ինդեքսի ստեղծումը
PostgreSQL 11-ում B-Tree ինդեքսի ստեղծումը միաժամանակ է: Ստեղծման գործընթացը արագացնելու համար կարող են օգտագործվել մի քանի զուգահեռ աշխատողներ: Այնուամենայնիվ, համոզվեք, որ այս կազմաձևման ընտրանքները ճիշտ են դրված.
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Նախնական արժեքները չափազանց փոքր են: Իդեալում, այս թվերը պետք է ավելանան պրոցեսորային միջուկների քանակին զուգահեռ: Կարդալ ավելին .
Ֆոնային ինդեքսի ստեղծում
Դուք կարող եք ինդեքս ստեղծել հետին պլանում՝ օգտագործելով տարբերակը CONCURRENTLY թիմերը CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXԱյս ինդեքսի ստեղծման ընթացակարգը տարբերվում է սովորականից նրանով, որ այն չի պահանջում կողպեք սեղանի վրա և, հետևաբար, չի արգելափակում գրելու գործողությունները: Մյուս կողմից, դա ավելի շատ ժամանակ է պահանջում և ավելի շատ ռեսուրսներ է սպառում:
Postgres-ը մեծ ճկունություն է տալիս ինդեքսների և հատուկ դեպքերի լուծման ուղիների ստեղծման համար, ինչպես նաև տվյալների բազան կառավարելու եղանակներ, եթե ձեր հավելվածը պայթել է: Հուսով ենք, որ այս խորհուրդները կօգնեն ձեզ արագորեն ստանալ ձեր հարցումները և ձեր տվյալների բազան պատրաստ լինել մասշտաբային:
Source: www.habr.com