3 ms·

> No hints would be excruciating, I don't care how good the optimizer is, sometimes I know better and sometime the optimizer is brain dead. PostgreSQL treats p

> No hints would be excruciating, I don't care how good the optimizer is, sometimes I know better and sometime the optimizer is brain dead. PostgreSQL treats poor planning from the optimizer as a bug, not something that humans should be forced to work around. File bug reports on the mailing list, don't hack up your SQL.

munk-a 7y ago
I both like and am annoyed by this. I like it because they actually do legitimately treat it as a bug and are quite responsive in publishing fixes - I'm annoyed by this because their scale of responsiveness is (for very valid reasons including measuring how a proposed fix will effect other cases and the cost of relying on mostly volunteer labour) quite a bit behind the scale of time I need a fix in. Using CTEs you are still able to hack the optimizer and while in pg12 they're making that fence optional it is remaining an option, that seems to be their primary conceit to needing to bludgeon an underperformant query - though, per their policy, queries that remain underperformant are getting rarer as they fix up the planner more and more.
btilly 7y ago
Yes, yes. PostgreSQL developers don't care about messy real world use cases. News at 11. I reiterate that no optimizer is perfect. Which means that if PostgreSQL redid some obscure statistic and a common query just switched to a far worse query plan, you've got a disaster and no easy way to fix it. Even if you know the right query plan, there is no easy way to fix it. Similarly "this query worked fine in development and staging, but mysteriously sucks in production" is not what you want to learn while deploying to production. Yes, I've seen both failure modes. No it wasn't pretty. And no, "File a bug report, wait for the next version of PostgreSQL to have a fix" is not an acceptable action plan. In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough. Or everybody is upset because the new plan is worse. I don't care if 99% of the time it turns out well and nobody notices, that 1% keeps people up at night. The top missing feature in PostgreSQL is this. If the query plan is good, allow the query plan to get locked and not change. Predictability beats perfection in production environments.
- d33 7y ago
  I see this one downvoted but I don't see one. Could somebody discuss with this instead? The tone doesn't sound repulsive to me.
- derefr 7y ago
  > In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough. This assumes a use-case where there is a "site" (i.e. a business layer in front of PG that can abstract away deficiencies in the query plan by adding e.g. caching.) If you're directly working with PG, interactively writing OLAP reporting queries, you can have "working queries" that take ~300s (still practical enough to build your report) switch to taking milliseconds after an autovacuum (if, for example, a table being joined against was recently populated via ETL and hadn't been vacuumed yet.) People do notice that.
SigmundA 7y ago
Or I could use a hint to fix my query now and file a bug so it can be fixed at some unspecified point in the future so long as the developers agree I have a valid use case. Sorry I have played that game before with SQL server back when it didn't have good hinting, having to fool the optimizer into getting a sane plan through trial and error and being at the mercy of waiting for a new release to fix the issue. Yes PG is better being open source, but fundamentally having dealt with this for many years relying on the optimizer with no manual override when needed is highly counter productive. There are way too many edge cases for any optimizer. As a human I can easily see the proper order of operations and indexes to use if only I could just tell that to the system. Most of the time you don't need to do this but its critical when you do and the system needs a way to do it.

munk-a 7y ago

I both like and am annoyed by this. I like it because they actually do legitimately treat it as a bug and are quite responsive in publishing fixes - I'm annoyed by this because their scale of responsiveness is (for very valid reasons including measuring how a proposed fix will effect other cases and the cost of relying on mostly volunteer labour) quite a bit behind the scale of time I need a fix in. Using CTEs you are still able to hack the optimizer and while in pg12 they're making that fence optional it is remaining an option, that seems to be their primary conceit to needing to bludgeon an underperformant query - though, per their policy, queries that remain underperformant are getting rarer as they fix up the planner more and more.

btilly 7y ago

Yes, yes. PostgreSQL developers don't care about messy real world use cases. News at 11. I reiterate that no optimizer is perfect. Which means that if PostgreSQL redid some obscure statistic and a common query just switched to a far worse query plan, you've got a disaster and no easy way to fix it. Even if you know the right query plan, there is no easy way to fix it. Similarly "this query worked fine in development and staging, but mysteriously sucks in production" is not what you want to learn while deploying to production. Yes, I've seen both failure modes. No it wasn't pretty. And no, "File a bug report, wait for the next version of PostgreSQL to have a fix" is not an acceptable action plan. In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough. Or everybody is upset because the new plan is worse. I don't care if 99% of the time it turns out well and nobody notices, that 1% keeps people up at night. The top missing feature in PostgreSQL is this. If the query plan is good, allow the query plan to get locked and not change. Predictability beats perfection in production environments.

d33 7y ago
I see this one downvoted but I don't see one. Could somebody discuss with this instead? The tone doesn't sound repulsive to me.
derefr 7y ago
> In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough. This assumes a use-case where there is a "site" (i.e. a business layer in front of PG that can abstract away deficiencies in the query plan by adding e.g. caching.) If you're directly working with PG, interactively writing OLAP reporting queries, you can have "working queries" that take ~300s (still practical enough to build your report) switch to taking milliseconds after an autovacuum (if, for example, a table being joined against was recently populated via ETL and hadn't been vacuumed yet.) People do notice that.

SigmundA 7y ago

Or I could use a hint to fix my query now and file a bug so it can be fixed at some unspecified point in the future so long as the developers agree I have a valid use case. Sorry I have played that game before with SQL server back when it didn't have good hinting, having to fool the optimizer into getting a sane plan through trial and error and being at the mercy of waiting for a new release to fix the issue. Yes PG is better being open source, but fundamentally having dealt with this for many years relying on the optimizer with no manual override when needed is highly counter productive. There are way too many edge cases for any optimizer. As a human I can easily see the proper order of operations and indexes to use if only I could just tell that to the system. Most of the time you don't need to do this but its critical when you do and the system needs a way to do it.