Боби 12. Индексҳои PostgreSQL

Дар боби 8 бо индекси B-tree шинос шудед ва дидед, ки он ҷустуҷӯро дар ҷадвали калон даҳҳо маротиба тез мекунад. Дар ин боб мефаҳмем, ки индекс чӣ гуна кор мекунад, бо кадом нарх меояд ва PostgreSQL ғайр аз B-tree боз кадом навъҳои индексро пешниҳод мекунад.

Индекс чист?

Дар PostgreSQL индекс сохтори додаҳоест, ки гирифтани маълумотро тез мекунад. Он ба PostgreSQL имкон медиҳад сатрҳои лозимаро бе аз назар гузаронидани тамоми ҷадвал пайдо кунад.

Индексро ба нишондиҳандаи охири китоб монанд кардан мумкин аст. Барои ёфтани мавзуъ ҳамаи саҳифаҳоро намехонед: ба нишондиҳанда нигоҳ мекунед ва бевосита ба саҳифаи лозима мегузаред. PostgreSQL низ индексҳоро ҳамин тавр истифода мебарад.

Фарз мекунем ҷадвале бо номи contacts доред:

CREATE TABLE contacts (  
    id INT PRIMARY KEY,  
    name VARCHAR(255) NOT NULL,  
    phone VARCHAR(10) NOT NULL  
);  

Барои ёфтани шахс аз рӯйи ном чунин дархост иҷро мекунед:

SELECT *  
FROM contacts  
WHERE name = 'John Doe';  

Бе индекси мувофиқ барои name, PostgreSQL бояд тамоми ҷадвали contacts-ро аз назар гузаронад. Агар ҷадвал сатрҳои зиёд дошта бошад, ҷустуҷӯ суст мешавад — мисли хондани тамоми китоб барои ёфтани як ҷумла.

Аммо бо индекс дар сутуни name, PostgreSQL метавонад сатрҳои мувофиқро хеле зудтар пайдо кунад.

Барои сохтани индекс аз фармони CREATE INDEX истифода мебарем:

CREATE INDEX contacts_name  
ON contacts (name);  

Ин фармон дар сутуни name-и ҷадвали contacts индексеро бо номи contacts_name месозад.

PostgreSQL қиматҳои сутуни name-ро хонда, сохтори индексро месозад. Агар ҷадвал калон бошад, ин кор вақт мегирад — ҳамон тавре ки сохтани нишондиҳандаи китоби ғафс вақти бештар талаб мекунад.

Ҳангоми сохтани индекс бо усули одӣ, PostgreSQL иҷрои SELECT-ро иҷозат медиҳад, вале амалиёти тағйирдиҳандаи додаҳои ҳамин ҷадвалро муваққатан бозмедорад:

INSERT  
UPDATE  
DELETE  

Агар ҳангоми сохтани индекс боздоштани ин амалиёт имконнопазир бошад, метавонед аз CREATE INDEX CONCURRENTLY истифода баред:

CREATE INDEX CONCURRENTLY contacts_name  
ON contacts (name);  

Ин усул имкон медиҳад навиштани додаҳо идома ёбад, вале одатан сохтани индекс вақти бештар ва кори иловагӣ талаб мекунад.

Ин ду фармон роҳҳои алтернативии сохтани як индександ; онҳоро бо ҳамин ном пайдарпай иҷро накунед.

Пас аз сохтани индекс, PostgreSQL метавонад онро барои дархости зерин истифода барад:

SELECT *  
FROM contacts  
WHERE name = 'John Doe';  

Доштани индекс маънои ҳатман истифода шудани онро надорад. PostgreSQL нақшаи иҷроро интихоб мекунад. Масалан, барои ҷадвали хеле хурд аз назар гузаронидани тамоми ҷадвал метавонад арзонтар бошад.

Индекс бояд ҳамеша бо додаҳои ҷадвал мувофиқ нигоҳ дошта шавад. Ҳангоми илова, тағйир ё нест кардани сатрҳо, PostgreSQL сохторҳои индекси дахлдорро низ нигоҳдорӣ мекунад.

Аз ин рӯ, индексҳо хондани додаҳоро тез мекунанд, вале барои амалиёти навиштан хароҷоти иловагӣ ва барои нигоҳдорӣ ҷой талаб мекунанд.

Ин ба нав кардани китоб монанд аст: ҳангоми тағйири муҳтаво нишондиҳандаи он низ метавонад навсозӣ талаб кунад.

Навъҳои индексҳои PostgreSQL

PostgreSQL чанд навъи индекс дорад. Ҳар кадом барои навъҳои муайяни додаҳо ва тарзҳои ҷустуҷӯ мувофиқ аст.

Навъи индексро бо калимаи калидии USING интихоб мекунанд:

CREATE INDEX index_name  
ON table_name USING method (column_name);  

Ба ҷойи method яке аз номҳои btree, hash, gin, gist, spgist ё brin навишта мешавад. Агар USING-ро нанависед, PostgreSQL индекси B-tree месозад.

Фаҳмидани фарқи онҳо барои беҳтар кардани суръати дархостҳо кумак мекунад.

Индекси B-tree

B-tree навъи пешфарзи индекс дар PostgreSQL аст. Он сохтори дарахтии мувозинатнок дошта, қиматҳоро бо тартиб нигоҳ медорад.

Барои инҳо муносиб аст:

• Ёфтани қимати дақиқ.
• Ҷустуҷӯ дар диапазон.
• Гирифтани додаҳо бо тартиби мувофиқ.

Масалан:

WHERE name = 'John Doe'  

WHERE price BETWEEN 100 AND 500  

Индекси Hash

Индекси Hash аз қиматҳои сутуни индексшуда кодҳои ҳешии 32-битӣ месозад.

Он танҳо муқоисаи баробариро дастгирӣ мекунад:

=  

Масалан:

WHERE customer_id = 125  

Барои ҷустуҷӯҳои диапазонӣ, ба монанди > ё BETWEEN, муносиб нест.

CREATE INDEX orders_customer_hash  
ON orders USING hash (customer_id);  

Дар амал B-tree баробариро низ хуб дастгирӣ мекунад, бинобар ин индекси Hash кам истифода мешавад.

Индекси GIN

GIN — Generalized Inverted Index индекси чаппашуда мебошад. Он барои қиматҳои дорои чанд ҷузъ мувофиқ аст, аз ҷумла:

• Массивҳо.
• Додаҳои JSONB.
• Ҷустуҷӯи пурраматнӣ.

GIN барои ҷузъҳои дохилии қимат сабтҳои алоҳида нигоҳ медорад. Аз ин рӯ, барои дархостҳое муфид аст, ки мавҷудияти ҷузъи муайянро месанҷанд.

Масалан: оё массив теги муайян дорад ё ҳуҷҷат калимаи муайянро дар бар мегирад?

Биёед GIN-ро бо сутуни jsonb, ки дар боби 5 бо он шинос шудед, бисанҷем. Ҷадвали products хусусиятҳои ҳар молро — ранг, андоза ва миқдор дар анбор — дар як ҳуҷҷати JSON нигоҳ медорад. Мисли боби 8, онро бо generate_series() бо 200 000 сатр пур мекунем:

CREATE TABLE products (  
    id integer PRIMARY KEY,  
    name text NOT NULL,  
    attrs jsonb NOT NULL  
);

INSERT INTO products  
SELECT n,  
       'Мол ' || n,  
       jsonb_build_object(  
           'color', (ARRAY['сурх', 'кабуд', 'сабз', 'сиёҳ'])[n % 4 + 1],  
           'size', (ARRAY['S', 'M', 'L'])[n % 3 + 1],  
           'stock', n % 50  
       )  
FROM generate_series(1, 200000) AS n;  

Функсияи jsonb_build_object() аз ҷуфтҳои калид–қимат ҳуҷҷати JSON месозад. Ду сатри аввал чунинанд:

 id | name  |                    attrs  
----+-------+---------------------------------------------  
  1 | Мол 1 | {"size": "M", "color": "кабуд", "stock": 1}  
  2 | Мол 2 | {"size": "L", "color": "сабз", "stock": 2}  

Оператори @> месанҷад, ки оё ҳуҷҷати чап ҳуҷҷати ростро дар бар мегирад. Дархости зерин ҳамаи молҳои сабзи андозаи L-ро меёбад:

SELECT count(*)  
FROM products  
WHERE attrs @> '{"color": "сабз", "size": "L"}';  

Натиҷа 16667 аст. Акнун индекси GIN месозем:

CREATE INDEX products_attrs_idx  
ON products USING gin (attrs);  

Як индекс ҳамаи калидҳо ва қиматҳои ҳуҷҷатро фаро мегирад: ҳам ранг, ҳам андоза ва ҳам ҳар калиди наве, ки баъдтар илова кунед. Бо EXPLAIN ANALYZE мебинем, ки PostgreSQL онро истифода мебарад:

Aggregate (actual time=5.975..5.975 rows=1.00 loops=1)  
  ->  Bitmap Heap Scan on products (actual time=2.720..5.710 rows=16667.00 loops=1)  
        Recheck Cond: (attrs @> '{"size": "L", "color": "сабз"}'::jsonb)  
        Heap Blocks: exact=2703  
        ->  Bitmap Index Scan on products_attrs_idx (actual time=2.568..2.568 rows=16667.00 loops=1)  
              Index Cond: (attrs @> '{"size": "L", "color": "сабз"}'::jsonb)  

Индекси B-tree дар сутуни attrs барои чунин дархост кумак намекард: B-tree тамоми ҳуҷҷатро ҳамчун як қимат муқоиса мекунад, на ҷузъҳои онро.

Индекси GiST

GiST — Generalized Search Tree чаҳорчӯбаи чандир барои индексгузории навъҳои гуногуни додаҳост, аз ҷумла додаҳои геометрӣ ва пурраматнӣ.

Вобаста ба навъи дода ва операторҳои дастгиришаванда, он метавонад чунин ҷустуҷӯҳоро имкон диҳад:

• Ёфтани объекти наздиктарин.
• Санҷидани ҳампӯшӣ ё буриши минтақаҳо.
• Ҷустуҷӯҳои махсуси мутобиқат.

Масалан, он барои ёфтани нуқтаи ҷуғрофии наздиктарин муфид буда метавонад.

Индекси SP-GiST

SP-GiST — Space-Partitioned GiST барои сохторҳои махсусе муносиб аст, ки фазои ҷустуҷӯро ба қисмҳо ҷудо мекунанд.

Он фазоро ба минтақаҳои ба ҳам напӯшанда тақсим мекунад ва барои баъзе додаҳои нуқтавӣ, сохторҳои дарахтмонанд ва ҷустуҷӯҳои пешвандӣ самаранок буда метавонад.

Индекси BRIN

BRIN — Block Range Index барои ҷадвалҳои хеле калон тарҳрезӣ шудааст ва ҷойи кам мегирад.

Он ба ҷойи сабти алоҳида барои ҳар сатр, ҷадвалро ба диапазонҳои саҳифаҳои нигоҳдорӣ ҷудо карда, барои ҳар диапазон маълумоти ҷамъбастӣ нигоҳ медорад. Масалан, қиматҳои хурдтарин ва калонтаринро.

BRIN махсусан вақте муфид аст, ки қиматҳои сутун бо тартиби ҷисмонии ҷойгиршавии сатрҳо робитаи қавӣ доранд.

Масалан, дар ҷадвали бузурги рӯйдодҳо сабтҳои нав одатан бо вақтҳои навтар илова мешаванд. BRIN метавонад барои ҷустуҷӯ аз рӯйи диапазони вақт қисмҳои нолозими ҷадвалро зуд хориҷ кунад.

Инро бо ҷадвали як миллион рӯйдод бисанҷем, ки ҳар дақиқа як рӯйдод сабт шудааст:

CREATE TABLE events (  
    id bigint GENERATED ALWAYS AS IDENTITY,  
    created_at timestamp NOT NULL,  
    message text  
);

INSERT INTO events (created_at, message)  
SELECT timestamp '2024-01-01' + n * interval '1 minute',  
       'рӯйдоди ' || n  
FROM generate_series(1, 1000000) AS n;

CREATE INDEX events_created_brin  
ON events USING brin (created_at);  

Барои муқоиса дар ҳамин сутун индекси B-tree низ сохтем ва андозаҳоро бо функсияи pg_relation_size() чен кардем:

Индекси BRIN қариб ҳазор маротиба хурдтар аст. Бо вуҷуди ин, вай дархостро барои рӯйдодҳои як рӯз хеле тез мекунад:

SELECT count(*)  
FROM events  
WHERE created_at >= timestamp '2024-03-01'  
  AND created_at <  timestamp '2024-03-02';  

Натиҷа 1440 аст — ҳамон қадар дақиқа, ки дар як шабонарӯз ҳаст. Ба шарт диққат диҳед: агар BETWEEN '2024-03-01' AND '2024-03-02' менавиштем, натиҷа 1441 мешуд, зеро BETWEEN ҳар ду сарҳадро дохил мекунад ва рӯйдоди соати 00:00-и 2 март низ ба он медарояд. Барои давраҳои вақт шакли «>= аввал ва < охир»-ро истифода баред.

Дар компютери мо ин дархост бо BRIN тақрибан дар 0,6 миллисония иҷро шуд. Ин натиҷаи як санҷиш аст: вақт ба сахтафзор, ба он ки саҳифаҳои ҷадвал дар хотираи кэш ҳастанд ё не, ва ба нақшаи интихобкардаи PostgreSQL вобаста аст.

Дар нақшаи иҷро сатри Heap Blocks: lossy=128 пайдо мешавад: BRIN ҷойи сатрҳои мушаххасро намедонад, балки танҳо блокҳои саҳифаҳоеро нишон медиҳад, ки сатрҳои лозимӣ метавонанд дар онҳо бошанд. PostgreSQL ин блокҳоро хонда, сатрҳои нолозимро полоиш мекунад. Агар рӯйдодҳо бо тартиби тасодуфии вақт илова мешуданд, ҳар блок тамоми диапазони вақтро фаро мегирифт ва BRIN бефоида мешуд.

Интихоби навъи индекс

Хулоса

Индекс мисли нишондиҳандаи китоб аст: хонданро тез мекунад, вале ҷой мегирад ва ҳангоми ҳар тағйири додаҳо бояд нав карда шавад. Аз ин рӯ, индексро барои сутунҳое созед, ки дар WHERE, JOIN ва ORDER BY воқеан истифода мешаванд, ва бо EXPLAIN ANALYZE санҷед, ки PostgreSQL онро истифода мебарад.

B-tree барои аксари ҳолатҳо кофист. Навъҳои дигар барои вазифаҳои махсус пешбинӣ шудаанд: GIN барои jsonb ва массивҳо, GiST ва SP-GiST барои додаҳои геометрӣ ва дарахтӣ, BRIN барои ҷадвалҳои бузурги таърихӣ.

Бо ин боб китоб ба охир мерасад. Шумо аз мафҳуми пойгоҳи додаҳои релятсионӣ оғоз карда, PostgreSQL-ро ба кор даровардед, ҷадвалҳо сохтед, дархост навиштед, ҷадвалҳоро пайваст кардед, додаҳоро ҷамъбаст намудед ва бо транзаксияҳову индексҳо шинос шудед. Беҳтарин роҳи идома — маҷмӯи додаҳои ба худ ҷолибро гирифта, ба он савол додан аст.

Худатон бисанҷед

1. Дар ҷадвали libraries_2024 барои сутуни region индекс созед ва нақшаи дархости SELECT * FROM libraries_2024 WHERE region = 'Шимол';-ро бо EXPLAIN бинед. Оё PostgreSQL индексро истифода мебарад? Чаро?
2. Ҷадвали posts-ро бо сутуни tags text[] созед, чанд сатр ворид кунед ва индексе созед, ки ҷустуҷӯи навиштаҳоро аз рӯйи тег тез мекунад. Навиштаҳои дорои теги postgres-ро ёбед.
3. Дар ҷадвали addresses-и боби 8 барои дархости WHERE house_number = 42 индекс вуҷуд дорад? Бо EXPLAIN санҷед.

Ҷавобҳо дар замима, бахши «Ҷавобҳо ба машқҳо», оварда шудаанд.