WITH robi różnicę (na pewno w PostgreSQL)

CTE, czyli Common Table Expressions, nazwywany również konstrukcją WITH, to jedna z konstrukcji języka SQL, która oferuje alternatywny sposób zapisu zapytania złożonego z podzapytań.

Dla większości silników bazodanowych CTE i podzapytania są w pełni równoważnymi konsktrukcjami. Każde podzapytanie może zostać przepisane na CTE i odwrotnie. Wówczas (pod warunkiem, że zostanie ono prawidłowo przepisane): nie tylko zwróci identyczne wyniki, ale również może zostać przekształcone przez planer do identycznej postaci. Są więc idealnym przykładem tzw. lukru syntaktycznego.

Warto wiedzieć, że w przeciwieństwie do innych systemów bazodanowych, w Postgresie CTE lukrem syntaktycznym nie jest. Jeśli więc pracowaliście dotąd z innymi bazami, to co dzieje się w silniku Postgresa może stanowić dla was niemałe zaskoczenie. Mianowicie przy optymalizacji zapytania nagle możecie zauważyć, że:

• w systemie zaczynają się tworzyć pliki tymczasowe (zależy od ustawienia work_mem),
• zamiast selektywnego wybrania kilku wierszy za pomocą indeksu pojawia się węzeł sequential scan, czyli sekwencyjny odczyt całej tabeli.

Dwa poniższe zapytania są dla Postgresa zupełnie czym innym:

SELECT *
FROM (SELECT
             n i_name,
             n i_price
           FROM generate_series(1, 10000) n) AS price_list
WHERE i_price BETWEEN 10 AND 100;

i

WITH price_list AS (
  SELECT
            n i_name,
            n i_price
        FROM generate_series(1, 10000) n
) SELECT * FROM price_list
WHERE i_price BETWEEN 10 AND 100;

Oczywiście, dane przez nie zwrócone będą identyczne, różnica tkwi w sposobie ich przetwarzania. O ile przy pierwszej konstrukcji podzapytanie może:

• zostać „wypłaszczone” do zapytania głównego i potraktowane jako jedna instrukcja, lub
• podzapytanie może zostać zmaterializowane, a główne zapytanie przetworzy zmaterializowane uprzednio wyniki, lub
• wyjście z podzapytanie może być przekazywane na bieżąco do węzła nadrzędnego

to w przypadku konstrukcji z WITH zawsze zaobserwujemy materializację.
Z tego powodu CTE w postgresie uznaje się za swoisty „hint”, analogiczny do oraclowego /*+ materialize */, a raczej obejście umożliwiające korzystanie z hintów bez ich posiadania :).