5 ms·

I can definitely relate to the struggle of debugging slow SQL queries. I've been optimizing Postgres for the last three years for my day job. There's nothing li

I can definitely relate to the struggle of debugging slow SQL queries. I've been optimizing Postgres for the last three years for my day job. There's nothing like spending hours trying to determine why a query is slow, only to discover some bizarre incantation makes the problem go away. I had one case where adding OFFSET 0 to a query, which did not change the semantics of the query, made the query ~10x faster. It turned out that due to a reference to a large column being inlined in a dozen different places, Postgres was detoasting[0] that column a dozen times more than it needed to. Adding the OFFSET 0 prevented the subquery from being inlined and made it so Postgres only detoasted the column once. In general, I think optimizing a database is way harder than it needs to be. A lot of it is black magic and it requires a ton of expertise that many teams don't have. Even though I've been doing it for years, I still struggle with figuring out what indexes I should create to make a query fast. I recently started a startup in an attempt to make optimizing databases easier. I built a Postgres extension that records information about queries as they run. It's basically the same information you would get for EXPLAIN ANALYZE, but you get it for every query at query time. Then, using this information, I'm able to make specific recommendations about how you should optimize your database. I can give advice on how you should structure your schema and what indexes you should create in order to maximize the performance of your queries. This way, even teams that don't have much in-house Postgres experience won't have to spend hours or even days trying to figure out how to make their queries faster. If this sounds interesting to you, I would be happy to chat. You can reach out to me at michael@perfalytics.com. It's my goal to use the expertise I've built up over the last couple of years to make scaling Postgres a truly painless experience. [0] https://malisper.me/postgres-toast/ https://malisper.me/postgres-toast/

ChrisSD 7y ago
Isn't there some irony in fact that SQL was meant to abstract away the "how" of getting records? If you need to optimize through implementation specific quirks, then wouldn't a more direct api be preferable?
- Shorel 7y ago
  All abstractions can be a bit leaky. That doesn't make them useless.
- malisper 7y ago
  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.
brightball 7y ago
Thoughts on Dexter?
- malisper 7y ago
  I like what it's trying to do, but I disagree with the approach. I have a few issues with the implementation: #1 Dexter relies solely on the Postgres cost model. Dexter works by using HypoPG[0] to create a hypothetical version of every possible index with two or less columns. It runs EXPLAIN against every query and keeps track of which indexes are used. This determines all the indexes that are part of a plan that Postgres _thinks_ will be the cheapest. The problem with this is Postgres is often wrong about which plan is best. If there is any disparity between the stats Postgres has, or if there is an issue with the Postgres cost model, the recommendations HypoPG will make may not match reality. I think any good optimization advice has to be based on how the queries actually perform. #2 Dexter will only check a small set of potential optimizations. Dexter only looks at creating one and two column b-tree indexes. This is a really small set of all possible optimizations. Dexter will not look for things like: - Config Changes - Gin Indexes - Partial Indexes - Table Partitioning - Query Rewriting (Dexter wouldn't have caught the issue mentioned in the OP) I think Dexter may be an ok starting point if you have never touched an index before. I don't think Dexter would have caught any of the major optimizations I've implemented. #3 Dexter is a fully automated solution. I don't think fully automated systems will be better than humans anytime within the next few decades. I think you can get some basic recommendations from an automated solution, but any good advice is going to have to come from a human. [0] https://github.com/HypoPG/hypopg https://github.com/HypoPG/hypopg
brian_spiering 7y ago
Optimizing SQL appears to be a reasonable task for Reinforcement Learning. Given it is sequential with clearly defined actions, states, and rewards. There has been work already done, for example https://blog.acolyer.org/2019/01/18/towards-a-hands-free-query-optimizer-through-deep-learning/ https://blog.acolyer.org/2019/01/18/towards-a-hands-free-que.... But it feels like the early days.

ChrisSD 7y ago

Isn't there some irony in fact that SQL was meant to abstract away the "how" of getting records? If you need to optimize through implementation specific quirks, then wouldn't a more direct api be preferable?

Shorel 7y ago
All abstractions can be a bit leaky. That doesn't make them useless.
malisper 7y ago
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.

brightball 7y ago

Thoughts on Dexter?

malisper 7y ago
I like what it's trying to do, but I disagree with the approach. I have a few issues with the implementation: #1 Dexter relies solely on the Postgres cost model. Dexter works by using HypoPG[0] to create a hypothetical version of every possible index with two or less columns. It runs EXPLAIN against every query and keeps track of which indexes are used. This determines all the indexes that are part of a plan that Postgres _thinks_ will be the cheapest. The problem with this is Postgres is often wrong about which plan is best. If there is any disparity between the stats Postgres has, or if there is an issue with the Postgres cost model, the recommendations HypoPG will make may not match reality. I think any good optimization advice has to be based on how the queries actually perform. #2 Dexter will only check a small set of potential optimizations. Dexter only looks at creating one and two column b-tree indexes. This is a really small set of all possible optimizations. Dexter will not look for things like: - Config Changes - Gin Indexes - Partial Indexes - Table Partitioning - Query Rewriting (Dexter wouldn't have caught the issue mentioned in the OP) I think Dexter may be an ok starting point if you have never touched an index before. I don't think Dexter would have caught any of the major optimizations I've implemented. #3 Dexter is a fully automated solution. I don't think fully automated systems will be better than humans anytime within the next few decades. I think you can get some basic recommendations from an automated solution, but any good advice is going to have to come from a human. [0] https://github.com/HypoPG/hypopg https://github.com/HypoPG/hypopg

brian_spiering 7y ago

Optimizing SQL appears to be a reasonable task for Reinforcement Learning. Given it is sequential with clearly defined actions, states, and rewards. There has been work already done, for example https://blog.acolyer.org/2019/01/18/towards-a-hands-free-query-optimizer-through-deep-learning/ https://blog.acolyer.org/2019/01/18/towards-a-hands-free-que.... But it feels like the early days.