Typowe wyrażenia tabel PostgreSQL a tabela tymczasowa?

Dokumentacja PostgreSQL na WITH pokazuje następujący przykład:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Zwraca również uwagę:

Przydatną właściwością zapytań WITH jest to, że są one oceniane tylko raz na wykonanie zapytania nadrzędnego, nawet jeśli odwołuje się do nich więcej niż raz przez zapytanie nadrzędne lub rodzeństwo Z zapytaniami rodzicielskimi.

Widzę, że WITHmożna to wykorzystać do innych rzeczy, takich jak ocena rekurencyjna. Ale czy w powyższym przykładzie jest jakaś istotna różnica między używaniem WITHa tworzeniem tabel tymczasowych?

Istnieje kilka subtelnych różnic, ale nic drastycznego:

• Możesz dodać indeksy do tabeli tymczasowej;
• Tabele tymczasowe istnieją przez cały czas trwania sesji (lub, jeśli ON COMMIT DROPtransakcja), wheras WITHzawsze jest ściśle ograniczony do zapytania;
• Jeśli zapytanie wywołuje funkcję / procedurę, można go zobaczyć tabelę temp, ale może nie widać żadnych WITHtabel wyrażeń;
• Tabela tymczasowa generuje VACUUMpracę w katalogach systemowych, która WITHtego nie robi, potrzebuje dodatkowej podróży w obie strony, aby ją utworzyć / wypełnić i wymaga dodatkowej pracy w zarządzaniu pamięcią podręczną serwera, więc jest nieco mniej wydajna.

Ogólnie rzecz biorąc, powinieneś preferować WITHtabele tymczasowe, chyba że wiesz, że skorzystasz na stworzeniu indeksu.

Jednak druga opcja, podzapytanie w FROMklauzuli, ma zupełnie inny zestaw zalet. W szczególności można go wstawiać, a kwalifikatory można podciągać / przesuwać w dół. Pisałem o tym w ostatnim artykule na blogu .