4 ms·

> OpenAI helper: If you have an OpenAI account, the interface can query OpenAI to help you understand your query plan and optimize your SQL queries How good ar

> OpenAI helper: If you have an OpenAI account, the interface can query OpenAI to help you understand your query plan and optimize your SQL queries How good are LLMs at optimizing queries? Do they just give basic advices like "try adding an index there" or can they do more? I guess it would need to cross the results of the explain analyze with at least the DDLs of the table (including partitions, indexes, triggers, etc), and like the sizes of the tables and the rate of reads/writes of the tables in some cases, to be able to "understand" and "reason" about what's going on and offer meaningful advices (e.g. proposing design changes for the tables). I don't see how a generic LLM would be able to do that, but maybe another type of model trained for this task and with access to all the information it needs?

ComputerGuru 2y ago
They’re non-deterministic and YMMV by design. No one can answer that question. It might save you a hundred million with some genius optimization it lucked into or, more likely, it’ll give you a seemingly equivalent query that actually returns subtly different results (easy to do with query optimizations when eg you rely on inherent/default sort order for joins with groups).
VWWHFSfQ 2y ago
I've used them off and on for basic stuff like "tell me what this query does". They're usually pretty good at that. Sometimes it will make suggestions that are meaningful improvements, most of the time not. Unless you specifically ask about something like "would this be better done with a left join lateral/correlated subquery?" But you kinda have to already know what you're going for and then it can be helpful.
akx 2y ago
They can be useful. I just recently used Claude to make a query 10x faster, though I did have to back-and-forth a bit, so you do still need to know what you're doing.
- parthdesai 2y ago
  Claude has been really good at all things Postgres related
  - bertrandhartwig 2y ago
    I tested numerous LLM models locally with Ollama using PgAssistant. The one that gave me the best results is codestral:latest (15B).However, ChatGPT is truly a cut above the rest.
ellisv 2y ago
In my experience, LLMs are a lot better than my backend engineers who don't even try but not that much better than someone who's skimmed the docs. Typically they suggest 1) adding indexes and 2) refactoring the query. If you only provide the query then the model isn't aware of what indexes already exist. LLMs make assumptions about your data model that often don't hold (i.e. 3NF). Sometimes you have to give the LLM the latest docs because it's unaware of performance improvements and features for newer versions. In my view, it's better to look at the query plan and think yourself about how to improve the query but I also recognize that most people writing queries aren't going to do that. There are other tools (RIP OtterTune) that can tune database parameters. Not something I really see any generative model helping with.
- gopalv 2y ago
  > LLMs are a lot better than my backend engineers who don't even try but not that much better than someone who's skimmed the docs. LLMs are basically at the skill-set of "Googled something and tried it", which for a lot of basic things is mostly what everyone does. If you can loop back the results of the trial & error back to the model, then it does do a pretty good simulation of someone feeling their way through a problem. The loop is what makes the approach effective, but a model by itself cannot complete the process - it at least needs an agent which can try the recommendation (or a human who will pull the lever).
  - sgarland 2y ago
    My main issue with the use of LLMs for problems you’re unfamiliar with is precisely this. Trial and error is a fine way to work through a problem; you learn well from failure. But if you’re copy/pasting from something else the entire time, you’re not the one failing, and you’re not the one reasoning about why something didn’t work. People do not learn this way, full stop. Anyone who thinks they are should try to write down, on paper, their understanding of a subject they think they have learned.
  - swasheck 2y ago
    yes. my experience is that an LLM will sometimes suggest an interesting approach that breaks my pattern of thought enough to get me to a better solution. but the ones i primarily use (claude sonnet and chatgpt 4o) also seem to love suggesting CTEs which help readability but it depends on the RDBMS (and other things) as to whether or not they offer any real performance benefit. that’s a long way of saying that LLMs are a tool in this space but not yet a full solution, in my opinion and experience
joshstrange 2y ago
They won’t understand your schema and make the wrong assumptions _but_ as long as you are checking the results and can understand the final query they can be very helpful. Obviously there are tricks to let them better understand your schema but even using all of those it’s going to make the wrong assumptions about some columns and how they are used.
- bertrandhartwig 2y ago
  When pgAssistant sends the prompt to the LLM, it provides the DDL of the tables involved in the query (using pg_dump), the result of the ANALYZE query, and of course, the query itself. Believe me, LLMs understand the schema very well, and in the vast majority of cases, they suggest relevant indexes and/or query rewrites. That being said, they are still LLMs and inherently non-deterministic. But the goal of pgAssistant is not to fight against databases experts : it was build to help developpers before asking help to a DBA.
victorbjorklund 2y ago
LLM:s are really really good at creating queries. Shaving of the last ms from a complex query? yea, im sure an experienced query optimizer expert might beat it. But you get very far from letting the LLM run a few queries to gather info about the database and its use.
notatoad 2y ago
i've had great luck with it. i can't say i'm any sort of database expert, but it's a lot better at this than me. i was especially impressed that it could not only suggest changes to the queries or indexes to add, but it suggested a query be split into multiple queries at the application level which worked incredibly well.

ComputerGuru 2y ago

They’re non-deterministic and YMMV by design. No one can answer that question. It might save you a hundred million with some genius optimization it lucked into or, more likely, it’ll give you a seemingly equivalent query that actually returns subtly different results (easy to do with query optimizations when eg you rely on inherent/default sort order for joins with groups).

VWWHFSfQ 2y ago

I've used them off and on for basic stuff like "tell me what this query does". They're usually pretty good at that. Sometimes it will make suggestions that are meaningful improvements, most of the time not. Unless you specifically ask about something like "would this be better done with a left join lateral/correlated subquery?" But you kinda have to already know what you're going for and then it can be helpful.

akx 2y ago

They can be useful. I just recently used Claude to make a query 10x faster, though I did have to back-and-forth a bit, so you do still need to know what you're doing.

parthdesai 2y ago
Claude has been really good at all things Postgres related
- bertrandhartwig 2y ago
  I tested numerous LLM models locally with Ollama using PgAssistant. The one that gave me the best results is codestral:latest (15B).However, ChatGPT is truly a cut above the rest.

ellisv 2y ago

In my experience, LLMs are a lot better than my backend engineers who don't even try but not that much better than someone who's skimmed the docs. Typically they suggest 1) adding indexes and 2) refactoring the query. If you only provide the query then the model isn't aware of what indexes already exist. LLMs make assumptions about your data model that often don't hold (i.e. 3NF). Sometimes you have to give the LLM the latest docs because it's unaware of performance improvements and features for newer versions. In my view, it's better to look at the query plan and think yourself about how to improve the query but I also recognize that most people writing queries aren't going to do that. There are other tools (RIP OtterTune) that can tune database parameters. Not something I really see any generative model helping with.

gopalv 2y ago
> LLMs are a lot better than my backend engineers who don't even try but not that much better than someone who's skimmed the docs. LLMs are basically at the skill-set of "Googled something and tried it", which for a lot of basic things is mostly what everyone does. If you can loop back the results of the trial & error back to the model, then it does do a pretty good simulation of someone feeling their way through a problem. The loop is what makes the approach effective, but a model by itself cannot complete the process - it at least needs an agent which can try the recommendation (or a human who will pull the lever).
- sgarland 2y ago
  My main issue with the use of LLMs for problems you’re unfamiliar with is precisely this. Trial and error is a fine way to work through a problem; you learn well from failure. But if you’re copy/pasting from something else the entire time, you’re not the one failing, and you’re not the one reasoning about why something didn’t work. People do not learn this way, full stop. Anyone who thinks they are should try to write down, on paper, their understanding of a subject they think they have learned.
- swasheck 2y ago
  yes. my experience is that an LLM will sometimes suggest an interesting approach that breaks my pattern of thought enough to get me to a better solution. but the ones i primarily use (claude sonnet and chatgpt 4o) also seem to love suggesting CTEs which help readability but it depends on the RDBMS (and other things) as to whether or not they offer any real performance benefit. that’s a long way of saying that LLMs are a tool in this space but not yet a full solution, in my opinion and experience

joshstrange 2y ago

They won’t understand your schema and make the wrong assumptions _but_ as long as you are checking the results and can understand the final query they can be very helpful. Obviously there are tricks to let them better understand your schema but even using all of those it’s going to make the wrong assumptions about some columns and how they are used.

bertrandhartwig 2y ago
When pgAssistant sends the prompt to the LLM, it provides the DDL of the tables involved in the query (using pg_dump), the result of the ANALYZE query, and of course, the query itself. Believe me, LLMs understand the schema very well, and in the vast majority of cases, they suggest relevant indexes and/or query rewrites. That being said, they are still LLMs and inherently non-deterministic. But the goal of pgAssistant is not to fight against databases experts : it was build to help developpers before asking help to a DBA.

victorbjorklund 2y ago

LLM:s are really really good at creating queries. Shaving of the last ms from a complex query? yea, im sure an experienced query optimizer expert might beat it. But you get very far from letting the LLM run a few queries to gather info about the database and its use.

notatoad 2y ago

i've had great luck with it. i can't say i'm any sort of database expert, but it's a lot better at this than me. i was especially impressed that it could not only suggest changes to the queries or indexes to add, but it suggested a query be split into multiple queries at the application level which worked incredibly well.