The best Postgres feature you're not using – CTEs aka WITH clauses

Yeah, SQL is clearly braindead and has seen its best days. Let's should mass revolt and switch to NoSQL! </sarcasm>

Unfortunately I haven't read the SQL standard(s) and I also seem to have trouble finding them in full on the internet.

However I was told on IRC in #postgres that not optimising across CTEs was something mandated by the standard.

Of course I might have been told something that's not quite correct or I might have misunderstood.

However in the context of postgres (which is what the article is talking about), I know for a fact that CTEs are first fully resolved. This is what I'm seeing in my query plans and it's what the manual says: http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)

If I'm wrong what the standard is concerned, then I apologise.

... that seems thoroughly backwards to me, at least in terms of expectations. I mean, I know that views that were optimization boundaries wouldn't be useful, but just from an intuitive perspective I'd expect that the stand-alone database objects would be optimization boundaries while the query-supporting statements would be optimized as part of the query.

> If you know that the CTE is deterministic and has no side effects, you can just treat it like a macro.

With PostgreSQL you can't really assume this. Consider this:

CREATE FUNCTION stable_random() returns double language sql as $$ select random() $$ stable;

Now what I have done is wrapped a volatile function in a stable function. Now the planner thinks it is deterministic and will fold it into the plan in a different way. I could further mark it as immutable and allow the planner to run it once per query even if it occurs multiple times. Now: select my_random() will be treated as fully deterministic, so select my_random() a from my_table will return a list of the same singular random number (all values of a will be the same).

So how is the planner to know this? Obviously we could just let the planner decide based on function markers but then you'd run into the case where a stable function above would produce different results, while a volatile or an immutable one would produce the same result. That seems to make very little sense to me.....

I'm the opposite. I hate joins in the where clause because it is semantically wrong and confuses the building the set part with the filtering the result part. I want to see how a table is joined into the query at the place it is joined and additional filtering in the where. To me it makes it much easier to follow and not miss a join item, especially if the join happens on multiple columns.

I really hope you are not suggesting going back to things like <star>= and =<star>

My experience is the exact opposite. Only absolutely beginner php/mysql guys do that. And they do so because they don't understand joins. You can't even do outer joins implicitly without vendor specific extensions (which are being deprecated), so the idea that it is better when mixing join types is particularly odd. It is also far from "the standard" in the oracle world. Oracle land has more people who think using weird oraclisms are good, but there's still an awful lot of people who prefer ANSI syntax there. Just look at the answers to questions about it on any forum.

I've worked in a mixed Oracle / PostgreSQL environment, and I prefer the explicit join syntax.

1. I hate needing to parse/use the '(+)' to denote which way an outer join goes. It's much easier to read "left outer join".

2. Separation of concerns. This way all of the conditions that are in the WHERE clause act as filters on the data, and all of the JOIN conditions sit right next the the relevant tables in the FROM clause.

3. I don't even know what the WHERE clause syntax for a CROSS JOIN would be.

4. When a single JOIN has multiple conditions, it's much nicer to have them all together in the FROM clause than to have them scattered (or even grouped) in the WHERE clause.

5. You can use USING. When you're joining two tables on a column with the same name (e.g. product_id), I always find it annoying to be required to specify which table's column to look at since they are guaranteed to be equal.

I don't consider myself a 'database expert', but I spend several years in an environment where 100+ line queries with 5-10 joined tables was not uncommon (so I'm not coming from a place of only having worked with simplistic queries).

> Completely disagree. All the database developer "experts" I know of (including myself) hate the explicit join syntax.

[...]

> Maybe it's also because I come from the Oracle world where it is the standard.

IME, lots of Oracle experts -- who are likely to be longtime heavy users that started when Oracle didn't support explicit joins -- often prefer implicit joins because its what they are used to.

But its much clearer when you need to reuse and modify a query -- particularly someone else's query -- to have the join conditions clearly identified and distinct from filtering conditions.

The purist in me hates the explicit join syntax.

The pragmatist in me remembers too many painful times fighting with overcomplicated "WHERE" clauses and the bizarre (+) syntax in old Oracle servers.

Advantage: pragmatist.

There may be some magical hypothetical relational-theory language where this sort of approach can be cleanly expressed, but Oracle PL/SQL isn't it.

I went from from a company that generally adopted the implicit syntax to one that uses explicit, and after some adjustment, I've come to slightly prefer the latter. One thing that helps is to indent the join condition, formatting the SQL as:

  select a.foo, b.bar, c.blah
    from blahblah a
         join whatever b on a.ID = b.A_ID
         join another_table c on b.ID = c.B_ID
   where 1=1
     and a.foo = "baz"

This becomes less clear with certain types of joins, but generally, I've come to prefer it.

I think you're either very selective with your sources or very selective about who you give the "expert" title to. I don't know that I've seen implicit joins used by a speaker at a SQL event in the past couple years, unless it's for cartesian joins.

I can't claim to have worked with a representative sample of SQL developers, but I've worked with a lot and written huge quantities myself, and I'd like to think some of it was near the 'expert' level.

And I'm 100% with the OP, not you, sorry. Explicit joins are, IMHO, enormously clearer and easier to work with, particularly with outer joins, cross joins (the rare times they're intentional), CROSS APPLY....

Each to their own though. You write the code that's easier for you and your team to read; I just think it's unlikely we'll be happy working on a team together ;-)

I am on the other side. Here's why.

if you have a very simple join it really makes no difference. On the other hand, when you have 10 tables involved in a 200 line query, debugging and maintaining the query is directly proportional to how well you can structure it. Breaking the query down into functional, related pieces which can be reviewed and managed separately is the key to maintaining large queries.

The way I describe it is this: In C or Perl, the complexity of debugging is linearly proportional to the length of the function. State accumulates during the function and consequently you have to read each line in context with those which came before and after.

With well-structured SQL, OTOH, you have something different. You have a set of sub-functions which are each somewhat independent and state does not (usually, outside of specified window functions) accumulate. Because it is declarative, a well structured query can be debugged not as a linked list of statements but as a tree. This is far more efficient. Personally I find a well-structured 200 line SQL query to be far more maintainable than a 50 line Perl function. And if it is really well structured, you could probably push this further out.

I 100% disagree with you. How is having even more conditions in the where clause helpful? It makes it very easy to accidentally cross join (Oops, I forgot to actually join that table because I have 40 lines in my where clause).

Also, I believe that in SQL Server, the old (ANSI 89) style of joining (joining in the where clause) was deprecated and isn't optimized anymore so you'll get crap performance because all of the tables are actually cross joined before the filtering happens. The explicit join forces relationships to be accounted for first which can help keep the amount of memory needed down.

One last thing, how long does it take to adapt a new standard? The explicit join has been the standard since 1992.

That makes troubleshooting and debugging a lot faster because you can troubleshoot join conditions and projection issues separately from filters.

As a counter-point here, I would point out that there are cases where we've found (with the LedgerSMB project) drastic increases in performance in switching to CTE's, especially with recursive ones.

The reason is that if you have a stable result set which is complex to calculate (particularly if it involves recursive processing), but needs to be checked against multiple conditions in different subqueries, it is far better to materialize it once and check a few times, than to materialize it in every subquery.

For example, our menu uses a simple referential tree. Generating this with connectby() when we had to support 8.3 primarily usually worked unless you wanted to exclude a large number of rows based on permissions. In that case, it would take better part of a minute to run. Not good for a web app.

Moving to recursive CTE's cut the execution time of our best performance in half, and increased our worst cases by orders of magnitude.

To be honest though, for shareable code, I think views are often better because they represent a stable interface that can be documented regarding acceptable uses (should you join this view? self-join it?). What CTE's often buy you though is an ability to break a query off into multiple, independently testable pieces (where a view might be overkill due to a lack of sharing), and that allows you to better debug when something goes wrong.

Yeah, it depends on what you're trying to do with the graph as well. I was using a mix of Postgres operations and Java code to do a Floyd-Warshall "all shortest paths" calculation. I forget how many vertices I had, but it wasn't many... on the order of hundreds or thousands, at most. And the Postgres part just totally bogged down when using CTEs. I finally switched to storing the underlying graph itself in my own hand-rolled format (and eventually ran into different bottlenecks, but that's another story).

Now it's possible that if I were more of a Postgres expert, I could have tuned or optimized it to behave better. But at the minimum, I'll say that a naive graph implementation using CTEs didn't prove to scale well for me, in this given use-case.

I'd be interested in seeing how you approach some of the things that graphs are better at. I have an understanding of creating efficient tree structures in a relational model - I haven't thought about the more general case of graphs though.

I was recently involved in a project that was using neo4j as a datastore. In the end we switched to postgres because the cognitive load was lower, the tooling was better and the relationship management was easier and more explicit. The trade off is going to be that later the relationship exploration will be a little harder. My plan was to add a graph datastore back in to mirror the relationships in the system so we could query it.

I think your big limiting factor in graph analysis with recursive cte's is that PostgreSQL is relational and therefore set-based. This effectively forces any search into a breadth-first search.

I don't see any reason why doing breadth-first searches would be any slower on PostgreSQL than Neo4j. But the question is, what happens when bredth-first is not what you want to do?

Now with some work, it should be quite possible to do depth-first or other sorts of searches, but you'd probably want to think very, very carefully about the implementation and use something besides a recursive cte.

So I would expect something like "show me everything within two hops of x" to perform pretty well if you designed your query right (and you'd want to put the starting point and the limit in your CTE so you aren't pulling the whole graph first to check). I would expect "show me the shortest route from x to y" to perform much better through a non-relational approach. However getting anything to work well would require carefully thinking through your query.

Ahhh, just read about that. The equivalent of the Oracle "start with, connect by" clause (http://docs.oracle.com/cd/B19306_01/server.102/b14200/querie...). Good to know.

Recursive CTEs are super amazing. One of the great underused features of SQL. Give me tall PostgreSQL and a WITH RECURSIVE to steer by, and i will happily take on any graph database.

Not promising i'll win, mind.

Ok, another reason to never use mysql.