4 ms·

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 queri

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

by paol 11y ago

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.