35 ms·
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 ha
by 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 agoBad hints can always be dropped. Every system is prone to abuse, I don't think hints are too out there in this aspect
- 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 agoSame with lack of hints. We're not talking about simple queries which can be expected to just work. These were previously found to be badly optimised by the first engine, so we're in unknown territory either way.