Дар ҷаҳони Postgres, индексҳо барои паймоиши муассири анбори пойгоҳи додаҳо муҳиманд (бо номи "теппа"). Postgres кластеризатсияро барои он дастгирӣ намекунад ва меъмории MVCC шуморо водор мекунад, ки версияҳои зиёди ҳамон як наворро анҷом диҳед. Аз ин рӯ, қобилияти эҷод ва нигоҳ доштани индексҳои муассир барои дастгирии барномаҳо хеле муҳим аст.
Инҳоянд чанд маслиҳат оид ба оптимизатсия ва беҳтар кардани истифодаи индексҳо.
Эзоҳ: дархостҳое, ки дар зер нишон дода шудаанд, дар шакли бетағйир кор мекунанд .
Истифодаи индексҳои фарогирӣ
Биёед ба дархости истихроҷи суроғаҳои почтаи электронӣ барои корбарони ғайрифаъол назар кунем. Ҷадвал 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' ба мо мегӯяд, ки дархост ҳоло танҳо ба индекс ниёз дорад, ки барои пешгирӣ кардани ҳама дискҳои воридотӣ барои хондани теппаи ҷадвал кӯмак мекунад.
Индексҳои пӯшиш ҳоло танҳо барои дарахтони 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. Барои ҷустуҷӯи ҳамаи чунин филмҳо, мо бояд ҳамаи сатрҳоро бо "Дар паси парда" интихоб кунем, вақте ки ҳама арзишҳои массив 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 ба мо кӯмак мекунад:
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 аз мавҷудияти унсурҳои алоҳида дар арзишҳои индексатсияшуда огоҳ нест.
Ба мо индекси 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 чандирии зиёдеро барои эҷоди индексҳо ва роҳҳои ҳалли ҳама гуна ҳолатҳои махсус, инчунин роҳҳои идоракунии пойгоҳи додаҳо дар сурати ба таври муназзам афзоиш ёфтани барномаи шумо фароҳам меорад. Умедворем, ки ин маслиҳатҳо ба шумо кӯмак мекунанд, ки дархостҳои худро зуд ба даст оред ва пойгоҳи додаҳои шуморо барои васеъшавӣ омода созед.
Манбаъ: will.com