3 ms·

Using hints is typically a sign of failure. The DB optimiser should typically not need them.

_a_a_a_ 3y ago
Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?
- mickeyp 3y ago
  You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
  - _a_a_a_ 3y ago
    Poster said oracle not PG
    - mardifoufs 3y ago
      Total db noob here: is Oracle's query planner better? What's the difference between the two?
      - _a_a_a_ 3y ago
        This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
    - mickeyp 3y ago
      Oracle has query hints; pg does not. Query planner hinters are useful. That's my point.
- zaptheimpaler 3y ago
  No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.
  - _a_a_a_ 3y ago
    You're right, I meant a failure of the DB/optimiser not the person. Thanks. No they're not perfect but I need them rarely with MSSQL > An optimizer will quickly run into NP-hard problems like join ordering As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.
mannyv 3y ago
Totally untrue...but adding hints makes your queries dependent on current performance. Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system. Even databases are general-purpose machines. That's the whole point of adding indexes, etc. I mean, has there ever been a database that auto-adds indexes based on profiler/optimizer feedback? (To answer my own question, apparently Azure SQL does).
- mnahkies 3y ago
  It's not automatically creating them AFAIK, but alloydb has an index suggestion tool https://cloud.google.com/alloydb/docs/use-index-advisor https://cloud.google.com/alloydb/docs/use-index-advisor Generally this seems like a nice balance to me - watch real queries, look for expensive plans and sniff out likely missing indexes based on the data distribution, but give the DBA final say on whether it's a good idea
- _a_a_a_ 3y ago
  > adding hints makes your queries dependent on current performance I don't understand this, can you explain. > Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system That's inaccurate. As a DBA I can understand the system better at higher level, but the database has statistics which gives it typically better understanding of the data distribution at a lower level. Indeed I could get that information and feed it into the query plan via hints, but that's going to be an enormous amount of my time, and I would have to do it every time the query is run whereas the database can keep an eye on the statistics as it varies and rebuild the query over time. Equally, if an index is added you can expect the database to start using it immediately without adding hints. Etc I am a fairly(?) skilled DBA who has a reasonable idea of what goes on underneath the hood, I do have some idea what I'm talking about.
- MenhirMike 3y ago
  > (To answer my own question, apparently Azure SQL does) Which is fun when you add a future schema migration that drops a column, test in locally and in some test cases, and then see it bomb in production because the column has an Index that you weren't aware of. (That's an easy thing to solve though, but definitely one of those cases where "The database is messing with its schema on its own" does do funny things)
GordonS 3y ago
In a perfect world, I wouldn't disagree; but we don't live in a perfect world where everything always goes your way. Sometimes the planner needs a little help, and that's OK.
- _a_a_a_ 3y ago
  I did say 'typically'! Yes, agreed, but it should rarely need help. Also hints are oh so often jammed in by newbies. I strongly get the impression use of hints is inversely correlated with experience/knowledge.
  - GordonS 3y ago
    I'm a greybeard you cheeky scamp ;) I very rarely used hints in my more database'y days - but every so often, they were needed, and often to tell the database something that seemed screamingly obvious to me. I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.
    - _a_a_a_ 3y ago
      We are in strong agreement, and the phrasing and measure of your post made it pretty obvious you are knowledgeable (another inverse correlation: absolute opinions launched with violence also seemed to be the realm of newbies; and yours was eminently not). > I very rarely used hints in my more database'y days evidence thereof! > I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience. It's been very, very much my experience. I worked in one place where nolock hints were applied everywhere, without them realising that READ UNCOMMITTED trans iso even existed, so had to show them that, and show them it could be specified at the application layer (not in the SQL). In another recent case I came across their use and I'm damn sure the person who used it didn't actually know what it did. Yeah, I've seen far too much hinting in my career.
renegade-otter 3y ago
Around the same time, I wrote an extremely convoluted MySQL query that changed its plan based on the column order in the SELECT clause. I wouldn't post snooty and dismissive comments about this - assume that you are in a company of people who have been to more rodeos than you have.
- _a_a_a_ 3y ago
  I've >25 years of DB rodeoing. Perhaps I know something about this by now. See my other answers on this thread. > that changed its plan based on the column order in the SELECT clause. Hmm? Could you elaborate; you mean the order of the cols or using ORDER BY? I assume you mean the latter but it sounded like the former.
somat 3y ago
The postgress team is generally opposed to hints. And their reasoning sort of makes sense. When do you need a hint? why do you need a hint? If you can answer those questions wouldn't it be better to codify those answers into the query planner than a one off hint? Which nicely sidesteps how hard it actually would be to first, understand the query planner to the degree needed to change it and second, the amount of effort it would take transform a one off hint into a general purpose optimization engine.

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

mickeyp 3y ago
You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
- _a_a_a_ 3y ago
  Poster said oracle not PG
  - mardifoufs 3y ago
    Total db noob here: is Oracle's query planner better? What's the difference between the two?
    - _a_a_a_ 3y ago
      This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
  - mickeyp 3y ago
    Oracle has query hints; pg does not. Query planner hinters are useful. That's my point.
zaptheimpaler 3y ago
No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.
- _a_a_a_ 3y ago
  You're right, I meant a failure of the DB/optimiser not the person. Thanks. No they're not perfect but I need them rarely with MSSQL > An optimizer will quickly run into NP-hard problems like join ordering As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.

mannyv 3y ago

Totally untrue...but adding hints makes your queries dependent on current performance. Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system. Even databases are general-purpose machines. That's the whole point of adding indexes, etc. I mean, has there ever been a database that auto-adds indexes based on profiler/optimizer feedback? (To answer my own question, apparently Azure SQL does).

mnahkies 3y ago
It's not automatically creating them AFAIK, but alloydb has an index suggestion tool https://cloud.google.com/alloydb/docs/use-index-advisor https://cloud.google.com/alloydb/docs/use-index-advisor Generally this seems like a nice balance to me - watch real queries, look for expensive plans and sniff out likely missing indexes based on the data distribution, but give the DBA final say on whether it's a good idea
_a_a_a_ 3y ago
> adding hints makes your queries dependent on current performance I don't understand this, can you explain. > Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system That's inaccurate. As a DBA I can understand the system better at higher level, but the database has statistics which gives it typically better understanding of the data distribution at a lower level. Indeed I could get that information and feed it into the query plan via hints, but that's going to be an enormous amount of my time, and I would have to do it every time the query is run whereas the database can keep an eye on the statistics as it varies and rebuild the query over time. Equally, if an index is added you can expect the database to start using it immediately without adding hints. Etc I am a fairly(?) skilled DBA who has a reasonable idea of what goes on underneath the hood, I do have some idea what I'm talking about.
MenhirMike 3y ago
> (To answer my own question, apparently Azure SQL does) Which is fun when you add a future schema migration that drops a column, test in locally and in some test cases, and then see it bomb in production because the column has an Index that you weren't aware of. (That's an easy thing to solve though, but definitely one of those cases where "The database is messing with its schema on its own" does do funny things)

GordonS 3y ago

In a perfect world, I wouldn't disagree; but we don't live in a perfect world where everything always goes your way. Sometimes the planner needs a little help, and that's OK.

_a_a_a_ 3y ago
I did say 'typically'! Yes, agreed, but it should rarely need help. Also hints are oh so often jammed in by newbies. I strongly get the impression use of hints is inversely correlated with experience/knowledge.
- GordonS 3y ago
  I'm a greybeard you cheeky scamp ;) I very rarely used hints in my more database'y days - but every so often, they were needed, and often to tell the database something that seemed screamingly obvious to me. I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.
  - _a_a_a_ 3y ago
    We are in strong agreement, and the phrasing and measure of your post made it pretty obvious you are knowledgeable (another inverse correlation: absolute opinions launched with violence also seemed to be the realm of newbies; and yours was eminently not). > I very rarely used hints in my more database'y days evidence thereof! > I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience. It's been very, very much my experience. I worked in one place where nolock hints were applied everywhere, without them realising that READ UNCOMMITTED trans iso even existed, so had to show them that, and show them it could be specified at the application layer (not in the SQL). In another recent case I came across their use and I'm damn sure the person who used it didn't actually know what it did. Yeah, I've seen far too much hinting in my career.

renegade-otter 3y ago

Around the same time, I wrote an extremely convoluted MySQL query that changed its plan based on the column order in the SELECT clause. I wouldn't post snooty and dismissive comments about this - assume that you are in a company of people who have been to more rodeos than you have.

_a_a_a_ 3y ago
I've >25 years of DB rodeoing. Perhaps I know something about this by now. See my other answers on this thread. > that changed its plan based on the column order in the SELECT clause. Hmm? Could you elaborate; you mean the order of the cols or using ORDER BY? I assume you mean the latter but it sounded like the former.

somat 3y ago

The postgress team is generally opposed to hints. And their reasoning sort of makes sense. When do you need a hint? why do you need a hint? If you can answer those questions wouldn't it be better to codify those answers into the query planner than a one off hint? Which nicely sidesteps how hard it actually would be to first, understand the query planner to the degree needed to change it and second, the amount of effort it would take transform a one off hint into a general purpose optimization engine.