PostgreSQL Common Table Expressions vs a temporary table?

The PostgreSQL documentation on WITH shows the following example:

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;

It also notes:


A useful property of WITH queries is that they are evaluated only once
per execution of the parent query, even if they are referred to more
than once by the parent query or sibling WITH queries.

I see that WITH can be used for other things, like recursive evaluation. But in the example above, is there any important difference between using WITH and creating temporary tables?

There are a few subtle differences, but nothing drastic:

• You can add indexes on a temp table;

• Temp tables exist for the life of the session (or, if ON COMMIT DROP, transaction), wheras WITH is always scoped strictly to the query;

• If a query invokes a function/procedure, it can see the temp table, but it can not see any WITH table-expressions;

• A temp table generates VACUUM work on the system catalogs that WITH doesn't, it needs an extra round trip to create/fill it, and it requires extra work in the server's cache management, so it's slightly less efficient.

Overall, you should prefer WITH to temp tables unless you know you will benefit from creating an index.

However, the other option, a subquery in the FROM clause, has a very different set of advantages. It can be inlined, in particular, and qualifiers can be pulled up / pushed down. I wrote about this in a recent blog article.

StackExchange Database Administrators Q#78253, answer score: 26