If you later join that CTE against some other table, eliminating most of the rows fetched by the CTE, you will have wasted all the time for retrieving these rows.

This means that you might get a worse plan by using a CTE instead of a direct join.

It also means that you might convince the system to give you a much better plan by using a CTE (if you can limit the total amount of rows by the rows in the CTE instead of the tables you join it against).

Just keep that in mind when you're debugging query speed issues, but in general, CTEs are good for very readable queries that are much easier to understand and maintain than if you join inline.

The additional readability is for sure worth it to first try to solve the issue with a CTE and only if you run into above problem and if the performance is inacceptable to then convert the query to a more traditional (in a sense of "what the various open source RDBMs provided until two years ago when Postgres introduced CTEs") join.

Wait, no. I know for a fact that SQL Server will happily optimize across CTEs. They are effectively syntactic sugar, and handled much like a view or derived table.

So, either the SQL standard defines this optimization boundary and SQL Server ignores it, or this is an idiosyncrasy of the Postgres implementation, or this is an idiosyncrasy of some other implementation that you have assumed applies to all implementations.

I honestly don't know. I wouldn't mind a syntactic construct that did define optimization boundaries, but to my mind CTEs are not that construct.

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.

Of the only annoying features in postgres, imho. You have a large query and you try to break it up into smaller self-contained cte parts and voila, performance goes crap.

1. The standards define CTE's as being stable within a statement. I.e. they don't change as the query progresses.

2. The way this is implemented in PostgreSQL currently is to have CTE's as optimization fences.

Presumably some cross-CTE optimization may be added in the future but in usual cases, will be slowly added with a lot of care.

I think it was a partial truth. In many cases, you can't optimize because you need to produce the same results as if you had evaluated the CTE in full exactly once.

CTEs are also a convenient place to have an optimization fence, which are sometimes useful. Yes, this conflates the logical and the physical, but that's the way it is in postgres.

No. You can optimize them however you want and still comply with the SQL standard; you just can't produce different results.

Different results are really only a problem when the CTE is non-deterministic or has side effects. 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 suspect that you're mixing up stable and immutable. (Only immutable functions are evaluated at planning time.)

As I think about it I may have gotten it wrong. It might need to be in a subquery to fold in that way.

What sort of brain-dead language design would conflate a performance construct with a maintainability construct? Even C isn't that bad (though it sure comes close).

A data-storage language based on relational theory is a great idea. We should make a new one at some point.

http://www.dcs.warwick.ac.uk/~hugh/CHAP05.pdf

I mean, strictly speaking, that would make PostgreSQL a noSQL database ...

Someone already wrote a custom background listener that pretends to be mongodb, so I guess PostgreSQL is web-scale now ;-)

PS: I always cringe when I see this sort of syntax though: "FROM users, tasks, project"

Much clearer using explicit JOINS imo, especially if your goal is readability.

Explicit joins are much better for readability, though I find myself well not using them enough...

I prefer to list out your tables and then do your joins in the "where" clause. For 3+ tables and mixing inner & outer joins it's especially better. I think maybe this only looks better once you become really really good at reading and writing SQL though. Maybe it's also because I come from the Oracle world where it is the standard.

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).

(+) syntax is awesome =)

I probably should start using the join syntax. Old habits die hard.

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

It is not "semantically wrong": conceptually, you can compute a join by taking the Cartesian product and then applying the join predicates to the result, which is actually what the implicit join syntax suggests. Semantically, there is no difference between applying an (inner) join predicate and applying any other predicate. There is also no actual division between "building the set" and "filtering the result": different predicates might be applied in different orders, before or after different relations in the query have been accessed.

I started out writing joins in the where clause; i'm not sure why, probably because that's how it was done in the first book i read. I then worked with some old Oracle hands who also did it that way, probably because of tradition. A year or two ago, i rediscovered the explicit join notation, and switched over to it completely in a matter of days.

As you say, explicit joins are right because they separate which tables/which relationships and which rows. Those are not particularly distinct things in the pure relational algebra model, but they are fully distinct in practical use.

[...]

> 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.

  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"

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.

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.

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.

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 ;-)

This is because the most common API for accessing databases is wrong and doesn't support composition.

Imagine designing a general-purpose, rich API that has only one method. The method takes one giant string argument. Insane? Absolutely.

SQL is actually awesome and amazing, but many programmers hate it because their API for accessing it is horribly broken. The whole point of relational algebra is that it's... an algebra. None of which is usefully exposed if you're just mashing strings of SQL together.

Imagine if you developed your website without ever editing a *.js file directly, and instead just programmatically mashed together strings of JavaScript code and emitted them inline in the HTML wherever necessary. You'd probably end up generating much crummier JavaScript code, too. But should you blame JavaScript or your Web application framework for the fact that your client-side scripting is crummy? No, of course not. The real problem is that you're horribly misusing your tools.

http://hackage.haskell.org/package/esqueleto-1.2.4/docs/Data...

http://search.cpan.org/~einhverfr/PGObject-Simple-Role-0.51/...

(read the PGObject::Simple docs too, and both are on github).

The idea is that you write your sql in .sql files as user defined functions, and then run them as parameterized queries in your code using a declarative interface. No SQL in your Perl, and you get the benefit of encapsulating your database in an API that is not so tightly tied to your application (you can add new optional parameters without throwing off your application).

  DECLARE @Queue TABLE (QueueID INT)

  WITH q AS (
  		SELECT TOP (1) QueueID, StatusID, ModifiedDate
		FROM worker.Queue WITH (ROWLOCK, READPAST)
		WHERE StatusID=(SELECT TOP 1 s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status] IN ('Queued'))
		ORDER BY QueueDate ASC
  	)
  	UPDATE q SET 
		StatusID=(SELECT s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status]='Processing'), 
		StatusMessage='New Process',
		ModifiedDate=GETDATE()
	OUTPUT INSERTED.QueueID INTO @Queue

Likewise, your example can also be done with derived tables, which predate CTEs in MSSQL:

    UPDATE a SET col2 = 10
    OUTPUT INSERTED.key1 INTO @local_table (key1)
    FROM (SELECT TOP (1) * FROM table1 ORDER BY col1) a

    with foo as (
    insert ... values ...
    returning *
    ),
    bar as (
    insert ... select ... from foo
    returning *
    )
    select ... from foo, bar, baz ...

Note that I don't mean to argue against the point of this post... CTEs are very handy. It's just that one of the more commonly cited uses for them is to do graph stuff, and my experience trying to that, specifically, has not been positive.

CTEs and recursive queries are okay for many cases, but they can blow up your RDBMS temp space easily if you aren't careful. Especially useless is a non-materialized view based on them.

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 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.

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.

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.

https://www.mail-archive.com/sqlite-users@sqlite.org/msg8119...

Note: I have far more SQL Server experience than I do with any other DBMS.

I wouldn't call a 2X difference in performance a "no brainer". Far from it. It could be the difference between a visitor trying your product and him leaving b/c it takes too long to load.

A 100% increase in query time for a query that originally takes 50ms is neglectable, for a query that takes 500ms it will indeed make a vital difference.

And of course you can do it all inline. For transactional uses, that is probably best anyway. But there are clear use cases where inline queries are significantly less readable. Imagine subqueries nested 6 levels deep...CTEs allow you to flatten it such that you can linearly read the buildup of the query from top to bottom. It also allows the optimizer additional room for optimization, as it allows the optimizer to decide based on its analyze data whether the query is best run inline or by creating temp tables.

Not promising i'll win, mind.

Can you share an example of two queries that are equivalent where one is written using CTEs and it doesn't construct the same plan?

But: The optimizer will first fully resolve the CTE expression and only then join. If the join condition eliminates many of the rows selected in the CTE, this might indeed yield a much worse execution time than doing it inline.

This behaviour is documented on http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)

SQL Server's plan generation is not always perfect, but it's good 98 times out of 100.