Funksiya və prosedurların performansının analizi: EXPLAIN ANALYZE istifadəsi

EXPLAIN ANALYZE sənə kömək edir başa düşməyə ki, PostgreSQL sənin sorğunu icra edəndə "nə düşünür":

• Verilənləri emal etmək üçün hansı addımlar atılır.
• Hər bir addımın icrası nə qədər vaxt aparır.
• Niyə hansısa sorğu yavaş işləyir — məsələn, tam cədvəl skan (ing. Seq Scan) və ya indeksin istifadə olunmaması.

EXPLAIN ANALYZE komandası əslində sorğunu icra edir və göstərir ki, PostgreSQL icranı necə optimallaşdırır. Təsəvvür elə, saatı sökürsən ki, mexanizmin necə işlədiyini başa düşəsən. EXPLAIN ANALYZE də eyni şeyi sənin SQL-sorğularınla edir.

EXPLAIN ANALYZE sintaksisi

Gəlin sadədən başlayaq. Komandanın əsas görünüşü belədir:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Bu sorğu SELECT komandasını icra edəcək və göstərəcək ki, PostgreSQL verilənləri necə emal edir.

EXPLAIN ANALYZE nəticəsi sorğunun icra ağacından ibarətdir. Ağacın hər səviyyəsi PostgreSQL-in icra etdiyi addımı təsvir edir:

• Operation Type — əməliyyatın tipi (məsələn, Seq Scan, Index Scan).
• Cost — PostgreSQL-in bu əməliyyatı nə qədər "bahalı" hesab etdiyini göstərir.
• Rows — neçə sətrin gözlənildiyi və əslində neçə sətir alındığı.
• Time — əməliyyatın nə qədər vaxt apardığı.

Çıxış nümunəsi:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Seq Scan on students-ə fikir ver. Bu o deməkdir ki, PostgreSQL students cədvəlinin BÜTÜN sətrlərini oxuyur. Əgər cədvəl böyükdürsə, bu ÇOX YAVAŞ ola bilər.

EXPLAIN ANALYZE istifadəsinə nümunələr

Gəlin bir neçə praktiki nümunəyə baxaq və sorğulardakı problemləri necə tapıb aradan qaldırmağı öyrənək.

Nümunə 1: tam cədvəl skanı

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Çıxış:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Buradakı problem odur ki, PostgreSQL Seq Scan edir, yəni cədvəlin bütün sətrlərini bir-bir yoxlayır. Əgər cədvəldə milyonlarla sətir varsa, bu performans üçün dar boğaz olacaq.

Həll: age sütunu üzrə indeks yaradaq.

CREATE INDEX idx_students_age ON students(age);

İndi eyni sorğunu icra et:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Çıxış:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Artıq Index Scan görürük, Seq Scan yox. Super, sorğu indi uçur!

Nümunə 2: JOIN ilə mürəkkəb sorğu

Təsəvvür elə ki, iki cədvəlimiz var: students və courses. Biz istəyirik bilək tələbələrin adlarını və onların yazıldığı kursların adlarını.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Çıxış təxminən belə ola bilər:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Gördüyün kimi, PostgreSQL hər şeyi nəzarətdə saxlayır: enrollments və courses cədvəllərində indekslər istifadə olunur və icra sürətlidir. Amma hansısa indeks yoxdursa, Seq Scan görə bilərsən və bu icranı yavaşıdar.

Funksiyaların performansının optimallaşdırılması

İndi təsəvvür elə ki, bir funksiya var, hansı ki, müəyyən yaşdan yuxarı olan tələbələrin siyahısını qaytarır:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Bu funksiyanın performansını EXPLAIN ANALYZE ilə analiz edə bilərik:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Funksiyanın icrasını sürətləndirmək

Əgər funksiyanın icrası çox vaxt aparırsa, yəqin ki, problem tam cədvəl skanındadır. Bunu düzəltmək üçün:

1. Filtrlərdə istifadə olunan sütunun (age) indeksləndiyinə əmin ol.
2. Cədvəldəki sətrlərin sayına bax və əgər çoxdursa, partisionlaşdırmanı düşün.

Dar boğazlar və onları necə düzəltmək olar

1. Tam cədvəl skanı (Seq Scan). Sətirləri tez tapmaq üçün indekslərdən istifadə et. Amma unutma, çoxlu indekslər əlavə etmək insert əməliyyatlarını yavaşıda bilər.

2. Nəticə dəstində çoxlu sətir. Əgər sorğu milyonlarla sətir qaytarırsa, əlavə filtr (WHERE, LIMIT) və ya səhifələmə (OFFSET) əlavə etməyi düşün.

3. "Bahalı" əməliyyatlar. Bəzi əməliyyatlar, məsələn, sortlama, aqreqasiya və ya böyük cədvəllərin birləşdirilməsi çox resurs tələb edə bilər. İndekslərdən istifadə et və ya sorğuları bir neçə mərhələyə böl.