← Powrót do Bazy wiedzy← Poprzednie pytanieNastępne pytanie →
programowanieProgramista Backend

Co robi instrukcja WITH (Common Table Expressions, CTE)? Jakie są zalety korzystania z CTE w porównaniu do podzapytań? Podaj przykład.

Zdaj rozmowy kwalifikacyjne z asystentem AI Hintsage

Odpowiedź

Instrukcja WITH (lub CTE — Common Table Expression) tymczasowo tworzy zbiór wyników, który można wykorzystać w głównym SELECT lub nawet rekurencyjnie. CTE upraszczają zapytania do zrozumienia, zwiększają czytelność i pozwalają na ponowne wykorzystanie pośrednich wyników.

Zalety w porównaniu do zwykłych podzapytań:

  • Poprawiają czytelność skomplikowanych zapytań (łatwiej debugować i utrzymywać).
  • Można używać zapytań rekurencyjnych.
  • Można odwoływać się do CTE wiele razy po nazwie, zamiast duplikować to samo podzapytanie.

Przykład:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Pytanie z haczykiem

“Czy ważne jest wskazanie MATERIALIZED/NOT MATERIALIZED podczas deklarowania CTE? Jak może to wpłynąć na wydajność?”

Odpowiedź: W PostgreSQL można wyraźnie wskazać MATERIALIZED (zawsze obliczać CTE raz i przechowywać, nawet jeśli jest używane kilka razy) lub NOT MATERIALIZED (CTE będzie inline w głównym zapytaniu). Zazwyczaj optymalizator sam decyduje, czy warto materializować CTE, ale wymuszenie tego czasami znacząco wpływa na szybkość.

Przykład:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Historia

W dużym projekcie analityk wymienił wszystkie podzapytań na CTE, nie wiedząc, że w PostgreSQL przed wersją 12 CTE zawsze były materializowane. Wynik — zapytania zwolniły się 2-3 razy. Po powrocie do podzapytań lub przejściu na nowe wersje problem zniknął.

Historia

Raport z kilkoma poziomami zagnieżdżonych podzapytań stał się nieczytelny dla kolegi. Przepisałem na CTE o zrozumiałych nazwach — dyskusje i wsparcie zapytań znacznie się uprościły.

Historia

Dwa CTE o identycznych nazwach wywołały zamieszanie w dużym skrypcie: CTE było używane jeszcze przed jego deklaracją. W rezultacie — błąd kompilacji i duża strata czasu na szukanie przyczyny.