6 ms·

Apologies for being negative, but... Is it just me or does it look like a bad abstraction trying to solve a problem that was created by SQL not being direct eno

by d33 7y ago

Apologies for being negative, but... Is it just me or does it look like a bad abstraction trying to solve a problem that was created by SQL not being direct enough in the first place? I had been working with a couple billion-row-scale PostgreSQL setups and trying to please query planner was sometimes a project in itself. Many times I just wished I could specify an algorithm on my own instead of having SQL interpreted as one plan for one variable, but as another if I replace it. Did anyone else have a similar impression?

SigmundA 7y ago
Yes I run into this a lot in SQL server but SQL server does let you do hints which in my understanding PG does not. 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.
- snuxoll 7y ago
  > 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.
- makoz 7y ago
  Out of curiosity have you looked at pg_hint_plan? http://pghintplan.osdn.jp/pg_hint_plan.html http://pghintplan.osdn.jp/pg_hint_plan.html Or is it not advanced enough?
  - SigmundA 7y ago
    I did not know about this and as usual PG has a plugin to the rescue. Looks like it does whats needed although having to specify in a special kind of comment is not as ergonomic as just some keywords in the language itself.
btown 7y ago
The flip side of this is that as your data grows and your schema changes, sometimes a query planner can do better than an optimization based on old, no-longer-true assumptions. Sometimes SQL is the right abstraction, and this kind of tool then becomes very useful.

by d33 7y ago

SigmundA 7y ago

Yes I run into this a lot in SQL server but SQL server does let you do hints which in my understanding PG does not. 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.

snuxoll 7y ago
> 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.
makoz 7y ago
Out of curiosity have you looked at pg_hint_plan? http://pghintplan.osdn.jp/pg_hint_plan.html http://pghintplan.osdn.jp/pg_hint_plan.html Or is it not advanced enough?
- SigmundA 7y ago
  I did not know about this and as usual PG has a plugin to the rescue. Looks like it does whats needed although having to specify in a special kind of comment is not as ergonomic as just some keywords in the language itself.

btown 7y ago

The flip side of this is that as your data grows and your schema changes, sometimes a query planner can do better than an optimization based on old, no-longer-true assumptions. Sometimes SQL is the right abstraction, and this kind of tool then becomes very useful.