Massivlərin indeksləşdirilməsi: GIN və BTREE indekslərinin yaradılması

Təsəvvür elə, sənin milyonlarla sətr olan bir cədvəlin var və sütunlardan biri massiv saxlayır. Məsələn, products adlı bir cədvəlimiz var və hər məhsul bir neçə kateqoriyaya aid ola bilər:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Məhsulun kateqoriyalarını saxlamaq üçün sətir massividir
);

Deyək ki, sən electronics kateqoriyasına aid bütün məhsulları tapmaq istəyirsən. Sadəcə @> operatorundan istifadə etsən, bütün cədvəli tam skan etməli olacaqsan:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Full scan (Seq Scan) — yavaşdır. Xüsusilə cədvəl böyükdürsə. İndekslər köməyə gəlir və bu işi daha sürətli edir.

Massivlər üçün indeks tipləri

PostgreSQL massivlər üçün iki əsas indeks tipini dəstəkləyir:

1. GIN (Generalized Inverted Index) — massivdə elementləri tez tapmaq və ya kəsişmələri yoxlamaq üçün idealdır.
2. BTREE (Binary Tree) — massivlərin tam bərabərliyini və ya sıralı müqayisəsini etmək üçün uyğundur.

Gəlin hər birinə bir az daha yaxından baxaq.

1. GIN indeksi: sürət şahanəsi

GIN (Generalized Inverted Index) — bu indeks aşağıdakı operatorlarla işləmək üçün superdir:

• @> (massiv element və ya başqa massiv saxlayır),
• <@ (massiv başqa massivə daxildir),
• && (massivlər kəsişir).

categories sütunu üçün GIN indeksi belə yaradılır:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

İndeks yaradandan sonra sorğuların icrası hiss olunacaq dərəcədə sürətlənəcək. Məsələn, bu sorğu:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

artıq sənin GIN indeksindən istifadə edəcək.

Maraqlı fakt: GIN indeksi əslində inverted list prinsipi ilə işləyir — hansı elementin (məsələn, sətir) hansı sətrlərdə olduğunu saxlayır. Kitablardakı mövzu göstəricisi (index) kimi, səhifə nömrəsinə görə tapırsan. Rahatdır, düzdür?

2. BTREE indeksi: sıralama önəmlidirsə

BTREE (Binary Tree) — bu, əksər verilənlər bazasında standart indeksdir. Massivlərin tam bərabərliyini və ya sıralı müqayisəsini etmək üçün uyğundur, məsələn:

• Massivin bərabərliyini yoxlamaq =,
• Massivləri elementlərin sırasına görə müqayisə etmək (>, <).

Massiv üçün BTREE indeksi belə yaradılır:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

BTREE indeksindən istifadə edə biləcək sorğu nümunəsi:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Amma yadında saxla ki, BTREE indeksləri @> və ya <@ kimi operatorlar üçün uyğun deyil. Onlar üçün GIN daha yaxşıdır.

İndekslərdən istifadə nümunələri

İndi isə nəzəriyyəni praktikaya çevirək və bir neçə nümunəyə baxaq.

1. Massivlərin kəsişməsini tapmaq

Deyək ki, electronics və smartphones kateqoriyaları ilə bağlı bütün məhsulları tapmaq istəyirsən, && operatorundan istifadə edərək (massivlərin kəsişməsi):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Bunun üçün ən yaxşısı, artıq yaratdığın GIN indeksidir:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Belə indekslə sorğu çox daha sürətli işləyəcək, çünki inverted list istifadə olunur.

2. Massivlərin bərabərliyini yoxlamaq

Əgər sənə yalnız electronics və gadgets (bu ardıcıllıqla) kateqoriyalarına aid məhsulları tapmaq lazımdırsa, burada BTREE indeksi daha uyğundur:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Müvafiq indeksi yarat:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

İndekslərin performansı

İndekslər sorğuları sürətləndirir, amma bunun da öz bədəli var. Məsələn:

• İndeksin yaradılması vaxt və resurs tələb edir. Əgər cədvəlin çox böyükdürsə, indeks qurmaq bir az uzun çəkə bilər.
• Cədvəlin yenilənməsi. Hər dəfə yeni sətr əlavə edəndə və ya mövcud məlumatı dəyişəndə, indekslər də yenilənir. Bu isə INSERT və UPDATE əməliyyatlarını bir az yavaşlada bilər.

Amma çox vaxt, sorğuların sürətli işləməsi bu xərcləri tamamilə kompensasiya edir.

Necə seçək: GIN yoxsa BTREE?

Budur, hansı tapşırıq üçün hansı indeksin daha uyğun olduğunu göstərən kiçik bir cədvəl: