4 ms·

If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or functio

If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)

HCIdivision17 11y ago
For many of my use cases, this is a handy workflow. CTEs are just perfect for iteratively building up a query to what I need. Once it's done, I can either package the whole shebang or break out the really useful bits into views. Notably, MSSQL does optimize across CTEs, so there's not as much pressure to break out to views on that platform. And if you're feeling especially perverse, you can make a CTE a view, which sorta... sporks the problem.
atwebb 11y ago
Disagree completely, CTEs and local temp/variable tables can serve an important purpose. I do think that a fair amount of CTEs can just be inner queries (not joins, well, joined queries...).
paol 11y ago
Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at. My rule is to refactor into a view when and only when it's going to be reused. > Postgres optimizes across views, but not across CTEs Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope.
- saosebastiao 11y ago
  > Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope. I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast it performed.
  - paol 11y ago
    It's a bizarre side-effect of lacking query hints. This is my interpretation of the sequence of events: 1. Postgres will never implement query hints, because query hints are evil 2. In the real world people need them anyway, so they (ab)use implementation details that offer some control over the query plan (WITH is one example, see also OFFSET 0 and other hacks) 3. Everyone starts relying on these implementation details, e.g. you'll find people actually recommending this as a way to optimize queries in the mailing lists 4. Now when someone asks to fix the planner, someone else will point out this will break all the queries that have been "optimized" by depending on the old behavior Postgres is generally a fairly sane project though, so I have some confidence they will come to their senses. Eventually.
  - jeltz 11y ago
    For the record, I have also read a quite a few people on the mailing lists strongly disagreeing with those claims.
AlisdairO 11y ago
True in some cases, but I find CTEs invaluable for simplifying my ad-hoc/personal queries.
wvenable 11y ago
Probably 30% of my queries are complex enough to have a CTE but none of them should be views. There is no reuse and views are opaque and exist in a single namespace. Views have their purpose but this isn't it.
- dragonwriter 11y ago
  > There is no reuse and views are opaque and exist in a single namespace. I'm not sure what you mean by "opaque"; they encapsulate logic, which is a plus. On the "single namespace" thing, that sort of depends on the RDBMS; e.g., in postgres, within a single DB, one can have multiple schemas, and there is nothing stopping a view in one schema from referencing objects in another schema, so views for one database may exist in separate schemas, which act as namespaces. Other DBs offer equivalent functionality, though the names of structures may be different.

HCIdivision17 11y ago

For many of my use cases, this is a handy workflow. CTEs are just perfect for iteratively building up a query to what I need. Once it's done, I can either package the whole shebang or break out the really useful bits into views. Notably, MSSQL does optimize across CTEs, so there's not as much pressure to break out to views on that platform. And if you're feeling especially perverse, you can make a CTE a view, which sorta... sporks the problem.

atwebb 11y ago

Disagree completely, CTEs and local temp/variable tables can serve an important purpose. I do think that a fair amount of CTEs can just be inner queries (not joins, well, joined queries...).

paol 11y ago

Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at. My rule is to refactor into a view when and only when it's going to be reused. > Postgres optimizes across views, but not across CTEs Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope.

saosebastiao 11y ago
> Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope. I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast it performed.
- paol 11y ago
  It's a bizarre side-effect of lacking query hints. This is my interpretation of the sequence of events: 1. Postgres will never implement query hints, because query hints are evil 2. In the real world people need them anyway, so they (ab)use implementation details that offer some control over the query plan (WITH is one example, see also OFFSET 0 and other hacks) 3. Everyone starts relying on these implementation details, e.g. you'll find people actually recommending this as a way to optimize queries in the mailing lists 4. Now when someone asks to fix the planner, someone else will point out this will break all the queries that have been "optimized" by depending on the old behavior Postgres is generally a fairly sane project though, so I have some confidence they will come to their senses. Eventually.
- jeltz 11y ago
  For the record, I have also read a quite a few people on the mailing lists strongly disagreeing with those claims.

AlisdairO 11y ago

True in some cases, but I find CTEs invaluable for simplifying my ad-hoc/personal queries.

wvenable 11y ago

Probably 30% of my queries are complex enough to have a CTE but none of them should be views. There is no reuse and views are opaque and exist in a single namespace. Views have their purpose but this isn't it.

dragonwriter 11y ago
> There is no reuse and views are opaque and exist in a single namespace. I'm not sure what you mean by "opaque"; they encapsulate logic, which is a plus. On the "single namespace" thing, that sort of depends on the RDBMS; e.g., in postgres, within a single DB, one can have multiple schemas, and there is nothing stopping a view in one schema from referencing objects in another schema, so views for one database may exist in separate schemas, which act as namespaces. Other DBs offer equivalent functionality, though the names of structures may be different.