Massivlərin indeksasiyası və operatorlar (`@>`, `<@`, `&&`) ilə sürətli axtarış

PostgreSQL-də massivlər bir cədvəlin bir hüceyrəsində bir neçə dəyəri saxlamağa imkan verir. Bu, məsələn, məqalə üçün tag-ların siyahısı və ya məhsulun kateqoriyaları kimi əlaqəli məlumatları qruplaşdırmaq lazım olanda çox rahatdır. Amma massivlərdə axtarış, filtrasiya və ya kəsişmə etməyə başlayanda performans ciddi şəkildə düşə bilər. Ona görə də massivlərin indeksasiyası əsl xilaskar olur. İndekslər aşağıdakı əməliyyatları sürətləndirməyə imkan verir:

• massivin müəyyən elementi saxlayıb-saxlamadığını yoxlamaq,
• verilmiş elementləri saxlayan massivləri tapmaq,
• massivlərin kəsişməsini yoxlamaq.

Massivlərlə işləmək üçün operatorlar

İndeks yaratmağa keçməzdən əvvəl, gəlin massivlərlə işləmək üçün əsas operatorlara baxaq:

@> (contains) — massivdə başqa massivdən olan bütün elementlərin olub-olmadığını yoxlayır.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Burada "SQL" tag-ı olan kursları axtarırıq.

<@ (is contained by) — bir massiv başqa massivdə tam olaraq var, ya yox, onu yoxlayır.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Burada kursların tag-larında ARRAY['PostgreSQL', 'SQL'] massivinin bütün elementləri olub-olmadığını yoxlayırıq.

&& (overlap) — massivlər arasında kəsişmə olub-olmadığını yoxlayır.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Bu sorğu "NoSQL" və ya "Big Data" tag-larından ən azı birini saxlayan kursları tapır.

İndeksləşmə necə kömək edir?

Təsəvvür elə ki, courses cədvəlində milyonlarla qeyd var və yuxarıdakı operatorlardan biri ilə sorğu edirsən. İndeks olmadan PostgreSQL hər sətri bir-bir yoxlamalı olacaq — bu isə çox vaxt aparır (xüsusən də, proqramçının kompilyasiya gözlədiyi qədər səbrin yoxdursa).

İndekslərlə bu xərclərdən qaçmaq olur. PostgreSQL massivlərlə işləmək üçün iki indeks növü təklif edir:

1. GIN (Generalized Inverted Index) — massivlər üçün ən yaxşı seçimdir.
2. BTREE — massivləri tam şəkildə müqayisə etmək üçün istifadə olunur.

Nümunə: Massivlər üçün indeks yaradılması

Gəlin, praktikada yoxlamaq üçün massivli kiçik bir cədvəl yaradaq.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Bəzi qeydlər əlavə edək:

INSERT INTO courses (name, tags)
VALUES
    ('SQL əsasları', ARRAY['SQL', 'PostgreSQL', 'Verilənlər bazası']),
    ('Big Data ilə iş', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Python-da proqramlaşdırma', ARRAY['Python', 'Web', 'Verilər']),
    ('PostgreSQL kursu', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Belə bir cədvəl belə görünəcək:

İndeks olmadan: yavaş axtarış

İndi təsəvvür elə ki, SQL tag-ı olan bütün kursları tapmaq istəyirik.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Bu sorğu işləyəcək, amma çoxlu məlumat olanda çox yavaş olacaq. PostgreSQL ardıcıl yoxlama (Sequential Scan) edəcək, yəni cədvəldəki hər sətri yoxlayacaq.

Sorğunun təxmini nəticəsi:

GIN indeksi yaradılması

Axtarışı sürətləndirmək üçün GIN tipli indeks yaradaq:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Eyni sorğunu yenidən icra edək:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

İndi PostgreSQL yaratdığımız GIN indeksindən istifadə edəcək və sorğu çox daha tez işləyəcək.

Əvvəl ardıcıl yoxlama (Seq Scan) istifadə olunurdusa, indi icra planında Bitmap Index Scan görəcəksiniz:

Operatorlar indekslərlə necə işləyir?

Nümunə 1: @> operatoru

Sorğu:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

GIN indeksi bu operator üçün idealdır. Postgres tez bir zamanda hansı sətrlərdə verilmiş elementin olduğunu tapır və nəticəni qaytarır.

Sorğunun nəticəsi:

Nümunə 2: && operatoru

Sorğu:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Bu operator massivlərin kəsişməsini yoxlayır: tags massivində ötürülən massivdən ən azı bir element varsa, sətri qaytarır.

GIN indeksi yenə işini görür — böyük həcmdə məlumatda belə axtarış sürətli olur.

Sorğunun nəticəsi:

İndeksləşmə və optimizasiya

Massivlərlə işləyəndə bu məsləhətlərə əməl et:

1. GIN indekslərdən massiv daxilində axtarış üçün istifadə et. Onlar ardıcıl yoxlamadan daha sürətlidir.
2. İndeksi yalnız həqiqətən tez-tez sorğularda istifadə olunan sütunlara əlavə et. İndeks yer tutur və məlumat əlavə etməyi yavaşladır, ona görə hər şeyi indeksləməyə ehtiyac yoxdur.
3. EXPLAIN və EXPLAIN ANALYZE ilə sorğuları profil et ki, indeksinin həqiqətən istifadə olunduğunu görəsən.

Nümunələr: massivlər üçün indekslərin yaradılması

Gəlin, massivlərlə konkret əməliyyatlar üçün indekslərin necə yaradıldığını və bunun praktikada niyə lazım olduğunu baxaq.

@> operatoru üçün indeks

Tutaq ki, artıq belə bir courses cədvəlin var:

@> operatoru ilə (massiv element saxlayır) sorğuları sürətləndirmək üçün GIN indeksi yaradaq:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

İndi sorğunu icra edək:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Nəticə:

@>, <@ və && operatorları üçün indeks

Əvvəlki nümunədəki cədvəl eynidir.

@>, <@ və && operatorları GIN indeksləri ilə effektiv işlədiyinə görə, bir universal indeks yaradıb bu operatorlarla olan sorğuları sürətləndirə bilərsən:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Sorğu nümunələri və nəticələri:

• @> — massivdə göstərilən elementlərin olub-olmadığını yoxlamaq:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — massiv başqa massivdə tam var, ya yox, onu yoxlamaq:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — massivlərin kəsişməsini yoxlamaq:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Bir az daha çətinini yoxlayaq

Elə bir sorğu yazaq ki, tag-larında ['Python', 'SQL', 'NoSQL'] siyahısından ən azı biri olan kursları qaytarsın:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Nəticə:

GIN indeksi ilə belə sorğu hətta milyonlarla sətr olan cədvəldə də ani işləyir.

Massivlərlə işləyərkən tipik səhvlər

İndeks istifadə olunmur: EXPLAIN çıxışında Seq Scan görürsənsə, indeksin yaradılıb-yaradılmadığını və istifadə etdiyin operatorun indeksləşməni dəstəklədiyini yoxla.

Massiv nadir hallarda istifadə olunur: massivli sütun sorğularda və ya yeniləmələrdə nadir hallarda istifadə olunursa, indeks əlavə yer tutur və fayda vermir.

Artıq indekslər: indekslər diskdə yer tutur və yazma əməliyyatlarını yavaşladır, ona görə yalnız həqiqətən lazım olan və sorğularda istifadə olunan indeksləri yarat.

İndi PostgreSQL-də massivlərlə effektiv işləmək üçün lazım olan bütün alətlər səndə var — @>, <@, && operatorları və GIN indeksləri ilə sorğuları sürətləndir. Öz məlumatlarında rahatlıqla test elə!