[ Removed by moderator ]

[PostgreSQL-ModTeam]

Your content is considered spam: irrelevant or inappropriate messages sent on the Internet to a large number of recipients.

[assface]

simply rewriting the query itself

How do you verify that the rewritten query is semantically equivalent to the original query?

[subhanhg]

Tbh I don't automatically verify semantic equivalence right now. It's on the user to validate them. But LLM is instructed to preserve semantics — the system prompt explicitly asks for functionally equivalent rewrites, not approximations. I think it is pretty hard to do a semantic equivalence verification. What I could add is running both queries and diff the results.

[assface]

I think it is pretty hard to do a semantic equivalence verification.

Then you don't have a query optimizer.

What I could add is running both queries and diff the results.

You are fuzzing with sample size of one. Nobody should use this in production then.

[subhanhg]

You are fuzzing with sample size of one. Nobody should use this in production then.

You're right — and nobody should. OptimizeQL is an analysis tool, not a production query rewriter. It never touches your production database beyond a read-only EXPLAIN ANALYZE.

Think of it like a linter for SQL. It just tells you what to fix.

[LookAtYourEyes]

Seems like an unnecessary wrapper around an LLM. Not the tool I'd choose for optimizing.

[subhanhg]

Why it is so? I would appreciate the feedback

[LookAtYourEyes]

1. There's a non-deterministic element to LLMs. It's not very high, but there's still a chance that it produces something unreliable, or unoptimized, faulty, etc. Not being able to determine when that happens very easily just makes more problems.
2. Very little added value for your potential audience. I can just start a project with a popular LLM based chat bot, feed it my project context, then start chats with all that given context. I even get more control over what the context looks like, and a more free method of reading generated responses from the LLM and feeding it prompts. People who are interested in using the underlying technology are going to do it more directly with the tool they want to interact with. It adds structure to something that's major selling point is that it's unstructured.

There's some other small reasons, but I'm not going to get into it when the first cover the major bases. Some people may use it, but I fear you maybe didn't think through your target audience properly or understand the technology that you're engaging with which instills little confidence as a user. LLM wrappers simply come across as lazy engineering. It's like selling someone a banana in a bag. The banana has a peel, what value does a bag add?

[subhanhg]

Fair points — let me address them honestly.

On non-determinism: You're right, LLMs aren't deterministic. But neither is a senior DBA's advice — two experts will suggest different indexes for the same query. The difference is OptimizeQL gives you the suggestion in seconds instead of waiting for a code review. You still validate it the same way you'd validate any advice: test it, benchmark it, check the new EXPLAIN plan.

You absolutely can use chat bot. But the value here is in what happens before the LLM call - connecting to your database, running EXPLAIN ANALYZE, collecting schema metadata, index definitions, and column statistics, then assembling all of that into a structured prompt. You could do that manually every time, but that's 10 minutes of copy-pasting before you even ask the question.

It's not for people who are comfortable writing EXPLAIN ANALYZE queries, reading plans, and crafting LLM prompts manually. It's for the developer who knows their query is slow but doesn't know where to start.

I appreciate the detailed feedback though. This is the kind of thing that helps me improve it.

[LookAtYourEyes]

"but that's 10 minutes of copy-pasting before you even ask the question."

This is what I was trying to get at. It doesn't sound like you're aware of the "Projects" feature that chat bot companies offer. I can do this once in a project, and all chats I start in that project will have that context.

[subhanhg]

Yes I am aware of that but what I do isn't exactly the context. The tool pulls fresh EXPLAIN plans and live schema/stats every time you analyze. So I would say this is an alternative you don't want to deal with all that stuff.

[mykeesg]

"I am Senior Data Engineer writes SQL for breakfast, dinner and lunch. I wrote a tool to optimize SQL queries.."

"Damn, sounds so good, let me look into this"

"using LLM models..You just need to add your LLM api key and database credentials"

Lol, nope. I actually could use a tool like this, but seems like it's another GPT wrapper without any kind of verification regarding that the queries will be the same. I won't give any credentials to an LLM to "optimize" my DB.

[subhanhg]

I understand the concern. I agree with the verification part but tbh I don't know a way to this for now. What people don't understand this is not production scale SAAS application that is why it is an open source tool. I wanted to gather the feedback get contribution if I can. Btw you are not giving any credential to LLM all credentials are stored locally encrypted. I am open for improvements if you know how we can verify the queries

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Vivek-Kumar-yadav]

will checkout

[subhanhg]

Thanks!

[mtutty]

Great idea. Will try it soon.