PgAssistant: OSS tool to help devs understand and optimize PG performance
17 comments
·February 12, 2025ggregoire
ellisv
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.
akx
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
Claude has been really good at all things Postgres related
ComputerGuru
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
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.
dewey
The screenshot section in the README seems to be empty. Would've been interesting to see that. There's many tools that do similar things like https://github.com/ankane/pghero or some tools here: https://gitlab.com/postgres-ai
justinclift
I think it's probably just a large file, as I remember thinking the same thing but it was loaded when I returned to the browser tab with the readme later on.
dewey
Ah, you are right. I'm on a slow connection right now and it's a 22MB gif.
(https://github.com/nexsol-technologies/pgassistant/tree/main...)
ai-christianson
> and it's a 22MB gif
Whoa... that's a lot of data for a README! But demos are pretty important, so I guess it's worth it.
znpy
honest question: does anybody know a good book/tutorial/source/whatever on becoming a postgresql DBA ?
most of the material i see is written for people that want to write applications that work with postgresql, not on how to proficiently manage postgresql itself.
cpachmann
[dead]
null
> 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?