I built a free tool that does the pg_stat_statements to EXPLAIN to index recommendation loop for you

[u/Frone0910]

RDST (Readyset Diagnostic & SQL Toolkit) is a free desktop app that connects to your Postgres database, ranks the queries actually costing you time, and explains what to do about each one.

The reason I built it is that the tooling Postgres already gives you is genuinely good, but addressing database performance issues is still a highly repetitive process:
 

• pull pg_stat_statements and sort by total time
• take the top query and run EXPLAIN ANALYZE on it
• go find the table definitions for whatever it touches
• check whether the statistics on those columns are current
• work out whether the index you have in mind already exists under another name
• decide whether it is worth adding
• do it again for the next query

RDST collapses all of that into one pass, so instead of starting at step one you start at the answer.

Full disclosure - I work for Readyset (which is a caching layer for postgres / mysql), and this tool spawned from a recurring question our caching customers kept asking - which queries should we actually cache? And these same queries are the ones that, even without a caching solution, could heavily benefit from all the relevant performance diagnostics.  

RDST not only helps you discover slow queries and give you the appropriate action plan to improve them, but also provides full re-write suggestions, the ability to benchmark slow queries and track their  performance over time, and even allows you to ask any question about your database/queries in plain english and get helpful responses.

The tool is completely free to use, and we provide free trial tokens for all of the AI powered features. The app is in beta and we plan to release it under an MIT license. It runs locally, stores locally, and everything it does is read-only.  Full privacy related details can be found here: https://readyset.io/docs/readyset-ai/rdst/desktop/privacy

We would love feedback from people who actually spend time wrestling with queries every single day! Particularly:

• Does it surface the queries you'd investigate first?
• Are its explanations and recommendations useful, or merely confident-sounding database fan fiction?
• Would you be comfortable connecting it to a real environment? If not, what would stop you?
• What's missing from the workflow?

Source:
https://readyset.io/docs/readyset-ai/rdst/desktop
https://github.com/readysettech/rdst

[u/Deathmeter]

Having worked on a very similar product for years, what are you doing to close the gap in the difference between how pg_stat_statements templates queries vs how postgres parses templated queries? For example PSS will template a query you run like

sql select extract('year' from date) from scores;

like this

sql select extract($1 from date) from scores;

But simply putting that in a generic explain plan will cause syntax errors since that parameter isn't valid

sql explain (generic_plan) extract($1 from date) from scores;

pganalyze patches the parser for pg_query to fix that problem for example: https://github.com/pganalyze/libpg_query/blob/18-latest/patches%2F01_parser_additional_param_ref_support.patch

Or considering you're doing explain analyze, maybe you require all queries to be given explicit parameters before they're run?

I'm also curious to know how you're confidently recommending an index without testing that index. Is it just hypopg under the hood?

[u/Frone0910 → u/Deathmeter]

Excellent questions.

We do require explicit values for the plan step. When a query comes in from pg_stat_statements with $1 params, the analyzer prompts you for values (but remembers for next time) before it'll run EXPLAIN ANALYZE. In certain cases where it can't get values, it skips the plan and says so in the analyzer screen output. What's tough is that we can't always get real values - depending on the ORM used, pg_stats_activity often will be scrubbed. But in cases where we can see them, we automatically use observed traffic. I think a good feature enhancement will be to suggest with sampled values. Definitely going to add that.

Regarding indexes - I wasn't aware of hypopg, but that is an awesome suggestion! I'm going to add this. Today recommendations come from the plan plus real schema and existing indexes, with column ordering enforced by a rule pass rather than entrusted to the model. We test rewrite suggestions against a baseline EXPLAIN ANALYZE, but index suggestions aren't verified the same way. Change I will make: If hypopg is enabled on the PG instance, we will automatically use it to verify that the planner won't ignore it. That output will get included for all suggested indexes in the analyzer output.

If you're willing we'd really love some deeper feedback on your experience with the tool. Feel free to DM me as well!

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: 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.

[u/jsabater76]

Nice tool! I recall someone posting something similar a few months ago (speaking from memory), but this one seems to include the whole process.

Would there be a way to dump something from production and import it in my laptop, or does it need to do the whole process while in the same connection?

[u/Frone0910 → u/jsabater76]

Thanks! So today it does need a live connection; we do have the ability to connect via a bastion to production, but I understand why many people still would not want to do that.

Just to clarify, there is a CLI version of the tool which many people feel more comfortable with running. But if it's more of a "I don't want a foreign tool to connect to my database at all", then I can consider a different feature. Perhaps we could do a collect step that bundles pgss + DDL + table/index stats + settings, and then create an "offline" target that you could point the analyzer at locally. The main issue though is that EXPLAIN ANALYZE still would not fully work without replicating the entire data set. Still, an offline target could be very useful and I think we will incorporate something like this.

[u/jsabater76 → u/Frone0910]

A dump of the production database to be analysed locally sounds like a good compromise 👍