9 ms·

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

135 points by craigkerstiens 13y ago

vladsanchez 13y ago
I'm using them since 8.4! Thanks for reminding everyone! =D
poolpool 13y ago
I don't know about postgres but joining CTEs in Sql Server is a recipe for blowing up your tempdb or at the very least waiting for ages for a trivial query to finish.
- endianswap 13y ago
  You must be doing something wrong then. MSSQL resolves the entire statement (CTEs plus final query) into a single query that it optimizes as a whole. 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?
- bbatchelder 13y ago
  I have not seen this behavior, and I use the fuck out of CTEs in SQL Server.
- pilif 13y ago
  Postgres has no problems joining against any sized CTEs - at least that's what I'm seeing here with a considerably sized dataset (600 GB raw table size). 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 http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)
- taspeotis 13y ago
  Sounds like you should look at the execution plan of the "trivial query" and see what's up with your database schema. SQL Server's plan generation is not always perfect, but it's good 98 times out of 100.
mindcrime 13y ago
CTEs in general are fine, just beware of using recursive CTEs to model graphs - and do graph operations - inside Postgres. My experience has been that the performance is pretty abysmal in that case, for even fairly small graphs. IMO, if you find yourself doing a lot of "graph stuff" just go ahead and store the graph in Neo4J or Titan or something. 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.
- craigkerstiens 13y ago
  Recursive CTEs can absolutely be more performance intensive, and even non recursive CTEs can be an optimization bottleneck. However from what I've seen with non recursive CTEs is that you're looking at a 1.5-2x difference in performance (in the bad cases) versus orders of magnitude. For making SQL more readable and shareable this seems a small price to pay, its much the same that people use higher level languages rather than dropping down to C for everything they need to do.
  - einhverfr 13y ago
    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.
adwf 13y ago
I love using small CTE's to help with the hassle of the alternative - creating temp tables and then cleaning them up. They can be particularly useful for data modification, as you can't ordinarily do a recursive delete or insert all that easily. 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.
- craigkerstiens 13y ago
  I agree on the explicit joins and called myself out on this in an older post - http://www.craigkerstiens.com/2013/05/06/my-sql-bad-habits/ http://www.craigkerstiens.com/2013/05/06/my-sql-bad-habits/ Explicit joins are much better for readability, though I find myself well not using them enough...
- socialist_coder 13y ago
  Completely disagree. All the database developer "experts" I know of (including myself) hate the explicit join syntax. 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.
  - matwood 13y ago
    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>
    - twic 13y ago
      Ditto. 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.
socialist_coder 13y ago
All SQL databases have this feature, it's nothing specific to PostgreSQL. I don't like the "with" syntax though, I just do it all inline. select * from ( select * from blah 1 ) a, ( select * from blah 2 ) b where a.x = b.x;
- adwf 13y ago
  The advantage of CTEs is they have the explicit "WITH RECURSIVE" option. Whereas an aliased SELECT statement can't refer to itself as far as I know.
  - socialist_coder 13y ago
    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/queries003.htm#i2060615 http://docs.oracle.com/cd/B19306_01/server.102/b14200/querie...). Good to know.
  - twic 13y ago
    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.
- saosebastiao 13y ago
  Find me CTEs in mysql or sqlite. I dare you. 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.
  - socialist_coder 13y ago
    Ok, another reason to never use mysql.
pilif 13y ago
One thing to keep in mind: CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs. So CTEs have to always be resolved first. 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.
- colanderman 13y ago
  CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs. 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).
  - FooBarWidget 13y ago
    Yeah, SQL is clearly braindead and has seen its best days. Let's should mass revolt and switch to NoSQL! </sarcasm>
    - Pxtl 13y ago
      I, on the other hand, am perfectly capable of believing that SQL is a pretty terrible language that merely persists because every attempt to replace it has been even worse because they threw out the baby with the bath-water. A data-storage language based on relational theory is a great idea. We should make a new one at some point.
buckbova 13y ago
mssql has a lot of "tricks" associated with CTE's. Using the OUTPUT clause on updates is one of them.. Here's some queue code using a cte to grab the top one by queued date. 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
- pradocchia 13y ago
  The OUTPUT clause in MSSQL doesn't require a CTE, and can be used with INSERT, DELETE and MERGE in addition to UPDATE. 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
- ddebernardy 13y ago
  Looks like Postgres' RETURNING clause... The latter works with inserts and updates, too, e.g.: with foo as ( insert ... values ... returning * ), bar as ( insert ... select ... from foo returning * ) select ... from foo, bar, baz ...
ef4 13y ago
> For some reason most people throw out principles we follow in other languages such as... composability just for SQL 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.
- bunderbunder 13y ago
  I'll one up you on that. . . the common approach of treating the database as if it were an API that you shove string arguments into is wrong. 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.
- dllthomas 13y ago
  Esqueleto is a pretty interesting take at putting together SQL without messing with strings. Doesn't quite expose the relational algebra in its full generality, but I've been finding it quite usable. http://hackage.haskell.org/package/esqueleto-1.2.4/docs/Database-Esqueleto.html http://hackage.haskell.org/package/esqueleto-1.2.4/docs/Data...
- einhverfr 13y ago
  Part of the reason I wrote this: http://search.cpan.org/~einhverfr/PGObject-Simple-Role-0.51/lib/PGObject/Simple/Role.pm 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).
AznHisoka 13y ago
"In most cases I have seen performance differences smaller than a 2X difference, this tradeoff for readability is a nobrainer as far as I’m concerned. " 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.
- risratorn 13y ago
  I'd agree but it really depends to be honest. 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.
seanwoods 13y ago
The topic of CTEs was recently discussed (with much vigor) on the SQLite users mailing list: https://www.mail-archive.com/sqlite-users@sqlite.org/msg81199.html https://www.mail-archive.com/sqlite-users@sqlite.org/msg8119...
- fphhotchips 13y ago
  "as long as the 'RECURSIVE' part of CTE is ignored."... To me, the recursive part of CTEs is half the point! All the other stuff is syntactic sugar that can be done elsewhere, but recursion is a function that I'm unable to find another method for elsewhere in the language. Note: I have far more SQL Server experience than I do with any other DBMS.

135 points by craigkerstiens 13y ago

I'm using them since 8.4! Thanks for reminding everyone! =D

I don't know about postgres but joining CTEs in Sql Server is a recipe for blowing up your tempdb or at the very least waiting for ages for a trivial query to finish.

endianswap 13y ago
You must be doing something wrong then. MSSQL resolves the entire statement (CTEs plus final query) into a single query that it optimizes as a whole. 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?
bbatchelder 13y ago
I have not seen this behavior, and I use the fuck out of CTEs in SQL Server.
pilif 13y ago
Postgres has no problems joining against any sized CTEs - at least that's what I'm seeing here with a considerably sized dataset (600 GB raw table size). 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 http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)
taspeotis 13y ago
Sounds like you should look at the execution plan of the "trivial query" and see what's up with your database schema. SQL Server's plan generation is not always perfect, but it's good 98 times out of 100.

mindcrime 13y ago

CTEs in general are fine, just beware of using recursive CTEs to model graphs - and do graph operations - inside Postgres. My experience has been that the performance is pretty abysmal in that case, for even fairly small graphs. IMO, if you find yourself doing a lot of "graph stuff" just go ahead and store the graph in Neo4J or Titan or something. 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.

craigkerstiens 13y ago
Recursive CTEs can absolutely be more performance intensive, and even non recursive CTEs can be an optimization bottleneck. However from what I've seen with non recursive CTEs is that you're looking at a 1.5-2x difference in performance (in the bad cases) versus orders of magnitude. For making SQL more readable and shareable this seems a small price to pay, its much the same that people use higher level languages rather than dropping down to C for everything they need to do.
- einhverfr 13y ago
  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.

adwf 13y ago

I love using small CTE's to help with the hassle of the alternative - creating temp tables and then cleaning them up. They can be particularly useful for data modification, as you can't ordinarily do a recursive delete or insert all that easily. 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.

craigkerstiens 13y ago
I agree on the explicit joins and called myself out on this in an older post - http://www.craigkerstiens.com/2013/05/06/my-sql-bad-habits/ http://www.craigkerstiens.com/2013/05/06/my-sql-bad-habits/ Explicit joins are much better for readability, though I find myself well not using them enough...
socialist_coder 13y ago
Completely disagree. All the database developer "experts" I know of (including myself) hate the explicit join syntax. 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.
- matwood 13y ago
  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>
  - twic 13y ago
    Ditto. 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.

socialist_coder 13y ago

All SQL databases have this feature, it's nothing specific to PostgreSQL. I don't like the "with" syntax though, I just do it all inline. select * from ( select * from blah 1 ) a, ( select * from blah 2 ) b where a.x = b.x;

adwf 13y ago
The advantage of CTEs is they have the explicit "WITH RECURSIVE" option. Whereas an aliased SELECT statement can't refer to itself as far as I know.
- socialist_coder 13y ago
  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/queries003.htm#i2060615 http://docs.oracle.com/cd/B19306_01/server.102/b14200/querie...). Good to know.
- twic 13y ago
  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.
saosebastiao 13y ago
Find me CTEs in mysql or sqlite. I dare you. 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.
- socialist_coder 13y ago
  Ok, another reason to never use mysql.

pilif 13y ago

One thing to keep in mind: CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs. So CTEs have to always be resolved first. 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.

colanderman 13y ago
CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs. 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).
- FooBarWidget 13y ago
  Yeah, SQL is clearly braindead and has seen its best days. Let's should mass revolt and switch to NoSQL! </sarcasm>
  - Pxtl 13y ago
    I, on the other hand, am perfectly capable of believing that SQL is a pretty terrible language that merely persists because every attempt to replace it has been even worse because they threw out the baby with the bath-water. A data-storage language based on relational theory is a great idea. We should make a new one at some point.

buckbova 13y ago

mssql has a lot of "tricks" associated with CTE's. Using the OUTPUT clause on updates is one of them.. Here's some queue code using a cte to grab the top one by queued date. 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

pradocchia 13y ago
The OUTPUT clause in MSSQL doesn't require a CTE, and can be used with INSERT, DELETE and MERGE in addition to UPDATE. 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
ddebernardy 13y ago
Looks like Postgres' RETURNING clause... The latter works with inserts and updates, too, e.g.: with foo as ( insert ... values ... returning * ), bar as ( insert ... select ... from foo returning * ) select ... from foo, bar, baz ...

ef4 13y ago

> For some reason most people throw out principles we follow in other languages such as... composability just for SQL 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.

bunderbunder 13y ago
I'll one up you on that. . . the common approach of treating the database as if it were an API that you shove string arguments into is wrong. 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.
dllthomas 13y ago
Esqueleto is a pretty interesting take at putting together SQL without messing with strings. Doesn't quite expose the relational algebra in its full generality, but I've been finding it quite usable. http://hackage.haskell.org/package/esqueleto-1.2.4/docs/Database-Esqueleto.html http://hackage.haskell.org/package/esqueleto-1.2.4/docs/Data...
einhverfr 13y ago
Part of the reason I wrote this: http://search.cpan.org/~einhverfr/PGObject-Simple-Role-0.51/lib/PGObject/Simple/Role.pm 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).

AznHisoka 13y ago

"In most cases I have seen performance differences smaller than a 2X difference, this tradeoff for readability is a nobrainer as far as I’m concerned. " 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.

risratorn 13y ago
I'd agree but it really depends to be honest. 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.

seanwoods 13y ago

The topic of CTEs was recently discussed (with much vigor) on the SQLite users mailing list: https://www.mail-archive.com/sqlite-users@sqlite.org/msg81199.html https://www.mail-archive.com/sqlite-users@sqlite.org/msg8119...

fphhotchips 13y ago
"as long as the 'RECURSIVE' part of CTE is ignored."... To me, the recursive part of CTEs is half the point! All the other stuff is syntactic sugar that can be done elsewhere, but recursion is a function that I'm unable to find another method for elsewhere in the language. Note: I have far more SQL Server experience than I do with any other DBMS.