Optymalizacja zapytań z użyciem CTE

Dzisiaj zanurzymy się w ekscytujący (i trochę straszny) świat optymalizacji zapytań z użyciem CTE (Common Table Expressions). Jeśli już ogarnąłeś, jak tworzyć CTE (gadaliśmy o tym na poprzednich wykładach), to teraz czas pogadać o szczegółach ich działania, „pułapkach” i sposobach wyciśnięcia z nich maksimum wydajności.

Na pierwszy rzut oka CTE wydają się idealne: wyglądają schludnie, pisze się je łatwo, pozwalają dzielić kod na logiczne bloki. Ale jest jeden mały (albo nie taki mały) haczyk. PostgreSQL ma specyficzną strategię pracy z CTE, która wpływa na ich wydajność.

Kiedy PostgreSQL widzi WITH, zazwyczaj materializuje wynik CTE. To znaczy, że dane zwrócone przez CTE są najpierw obliczane i zapisywane jako tymczasowa tabela, która potem jest używana w głównym zapytaniu. To wygodne, jeśli chcesz użyć tych danych kilka razy, ale może być problematyczne, jeśli:

1. Ilość danych w CTE jest ogromna, a wynik używany jest tylko częściowo.
2. CTE jest wywoływane zbyt wiele razy, co zwiększa narzut.
3. Tworzymy niepotrzebnie skomplikowane CTE, które tak naprawdę nie są potrzebne.

Poznanie materializacji

Materializacja — to proces, w którym PostgreSQL zapisuje wynik CTE w pamięci lub na dysku (w zależności od rozmiaru danych). To znaczy, że dane są pobierane tylko raz, ale jeśli używasz CTE tylko w jednym miejscu, materializacja może być zbędna. Na przykład:

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

W tym przypadku PostgreSQL najpierw tworzy tymczasową tabelę z pełnym wynikiem CTE (grade > 60), a potem filtruje wiersze, gdzie grade > 90. To dodaje niepotrzebny krok pośredni i wpływa na wydajność.

Jak uniknąć zbędnej materializacji?

Od PostgreSQL 12 pojawiła się opcja unikania materializacji CTE, kiedy nie jest to potrzebne. Służy do tego słowo kluczowe MATERIALIZED (domyślnie) albo NOT MATERIALIZED. Przykład:

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

Tutaj mówimy PostgreSQL, żeby nie materializował danych large_set, tylko wstawił zapytanie bezpośrednio do głównego wyrażenia. Dzięki temu zapytanie jest bardziej wydajne, bo nie powstaje tabela pośrednia.

Kiedy materializacja jest przydatna?

Nie myśl, że materializacja to zawsze zło! Jeśli dane z CTE są używane wielokrotnie w zapytaniu albo muszą być obliczone niezależnie, materializacja może być przydatna. Przykład:

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;

Tutaj materializacja pozwala uniknąć ponownego obliczania filtra grade > 60.

Optymalizacja zapytań z użyciem indeksów

Żeby CTE działały szybciej, warto używać indeksów w bazowych tabelach, z których pobierane są dane. Na przykład:

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;

Indeks na kolumnie grade pozwala PostgreSQL szybciej pobierać wiersze spełniające warunek grade > 90. To szczególnie ważne przy pracy z dużymi tabelami.

Rozbijanie dużych CTE na mniejsze

Jeśli CTE zwraca dużo danych, które potem są filtrowane albo agregowane, lepiej rozbić je na kilka etapów. Zamiast jednego dużego CTE wygodniej stworzyć kilka mniejszych:

Źle (duży 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;

Lepiej (rozbicie na etapy):

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;

Taki sposób pomaga PostgreSQL lepiej zoptymalizować wykonanie zapytania.

Praktyczny przykład: analiza struktury i optymalizacja

Sprawdźmy bardziej złożony przykład. Mamy tabele studentów, kursów i ocen. Chcemy znaleźć studentów z wysoką średnią ocen i wyświetlić ich listę razem z odpowiednimi kursami:

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;

To zapytanie można zoptymalizować, jeśli dodasz indeksy do tabel grades i enrollments, co przyspieszy filtrowanie i łączenie.

Monitoring: analiza wydajności

Żeby sprawdzić, jak wydajne jest zapytanie, użyj EXPLAIN albo EXPLAIN ANALYZE. Na przykład:

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;

To zapytanie pokaże, ile czasu zajmuje wykonanie każdego kroku i pomoże ogarnąć, gdzie można poprawić wydajność.

Bardziej szczegółowo EXPLAIN ANALYZE ogarniemy na kolejnych poziomach :P

Typowe błędy przy optymalizacji CTE

1. Zapomniane indeksy. Jeśli filtrujesz dane w CTE, ale w bazowej tabeli nie ma indeksu, wydajność siada.
2. Używanie zbyt dużych CTE. Jeśli jedno zapytanie robi za dużo, może to prowadzić do materializacji dużych ilości danych.
3. Nadużywanie NOT MATERIALIZED. W niektórych przypadkach materializacja jednak jest potrzebna, żeby uniknąć ponownego wykonywania CTE.
4. Ignorowanie monitoringu. Bez analizy przez EXPLAIN możesz nie zauważyć, że zapytania działają wolno.

Teraz jesteś gotowy optymalizować zapytania z użyciem CTE, unikając pułapek i zwiększając wydajność! Pamiętaj, że CTE to narzędzie, a nie złoty środek. Używaj ich z głową, a staną się twoimi najlepszymi ziomkami w PostgreSQL.