3 ms·

I have a number of thoughts on this. A common claim I've heard is that query optimizers are better at producing efficient query plans than all but the best prog

I have a number of thoughts on this. A common claim I've heard is that query optimizers are better at producing efficient query plans than all but the best programmers. I can't find any concrete studies on this, but it's plausible. Databases are great at handling slight variations of a query and a data set. I've had times where I thought Postgres was going to execute a query one way, but it wound up executing it in a different way that happened to be more efficient. Given that query optimizers are usually better than humans, it makes sense to use them for the general case. The question then becomes what happens for the exceptional cases. Cases where the database chooses a bad plan that a human could tell is obviously going to be slow. For these cases I think it's up to the database implementors to decide what to do. Some databases have support for query hints, a way to force your DB to do what you want. The Postgres team has avoided implementing query hints for two reasons: - In the long term queries that make use of query hints will be suboptimal. (Personally I don't buy this. I've ran across cases in Postgres where the planner will only give you a very inefficient query. I would rather use query hints to get the query to work for the time being than never have a working query at all.) - Implementing query hints would overly complicate the planner. I find this to be pretty reasonable. As usual, having a query optimizer is a tradeoff. It seems to me like in most cases it's better to have one than to not.

petergeoghegan 7y ago
Even if you assume that the human programmer is better than the query optimizer, which I think you'll agree is assuming rather a lot, the query optimizer still has important advantages. It can react to changing conditions. The optimal query plan for a given query can change as data is added or deleted. Moreover, there can be multiple optimal query plans at the same time for "the same query" (or at least the same parameterized SQL statement). I'm not suggesting that optimizers don't produce bad plans that have to be worked around. My point is that comparing the optimizer to an expert human only makes sense as a thought experiment. Most bad query plans are caused by bad selectivity estimates/stats, not bad cost models.
- ChrisSD 7y ago
  This is starting to sound a lot like the discussions around optimizing C compilers. Only in that case you can drop into assembly if necessary.
CaptainZapp 7y ago
I totally agree with your assesments. There's just one more flavor, which I'd like to add to the mix: That's when the underlying data changes. Optimizers (on which I agree with you are usually much better than any developer / DBA in assessing the optimal path to execute a query). They do rely on statistcs, however, which are not automatically updated. This is also reasonable since it would be much too expensive to run real time updates on statistics, while the database is in use. A heavily transaction oriented system (which are still a lot of relational database implementations) would be killed by such a requirement. If those indexes are stale, however, this can totally kill the optimizer's ability to determine a good plan. I've seen very, very few examples when a optimized plan sucks, when it's based on correct statistics. And I agree that hints are a a good way to fix this. I'd make this conditional that they always need to be reviewed with a new version of the underlying database engine.

petergeoghegan 7y ago

Even if you assume that the human programmer is better than the query optimizer, which I think you'll agree is assuming rather a lot, the query optimizer still has important advantages. It can react to changing conditions. The optimal query plan for a given query can change as data is added or deleted. Moreover, there can be multiple optimal query plans at the same time for "the same query" (or at least the same parameterized SQL statement). I'm not suggesting that optimizers don't produce bad plans that have to be worked around. My point is that comparing the optimizer to an expert human only makes sense as a thought experiment. Most bad query plans are caused by bad selectivity estimates/stats, not bad cost models.

ChrisSD 7y ago
This is starting to sound a lot like the discussions around optimizing C compilers. Only in that case you can drop into assembly if necessary.

CaptainZapp 7y ago

I totally agree with your assesments. There's just one more flavor, which I'd like to add to the mix: That's when the underlying data changes. Optimizers (on which I agree with you are usually much better than any developer / DBA in assessing the optimal path to execute a query). They do rely on statistcs, however, which are not automatically updated. This is also reasonable since it would be much too expensive to run real time updates on statistics, while the database is in use. A heavily transaction oriented system (which are still a lot of relational database implementations) would be killed by such a requirement. If those indexes are stale, however, this can totally kill the optimizer's ability to determine a good plan. I've seen very, very few examples when a optimized plan sucks, when it's based on correct statistics. And I agree that hints are a a good way to fix this. I'd make this conditional that they always need to be reviewed with a new version of the underlying database engine.