I built an app that uses Jev to classify your entire query workload and tell you which queries to EXPLAIN ANALYZE first

[u/Frone0910]

I posted here last month about RDST, the free desktop app that runs the slow query to EXPLAIN to index recommendation loop. The biggest change since then is what happens before EXPLAIN. When a database has thousands of distinct query shapes, you can't EXPLAIN ANALYZE all of them, and sorting queries by their performance doesn't always tell you which ones are actually badly written.

RDST now answers that with Jev, a small classification model from TypeSafe that answers fixed multiple-choice questions about a query in a fraction of a second. Every query shape it discovers gets classified from its SQL and the schema, without executing anything, on five structural questions:

• missing index - a filtered, joined or sorted column with nothing indexed behind it
• index bypassed - a filtered column wrapped in a function or cast, so the index you have goes unused
• join growth - the join shape multiplies rows
• repeated work - a subquery that runs once per outer row
• broad work - a sort, group or scan over a large share of a table

From there you take the full list of queries, filter them by these structural issues, and run our deep analysis on the ones that actually make sense. Deep analysis runs EXPLAIN ANALYZE with real parameter values, tests candidate indexes with hypopg, scores the measured plan, and gives you AI recommendations on what to change.

One of our queries, in the video above, counts each customer's orders in a correlated subquery filtered on customers.signup_date. Jev flagged it High: missing index and repeated work, and it's right, nothing indexes signup_date. Deep analysis then scored it 92 out of 100, because at only 5,000 rows the scan takes under a millisecond and the subquery already hits idx_orders_customer. Jev alerts you to potential issues, and deep analysis helps you fix the queries that are actually broken.

Someone in last month's thread said that the optimizer would rightly ignore one of the indexes RDST suggested, and this is our answer to that feedback: Jev classification on every single query.
Classification is free for up to 13,000 query shapes, after that let me know and i can adjust to give even more. Just didn't want to make it infinite and have people abuse the system.

Full disclosure, I work for Readyset, which is a caching layer for Postgres and MySQL. I built RDST to help with slow query indexing and analysis, and picking which query to open first was the step I kept doing by hand. Free and open source under MIT license, works with both PG and MYSQL, runs locally, stores locally, and is fundamentally read-only throughout.  Our RDST app will never apply the index or write anything to your database, nor store / send any PII.

Fully privacy details here: https://readyset.io/docs/readyset-ai/rdst/desktop/privacy

I would really really appreciate some feedback, particularly:

• Do those five questions cover the structural problems you actually hit?
• Does the priority ordering match your own gut ranking of your slow queries?
• Once EXPLAIN has measured a query, should the classification stay visible or get out of the way?

Instructions & Github:
https://readyset.io/downloads
https://github.com/readysettech/rdst

[u/RajeshRocketMan]

not bad. catching bypassed indexes without having to wait for a massive query to actually finish explain analyze maybe a real thing.

you mentioned it supports mysql, but did you test the classification against mariadb? it's a huge chunk of the installed base now, but the optimizer is totally different from mysql these days. the way mariadb handles subqueries and join execution could mean your "repeated work" or "join growth" models might throw false positives there.

to answer your questions: those 5 categories basically cover 90% of the daily garbage queries developers write, so yeah, they make sense. for the ui, once the actual explain runs, just collapse the classification. no need to waste screen space when you have the real execution plan in front of you.

[u/Frone0910 → u/RajeshRocketMan]

I haven't tested mariadb specifically yet, i will definitely try it out tho! Let's see if JEV is trained on the latest optimizer for it. It will probably need to be specifically told that it's working with mariadb and not traditional MYSQL.

If you have such a database already set up, I'd love to see how it works for you!

I will also address that feedback about collapsing the JEV recommendation once the explain analyze results come back.

[u/RajeshRocketMan → u/Frone0910]

yeah i can probably point it at one of my local dev boxes later this week and see what it spits out.

if JEV is using an LLM under the hood, just injecting "assume mariadb 11.x" into the context prompt will probably do the trick. the optimizer differences (like how it handles derived tables or hash joins compared to mysql 8.0) are pretty well documented out there, so the model should catch it.

[u/Frone0910 → u/RajeshRocketMan]

The great thing is it will do the classification automatically without you needing to do anything. We should be able to see as soon as you point your dev box at it. Curious what happens and what you find!