Readyset Docs

rdst analyze

See how your database runs a query and review suggested indexes and rewrites. Check whether Readyset can cache it.

rdst analyze reports how your database plans to run a query and how long it takes. Review its suggestions for improving the query and check whether Readyset can cache it.

Common usage

# Analyze an inline query
rdst analyze -q "SELECT * FROM orders WHERE customer_id = 42" --target prod-orders

# Analyze a query from the registry by hash
rdst analyze --hash a1b2c3d4e5f6 --target prod-orders

# Analyze a query from the registry by name
rdst analyze --name top-customers-by-revenue --target prod-orders

# Analyze a multi-line query from a file
rdst analyze -f ./reports/top_customers.sql --target prod-orders

# Analyze from stdin
echo "SELECT COUNT(*) FROM orders" | rdst analyze --stdin --target prod-orders

What it does

Run EXPLAIN. Read the planner's plan without executing the query. Missing permissions, unsupported SQL, or missing parameter values can cause this step to fail.

Run EXPLAIN ANALYZE. This actually runs the query against the target. On large tables this can take minutes, RDST prompts you to skip after 10 seconds, and keeps the EXPLAIN plan for the rest of the analysis.

Collect schema. RDST extracts the relevant tables, columns, index definitions (including USING clause), and row estimates so the LLM has concrete numbers to reason about.

Generate recommendations. The LLM produces candidate index definitions, candidate rewrites, and a Readyset cache-fit score.

Validate. A post-hoc check runs against your real schema to catch common LLM hallucinations, for example, a suggested index that already exists, or a rewrite that references a non-existent column.

Example output

Query Analysis
════════════════════════════════════════════════════════════════
  Hash:              a1b2c3d4e5f6
  Target:            prod-orders (PostgreSQL 15.4)

  SELECT o.id, o.customer_id, o.total_cents, c.name
  FROM   orders o
  JOIN   customers c ON c.id = o.customer_id
  WHERE  o.status = 'shipped' AND o.created_at >= $1
  ORDER BY o.created_at DESC
  LIMIT  50;

Performance
  Execution time:    412.1 ms   →  SLOW (optimization needed)
  Rows examined:     2,847,201  (38.2% of orders)
  Rows returned:     50
  Scan efficiency:   INEFFICIENT
  Rating:            FAIR (65/100)

Key Issues
  • Full index scan on orders_created_at_idx; composite needed
  • Sort step: 41% of execution time
  • JOIN order picks customers first despite smaller filtered set

Index Recommendations
  1. CREATE INDEX idx_orders_status_created
       ON orders (status, created_at DESC)
       WHERE status = 'shipped';
     Expected impact: cuts scan ~90%, -300ms (~73%)

Query Rewrites
  (None suggested  -  rewrite candidates would not improve correctness-preserving
   performance. Fix the missing index above first.)

Readyset Cache Fit
  Candidate:        YES  (confidence: high)
  Parameter:        $1 (created_at lower bound)
  Next step:        rdst cache add a1b2c3d4e5f6 --target prod-orders-cache

If a Readyset cache is already deployed for the target (i.e. a <target>-cache target exists from rdst cache deploy), analyze also runs the query against the cache and reports the actual measured latency next to the upstream latency. The report labels measured latency separately from the cache-fit assessment.

Review and test suggested rewrites for equivalent results, including ordering and NULL handling. If a rewrite changes results, report it with rdst report --hash <id> --negative.

Interactive follow-up

Pass --interactive to enter a Q&A loop after the analysis:

rdst analyze --hash a1b2c3d4e5f6 --target prod-orders --interactive
> Why not suggest a partial index on created_at alone?
RDST: The query filters by status='shipped' first (~18% of rows), so indexing on
status, created_at lets the scan skip 82% of rows before the sort. A plain
created_at index would need to scan every shipped *and* unshipped row before
filtering.

> What if I cache this in Readyset instead?
RDST: With customer_id bound and $1 as a time cutoff, Readyset can keep the result
set warm. Cache-fit is high. You can test it with:
  rdst cache add a1b2c3d4e5f6 --target prod-orders-cache

The same conversation is saved, replay it later with --review:

rdst analyze --hash a1b2c3d4e5f6 --target prod-orders --review

Flags reference

FlagWhat it does
-q, --query <sql>Inline SQL (quote multi-line queries)
-f, --file <path>Read SQL from a file
--stdinRead SQL from stdin
--hash <id>Load a query by hash from the registry
--name <name>Load a query by saved name
--target <name>Target database
--fastSkip EXPLAIN ANALYZE; use EXPLAIN only (much faster, less accurate timing)
--save-as <name>Save the query to the registry under a friendly name
--interactiveEnter Q&A mode after analysis
--reviewReplay the previous conversation for this query without re-running
--workloadAnalyze multiple queries together for holistic index recommendations (preview)
--jsonMachine-readable output
--skip-warningSkip the EXPLAIN ANALYZE safety confirmation

When to use --fast

EXPLAIN ANALYZE actually runs the query. On a production database with a 500 ms query that's fine. On a 2-minute query against a 500M-row table, it's a real cost. --fast skips execution and uses the EXPLAIN plan alone.

The tradeoff: row counts in EXPLAIN are planner estimates, not actual measurements, so timing-related recommendations are approximate. Review index and rewrite recommendations against the estimated plan. Measured results may reveal additional issues.

See also