pg_stat_statements ilə indekslərin və filtrlərin istifadəsinin analizi

İndekslər — kitabda bookmark-lar kimi bir şeydir. Lazım olan məlumatı tez tapmağa kömək edir. Amma təsəvvür elə ki, bir sürü bookmark əlavə eləmisən, amma heç kim onlardan istifadə etmir. Və ya daha pis, pis seçilmiş bookmark-lar səni məcbur edir ki, kitabı başdan sona qədər vərəqləyəsən. Bax, bu anda indekslərin istifadəsini analiz eləmək vacib olur.

Pis yazılmış sorğular indeksləri görməzdən gəlir və nəticədə bahalı ardıcıl skanlar (Seq Scan) baş verir. Bu isə sorğuların icrasını ləngidir və serverə əlavə yük yaradır. Məqsədimiz — hansı sorğuların indekslərdən istifadə etmədiyini və niyə belə olduğunu başa düşməkdir.

İndekslər istifadə olunurmu, necə bilək?

Gəlin iki əsas problemi nəzərdən keçirək:

1. Yaratdığımız indekslər istifadə olunurmu?
2. Əgər istifadə olunursa, effektivdirmi?

Bunun üçün pg_stat_statements statistikalarını analiz edə bilərik, bir neçə sütuna diqqət yetir:

• rows: sorğu tərəfindən işlənən sətr sayı.
• shared_blks_hit: yaddaşdan oxunan səhifələrin sayı (diskdən yox).
• shared_blks_read: həqiqətən diskdən oxunan səhifələrin sayı.

Sorğunun işlədiyi sətr sayı nə qədər az, shared_blks_hit isə ümumi səhifələrə nisbətən nə qədər çox olsa, indeksimiz bir o qədər yaxşı işləyir.

İndeksləşdirmənin analizi nümunəsi

Tutaq ki, bizdə tələbələr cədvəli var:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- grade_level üçün indeks əlavə edirik
CREATE INDEX idx_grade_level ON students(grade_level);

İndi eksperiment üçün data əlavə edək:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Tələbə ' || generate_series(1, 100000),
    'Soyad',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

İndi müəyyən səviyyədə olan tələbələri tapmaq üçün sorğu yazırıq:

SELECT *
FROM students
WHERE grade_level = 10;

pg_stat_statements-də yoxlama

Sorğunu bir neçə dəfə işlətdikdən sonra statistikaya baxa bilərik:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Nəticənin interpretasiyası:

• rows: Əgər sorğu çoxlu sətr qaytarırsa, indeksin mənası varmı? Ola bilər ki, aşağı selektivlik üçün indeks lazım deyil.
• shared_blks_hit və shared_blks_read: Əgər çoxlu səhifə diskdən oxunursa (shared_blks_read), deməli ya indeks işləmir, ya da data buffer pool-da deyil.

İndeksləşdirmənin optimallaşdırılması

İndeks yaratmaq işin yarısıdır. Əsas odur ki, PostgreSQL həqiqətən ondan istifadə eləsin. Bəzən nə qədər çalışsan da, database nədənsə indeks əvəzinə bütün cədvəli ardıcıl skan edir. Niyə belə olur? Gəlin baxaq.

Əvvəlcə baxaq, niyə indeks bəzən açıq-aşkar faydalı olsa da, istifadə olunmur. Sonra isə — hansı fəndlərlə database-i "yadına salmaq" olar ki, bizdə indeks var və istifadə eləsin.

Bəs indeks istifadə olunmur?

Bəzən PostgreSQL indeksi görməzdən gəlir və ardıcıl skan (Seq Scan) edir. Bunun bir neçə səbəbi ola bilər:

1. Aşağı selektivlik. Əgər sorğu cədvəlin yarısından çoxunu qaytarırsa, ardıcıl skan daha sürətli ola bilər.
2. Data tipi və ya funksiyalar. Əgər sorğuda indekslənən sütunda funksiya istifadə edirsənsə, indeks işləməyə bilər. Məsələn:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- İndeks istifadə olunmur

Belə hallarda sorğunu belə dəyişmək olar:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- İndeks istifadə olunur

3. Uyğun olmayan indeks tipi. Məsələn, full-text search üçün GIN və ya GiST indekslər daha uyğundur, B-TREE yox.

4. Statistikanın səhv olması. Əgər statistika köhnədirsə, optimizer səhv qərar verə bilər. ANALYZE istifadə elə:

   ANALYZE students;
   

Sorğunu yaxşılaşdırmaq

Gəlin yenə nümunəyə qayıdaq. Əgər indeks işləmir, aşağıdakıları yoxla:

1. Sorğuda indeks istifadə oluna biləcək filterlərdən istifadə et: funksiya, type conversion və s. istifadə etmə.
2. Əgər filter çoxlu nəticə qaytarırsa, indeks lazımdırmı düşün. Əgər bu tez-tez işlədilən sorğudursa, cədvəl strukturunu dəyiş və ya materialized view əlavə et.
3. Əgər çoxlu data olduğuna görə Seq Scan istifadə olunursa, cədvəli partition-lara böl (PARTITION BY).

İndeksləşdirmənin effektivliyini yoxlamaq

Optimallaşdırmadan sonra sorğunu yenidən işlə və statistikaya bax:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Metrləri əvvəl və sonra müqayisə et. Diskdən oxuma (shared_blks_read) azalmalı, yaddaşdan oxuma (shared_blks_hit) isə artmalıdır.

Real case-lər

1. İndeksin düzgün istifadə olunmaması

Bizdə description adlı text sahəsi olan məhsullar cədvəli var:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Full-text search üçün indeks
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Əgər belə sorğu yazsaq:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

İndeks istifadə olunmayacaq! Səbəb odur ki, ILIKE GIN ilə uyğun deyil. İndeksdən istifadə etmək üçün sorğunu belə yazmaq lazımdır:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. İndeksin olmadığı yer

Tutaq ki, belə sorğu var:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

və bu ardıcıl skan (Seq Scan) edir. Səbəb birth_date üçün indeksin olmamasıdır. İndeks yaradıb:

CREATE INDEX idx_birth_date ON students(birth_date);

və statistikaları yeniləsən (ANALYZE students), bu sorğunun icrasını xeyli sürətləndirə bilərsən.