3 ms·

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

by 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)

by mannyv 3y ago

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)