Sorğuların CTE ilə optimallaşdırılması

Bu gün biz CTE (Common Table Expressions) ilə sorğuların optimallaşdırılması dünyasına baş vururuq — həm maraqlı, həm də bir az qorxulu mövzudur. Əgər artıq CTE necə yaradılır bilirsənsə (əvvəlki leksiyalarda danışmışdıq), indi vaxtdır onların daxili iş prinsipləri, “gizli tələlər” və maksimum effektivlik üçün nələr etmək olar, bunları müzakirə edək.

İlk baxışdan CTE-lər ideal görünür: kodu təmiz saxlayır, yazmaq rahatdır, kodu məntiqi bloklara bölməyə imkan verir. Amma bir balaca (bəzən də böyük) nüans var. PostgreSQL-in CTE ilə işləmə strategiyası onların performansına təsir edir.

PostgreSQL WITH görəndə, adətən CTE-nin nəticəsini materializasiya edir. Yəni, CTE-dən qaytarılan məlumatlar əvvəlcə hesablanır və müvəqqəti cədvəl kimi saxlanılır, sonra əsas sorğuda istifadə olunur. Bu, təkrar istifadə üçün rahatdır, amma problem ola bilər, əgər:

1. CTE-dəki məlumat çox böyükdürsə və nəticə yalnız qismən istifadə olunursa.
2. CTE çox dəfə çağırılırsa və əlavə xərclər artırsa.
3. Əslində lazım olmayan mürəkkəb CTE-lər yaradırıqsa.

Materializasiya ilə tanışlıq

Materializasiya — PostgreSQL-in CTE nəticəsini yaddaşda və ya diskdə (məlumatın ölçüsündən asılı olaraq) saxlaması prosesidir. Yəni, məlumat yalnız bir dəfə çıxarılır, amma əgər CTE yalnız bir yerdə istifadə olunursa, materializasiya artıq ola bilər. Məsələn:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Bu halda PostgreSQL əvvəlcə tam CTE-nin nəticəsi ilə müvəqqəti cədvəl yaradır (grade > 60), sonra isə grade > 90 olan sətirləri filtrləyir. Bu, artıq bir ara addım əlavə edir və performansa təsir edir.

Artıq materializasiyadan necə qaçmaq olar?

PostgreSQL 12-dən başlayaraq, lazım olmayan hallarda CTE-nin materializasiyasından qaçmaq mümkündür. Bunun üçün MATERIALIZED (default) və ya NOT MATERIALIZED açar sözlərindən istifadə olunur. Məsələn:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Burada biz PostgreSQL-ə deyirik ki, large_set məlumatını materializasiya etməsin, sorğunu birbaşa əsas ifadəyə daxil etsin. Bu, sorğunu daha effektiv edir, çünki ara cədvəl yaradılmır.

Materializasiya nə vaxt faydalıdır?

Elə düşünmə ki, materializasiya həmişə pisdir! Əgər CTE-dəki məlumat sorğuda bir neçə dəfə istifadə olunursa və ya ayrıca hesablanmalıdırsa, materializasiya faydalı ola bilər. Məsələn:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Burada materializasiya grade > 60 filtrinin təkrar hesablanmasının qarşısını alır.

İndekslərlə sorğuların optimallaşdırılması

CTE-lərin daha sürətli işləməsi üçün, məlumat çıxarılan əsas cədvəllərdə indekslərdən istifadə etmək lazımdır. Məsələn:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

grade sütunu üzrə indeks PostgreSQL-ə grade > 90 şərtinə uyğun sətirləri daha tez çıxarmağa imkan verir. Bu, xüsusilə böyük cədvəllərlə işləyərkən vacibdir.

Böyük CTE-ləri daha kiçik hissələrə bölmək

Əgər CTE çoxlu məlumat qaytarırsa və sonra onlar filtrlənir və ya aqreqasiya olunursa, onu bir neçə mərhələyə bölmək daha yaxşıdır. Bir böyük CTE əvəzinə bir neçə kiçik yaratmaq rahatdır:

Pis (böyük CTE):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Daha yaxşı (mərhələlərə bölmək):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

Bu yanaşma PostgreSQL-ə sorğunun icrasını daha yaxşı optimallaşdırmağa kömək edir.

Praktik nümunə: strukturun analizi və optimallaşdırma

Gəlin bir az daha mürəkkəb nümunəyə baxaq. Bizdə tələbələr, kurslar və qiymətlər cədvəlləri var. Yüksək orta balı olan tələbələri tapmaq və onların siyahısını uyğun kurslarla birlikdə çıxarmaq istəyirik:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Bu sorğunu optimallaşdırmaq üçün grades və enrollments cədvəllərinə indeks əlavə etmək olar, bu da filtrasiya və join-ləri sürətləndirəcək.

Monitoring: performansın analizi

Sorğunun nə qədər effektiv olduğunu başa düşmək üçün EXPLAIN və ya EXPLAIN ANALYZE istifadə et. Məsələn:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Bu sorğu hər addımın nə qədər vaxt apardığını göstərəcək və harada performansı yaxşılaşdırmaq olar, onu anlamağa kömək edəcək.

EXPLAIN ANALYZE barədə daha ətraflı növbəti səviyyələrdə danışacağıq :P

CTE optimallaşdırmasında tez-tez rast gəlinən səhvlər

1. İndeksləri unutmaq. Əgər filtrasiya CTE-də aparılır, amma əsas cədvəldə indeks yoxdursa, performans zəifləyəcək.
2. Çox böyük CTE-lərdən istifadə. Bir sorğu çox iş görürsə, bu, böyük həcmdə məlumatın materializasiyasına səbəb ola bilər.
3. NOT MATERIALIZED ilə həddindən artıq istifadə. Bəzi hallarda materializasiya lazımdır ki, CTE-nin təkrar icrasından qaçmaq olsun.
4. Monitoring-i nəzərə almamaq. EXPLAIN ilə analiz etmədən, sorğuların yavaş işlədiyini görə bilməzsən.

İndi sən CTE ilə sorğuları optimallaşdırmağa hazırsan, tələlərdən qaçıb performansı artırmağa başla! Unutma, CTE bir alətdir, möcüzə deyil. Onlardan ağılla istifadə et və onlar PostgreSQL-də ən yaxşı dostların olacaq.