The Slow Query Sentinel: A Zero-Dollar Server That Explains Your Database's Worst SQL

Description and use

The sentinel automatically identifies the slowest PostgreSQL queries, sends them to a free LLM, and receives plain explanations and fixes, speeding up performance troubleshooting.

Problem

On-call engineers often spend long time diagnosing slow queries after a sudden performance drop, typically by manually inspecting EXPLAIN output and identifying root causes such as index regressions.

Outcome

The sentinel reduces the time from noticing a slow query to understanding its root cause from hours to minutes, providing clear, high-impact fixes such as index suggestions.

Workflow

• 1. Prepare a free server database: enable pg_stat_statements and create a query_advice table.
• 2. Write a collector that queries pg_stat_statements for top queries, fetches EXPLAIN (FORMAT JSON) for each, and stages the data.
• 3. Implement a model caller that sends query text and plan JSON to a free LLM endpoint, requesting a structured JSON answer with advice and impact.
• 4. Parse the model response and insert advice into query_advice table.
• 5. Query the table to rank recommendations by impact, presenting the highest-value fixes first.

Evidence

The sentinel I describe below pulls the slowest queries from pg_stat_statements, formats them with their execution plans, and asks a free model to explain the root cause in plain language.

Source

A typical response looks like this: the model points out a sequential scan on a large table, suggests a composite index, and marks the impact as high.

Source