In PostgreSQL 8.4 we got CTE – Common Table Expressions. Since then we have this great tool available, but apparently for some people it's still black magic. CuTE, but still magic. I'll try to make it a bit less magical, and more understandable.

Line #1 – starts with word “WITH" – this is the tell-tale sign of CTEs. Every query that uses them has to start using WITH

Still line #1 – “base_info" – name of the CTE that will be defined after “as", and within parentheses. Any name is acceptable – basically it is just like table or view name

Lines 2-7 – definition of the CTE. This is a query, that can return any number of rows – you can think of it as inlined VIEW (though there are differences)

Line 8 – parentheses finishing current CTE. We could have more than one in given query, using syntax like: with a as (…), b as (…), c as (…), but for now single CTE is enough

Lines 9-11 – actual query, that is using the CTE just as it would any table/view – just providing its name in FROM clause.

Hope that's not scary.

Now you could ask: well, OK. But what is the point of using CTE when you can simply have VIEWs?

Answer has two elements. First – because it can be simpler. Instead of going the whole route of think about view, create it, grant privileges, and so on – you just embed the view in your query, and you're done.

But the other, much more important (in my opinion) thing is that CTEs, unlike VIEWs, are treated as separate entities, and behave like “optimization wall".

Please note that planner did something rather stupid in here: instead of getting 478 rows that match where condition, it instead scanned almost whole table, discarded first found 9001 rows, and then, finally, found 5 newest rows with some_val = 1.

( side note: this example is one of the reasons I'm firm believer that we should have planner HINTs, despite all the opposition from some developers )

Why did it happen? Well, without going into too much detail – PostgreSQL assumed some things that are not true, and ran the query as if they were. So it was slower than it could be.

Let's see how I can make PostgreSQL behave if I'll use CTE optimization barrier to fight stupid decisions:

Why is that so? Very simple – view rewrites the query, and CTE is “materialized". And this means that in VIEW version our slow function has to be called 20 times (10 times to return records, and 10 times to be used for summing of return values). In case of CTE – function is called 10 times, and the returned values are available to reuse for summing.

The difference should be clearly visible – view approach was able to use the “some_val = 3″ condition to fetch less rows from table, and then count just them.

CTE approach is different – first it takes all records (rows=10000), then it aggregates it, and then, from the aggregate takes single row.

As you can see – there is no silver bullet. There are cases where CTEs are better, and there are cases when CTEs are worse. I like to use them, because it lets me write my query in a sort of “block-by-block" way. Like: first, I need some rows from here, so I'll build CTE that does it. Then, based on these rows I need something else, so I add another CTE, and so on.

All I have written up to now is about simple CTEs – just a “views in disguise". But CTEs have one big trick up their sleeve: recursive cuteness.

We all know that to understand recursion you have to first understand recursion. How can I explain it, then?

recursive CTE consists of two queries joined with UNION or UNION ALL. In my case it is “SELECT 1 as x" and “SELECT x+1 FROM i WHERE x < 10

You might ask: what if I have multiple CTEs in single query, and one of them, and not the first one, is recursive, while others are not? It's simple – as long as any of the CTEs are recursive, you have to use “WITH RECURSIVE".

Let's get a bit more complicated query, so we can see what's happening and how.

For my tests, I will be using copy of DMOZ category structure that I downloaded long time ago. You can get the data in sql-dump format from here so you can follow with my examples.

To have rather small, but interesting sample data, I chose these categories:

Of course, it works, but please note that we would have to add another query just to get 4 levels that we need in our case, but technically – we should be adding much more queries if we didn't know how deep the structure goes.

Recursive CTE is great solution, because You can rewrite potentially infinite query (UNION-ALL style, like above) to something way simpler:

Line #1 – marks that we will be doing some recursion (WITH RECURSIVE), and starts “all_kids" cte.

Line #2 – it's the initial row for recursion – the row that we know how to get by id

Line #3 – UNION ALL – all recursive queries, as I said – as two queries with UNION or UNION ALL in between

Lines #4-#5 – we will be returning all columns from table dmoz

Line #6 – rows to return should have parent_id that is listed in all_kids cte

Line #7 – ends the CTE

Line #8 – returns rows from all_kids

You might wonder – well, but on first loop – the 2nd query (after UNION ALL), will be equivalent to: select * from dmoz where parent_id = 92987, because that's the only row there. What happens on subsequent rows, though? Which rows are taken into consideration? Of course new ones (with ids 92989, 92991, 92996, 92998, but is 92987 (top level) still taken into consideration)? No. On each run only newly added rows are “visible" to recursive part. We can see it by adding simple window function:

In here, I used first normal CTE (ids_to_delete) to get some ids of rows to delete, then I used delete_it as writable cte – that is – it did remove the rows, and delete_it CTE contained deleted rows. And finally – I got labels of all deleted rows as single column.

I hope this will make this CuTE feature of PostgreSQL simpler to understand and use. As always – if anything is not clear, please let me know so I can fix it.

One comment

I’d just like to note that this “optimization wall” thing has frequently hit me from the opposite side of the equation. I often like to use CTEs to make my query clearer and more organized–but because of this effect, it can result in an unworkable query plan. (As in: going from less than a second to potentially multiple hours. Not just getting a little less efficient.)

That kind of situation, where the structure of the query must be changed in order to influence the query plan, is exactly what I never want in my DBMS. So sign me up for “hints would be a very good thing to have, particularly if they don’t have to be in-line in the body of the query”, and a desire that the query planner also do a better job of making a larger variety of similar queries behave the same way.