10

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?

  • when you use CTE for building up the query, adding another column to SELECT in WITH is just typing the name and rerunning. While with temporary table it would take DROP and CREATE. On the other hand if you build a query and you going to reuse static data lots of times - building temporary table with indexes is definitely beneficial against CTE. – Vao Tsun Nov 4 '16 at 8:19
  • @VaoTsun if using TEMPORARY TABLE with ON COMMIT DROP within a query, it's also just a matter of modifying the query and re-running, right? postgresql.org/docs/9.6/static/sql-createtable.html – Nathan Long Nov 7 '16 at 16:40
15

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.

  • What about views and temporary views? – CMCDragonkai Nov 3 '16 at 8:57
  • 1
    Kinda inbetween but closer to a temp table than a CTE term. No indexes. Session scoped. Visible to functions/procedures. Needs catalog vacuum. – Craig Ringer Nov 3 '16 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.