4 ms·

If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large th

If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large third party systems. I never saw widespread abuse of hints. Yet they were immensely helpful during development and troubleshooting. I put perhaps two queries into production with hints over 10+ years. No one ever had a reason to complain about either. There are no perfect query planners. There is no perfect analysis. The notion that one must submit entirely to the mercy of PostgreSQL's no-hints dogma causes me to harbor some resentment. Fortunately you can frequently abuse CTEs to achieve a desired access pattern because (until recently) CTEs were an "optimization fence." But I've also resorted to creating functions and other hacks. So I conclude the policy is simply wrong headed; there is no legitimate reason to fear hint abuse and the premise that hints aren't necessary is false.

hibikir 7y ago
While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers, a vast majority of hinted queries were very wrong, leading to months of developers rebuilding every query, despite having no schema changes. So yes, a lot of people in the industry have been burned by hints to make the Postgres perspective understandable. At the same time, this doesn't make it good: Postgres' default settings over the years have lead to specific kinds of tables requiring extra love and care to make the query planner not do silly things. The most traditional failure case being a transaction table with an always increasing timestamp, where a vast majority of queries only care about today: The traditional thing to do was to convince Postgres that yes, this table needs very frequent stats recalculation, as to make it learn that there are more than 3 rows today, so nested loops will not do. Whether the Postgres quirks are better or worse than hint hell, I am still not sure of: A competent engineer can handle things either way.
- raverbashing 7y ago
  Bad hints can always be dropped. Every system is prone to abuse, I don't think hints are too out there in this aspect
  - asah 7y ago
    Came here to say this. We are not (all) children.
  - hinkley 7y ago
    > leading to months of developers rebuilding every query, despite having no schema changes. But doing so is not free. You may be on a completely different scale than they were.
- viraptor 7y ago
  > In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already. It sounds like the lesson should be "with large enough system, plan for extended time for query rewriting if you plan to replace your db engine", rather than anything about hints themselves.
  - deleted 7y ago
    [deleted]
  - ric2b 7y ago
    > If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? The hints can introduce wrong assumptions to the planner, which can lead to worse performance than if those wrong assumptions weren't there.
    - viraptor 7y ago
scosman 7y ago
Related, after a major version upgrade it doesn't automatically run an ANALYZE on every table. Without this the query planner can't properly plan. It treats a billion row table the same as a 20 row table until you think to manually kick off the magic command.
hobs 7y ago
I see hint abuse constantly and everywhere in enterprise databases, it's really more about the lineage you work with.
- topspin 7y ago
  > it's really more about the lineage you work with. (disclaimer: I'm not entirely certain what 'lineage' means here, but I'll assume it refers to the nature of the products for which database schemas are created and the quality of the development process.) In my case the lineage was two unrelated ERPs at consecutive employers and a variety of in-house applications. I don't recall ever seeing a hint in either ERP except in some "one off" upgrade operations. Both employers had development guidelines and peer review processes for bespoke work. They did not explicitly preclude hints but if you had been foolish enough to offer slapdash work -- abusive use of hints, for instance -- you wouldn't get very far. That was my experience with hints. I don't doubt there are reckless people who abuse them. I just resent being denied an affordance because they exist.
jaakl 7y ago
Few years back we had nation-wide panic that Parliament election results were unavailable for hours after closing polls due to “IT issue”. Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late. Automatic query planner just failed and all the smartest experts in country could do was to wait until it self-heals. Imagine national TV screening for hours “no results available” and You giving updates to the PM every 5 minutes. And all you know that your most important DB in country cannot figure it out... True story
- bragh 7y ago
  Please stop spreading FUD: https://www.postgresql.org/message-id/flat/201103091459.p29Ex3405229%40momjian.us https://www.postgresql.org/message-id/flat/201103091459.p29E...
- yjftsjthsd-h 7y ago
  > Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late ...then in no useful sense was it load tested.
DrJokepu 7y ago
A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.
- topspin 7y ago
  Indeed. I too have resorted to implementing my own plan using temporary tables. Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.
  - radiowave 7y ago
    Yes, I regard temporary tables as a last-resort. Although, a sometimes very useful thing that this approach has going for it is that you can add indexes to those temporary tables which are much more specialized than what could be achieved by working solely with the indexes of the source tables, no matter how sophisticated the query plan.
  - DrJokepu 7y ago
    Maybe it’s a subjective thing, for me at least I think breaking up a larger problem into a series of smaller problems tends to make it easier to understand what’s going on.
orthoxerox 7y ago
I once couldn't convince Oracle planner to use the index at all. It was an index on the one-char status column and all the query had to do was to return the count of unauthorized rows. Almost always 100% of the rows were authorized, so no matter how many times you ran the analyzer on the table the planner remained convinced that the index was useless and opted for a full table scan instead, wasting minutes. I had to nail down the index with a hint.
- blattimwind 7y ago
  In postgres, queries involving aggregates on joins generally seem to result in join-then-aggregate, even if the join is entirely foreign-key-independent of the aggregate.
foxhill 7y ago
so, i absolutely agree. i do find it odd that no one has mentioned pg_hint_plan[1]. we use it, and it is great. [1]: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html
SigmundA 7y ago
Completely agree coming from years of experience with MSSQL using it before it had good hinting, its very necessary. I don't care how good your optimizer is, its not going to replace a human that knows what they are doing anytime soon. I try real hard not to use hints and most of the time you don't, but sometimes you just do. To not have it will simply make you find whatever hacky way there is to beat the optimizer into submission to get the job done. Its funny to see similar complaints about the V8 jitter now app developers get to experience a black box optimizer making different brain dead choices in production vs development environments.

hibikir 7y ago

While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers, a vast majority of hinted queries were very wrong, leading to months of developers rebuilding every query, despite having no schema changes. So yes, a lot of people in the industry have been burned by hints to make the Postgres perspective understandable. At the same time, this doesn't make it good: Postgres' default settings over the years have lead to specific kinds of tables requiring extra love and care to make the query planner not do silly things. The most traditional failure case being a transaction table with an always increasing timestamp, where a vast majority of queries only care about today: The traditional thing to do was to convince Postgres that yes, this table needs very frequent stats recalculation, as to make it learn that there are more than 3 rows today, so nested loops will not do. Whether the Postgres quirks are better or worse than hint hell, I am still not sure of: A competent engineer can handle things either way.

raverbashing 7y ago
Bad hints can always be dropped. Every system is prone to abuse, I don't think hints are too out there in this aspect
- asah 7y ago
  Came here to say this. We are not (all) children.
- hinkley 7y ago
  > leading to months of developers rebuilding every query, despite having no schema changes. But doing so is not free. You may be on a completely different scale than they were.
viraptor 7y ago
> In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already. It sounds like the lesson should be "with large enough system, plan for extended time for query rewriting if you plan to replace your db engine", rather than anything about hints themselves.
- deleted 7y ago
  [deleted]
- ric2b 7y ago
  > If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? The hints can introduce wrong assumptions to the planner, which can lead to worse performance than if those wrong assumptions weren't there.
  - viraptor 7y ago

scosman 7y ago

Related, after a major version upgrade it doesn't automatically run an ANALYZE on every table. Without this the query planner can't properly plan. It treats a billion row table the same as a 20 row table until you think to manually kick off the magic command.

hobs 7y ago

I see hint abuse constantly and everywhere in enterprise databases, it's really more about the lineage you work with.

topspin 7y ago
> it's really more about the lineage you work with. (disclaimer: I'm not entirely certain what 'lineage' means here, but I'll assume it refers to the nature of the products for which database schemas are created and the quality of the development process.) In my case the lineage was two unrelated ERPs at consecutive employers and a variety of in-house applications. I don't recall ever seeing a hint in either ERP except in some "one off" upgrade operations. Both employers had development guidelines and peer review processes for bespoke work. They did not explicitly preclude hints but if you had been foolish enough to offer slapdash work -- abusive use of hints, for instance -- you wouldn't get very far. That was my experience with hints. I don't doubt there are reckless people who abuse them. I just resent being denied an affordance because they exist.

jaakl 7y ago

Few years back we had nation-wide panic that Parliament election results were unavailable for hours after closing polls due to “IT issue”. Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late. Automatic query planner just failed and all the smartest experts in country could do was to wait until it self-heals. Imagine national TV screening for hours “no results available” and You giving updates to the PM every 5 minutes. And all you know that your most important DB in country cannot figure it out... True story

bragh 7y ago
Please stop spreading FUD: https://www.postgresql.org/message-id/flat/201103091459.p29Ex3405229%40momjian.us https://www.postgresql.org/message-id/flat/201103091459.p29E...
yjftsjthsd-h 7y ago
> Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late ...then in no useful sense was it load tested.

DrJokepu 7y ago

A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.

topspin 7y ago
Indeed. I too have resorted to implementing my own plan using temporary tables. Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.
- radiowave 7y ago
  Yes, I regard temporary tables as a last-resort. Although, a sometimes very useful thing that this approach has going for it is that you can add indexes to those temporary tables which are much more specialized than what could be achieved by working solely with the indexes of the source tables, no matter how sophisticated the query plan.
- DrJokepu 7y ago
  Maybe it’s a subjective thing, for me at least I think breaking up a larger problem into a series of smaller problems tends to make it easier to understand what’s going on.

orthoxerox 7y ago

I once couldn't convince Oracle planner to use the index at all. It was an index on the one-char status column and all the query had to do was to return the count of unauthorized rows. Almost always 100% of the rows were authorized, so no matter how many times you ran the analyzer on the table the planner remained convinced that the index was useless and opted for a full table scan instead, wasting minutes. I had to nail down the index with a hint.

blattimwind 7y ago
In postgres, queries involving aggregates on joins generally seem to result in join-then-aggregate, even if the join is entirely foreign-key-independent of the aggregate.

foxhill 7y ago

so, i absolutely agree. i do find it odd that no one has mentioned pg_hint_plan[1]. we use it, and it is great. [1]: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html

SigmundA 7y ago

Completely agree coming from years of experience with MSSQL using it before it had good hinting, its very necessary. I don't care how good your optimizer is, its not going to replace a human that knows what they are doing anytime soon. I try real hard not to use hints and most of the time you don't, but sometimes you just do. To not have it will simply make you find whatever hacky way there is to beat the optimizer into submission to get the job done. Its funny to see similar complaints about the V8 jitter now app developers get to experience a black box optimizer making different brain dead choices in production vs development environments.