Investigate

I remember things. Trace helps.

Investigate starts from a symptom, why is loading the orders page slow?, and ends with fixes that cite their evidence. For an index, it can apply the fix and measure whether it helped.

Open it from Tools › Investigate in the sidebar. It works on PostgreSQL, MySQL, SQLite, SQL Server, Trino and ClickHouse.

The evidence

Lily sends one request with:

• the schema (focused on the relevant tables on big schemas) and the business context from Understand;
• up to 15 of your recent reads on this connection, with every value replaced by ?, and the plans of five of them;
• the eight slowest queries saved by Trace, if Settings › Workspace › Remember Trace history on disk is on.

No row values are sent. Each query is tagged (Q1, T3…), and every proposed fix has to cite them.

The fixes

Each fix is labeled:

• INDEX and STATS can be applied by Lily. A fix of this kind that does not cite real evidence is dropped.
• REWRITE and ADVICE are for you to apply; Lily offers Copy.

Apply and prove

Apply & prove on an index fix:

1. measures the planner's estimated cost of the cited queries;
2. creates the index (CONCURRENTLY on PostgreSQL, so writes are not blocked);
3. measures again and shows how much the estimate dropped.

If the estimate did not go down, Lily drops the index again on PostgreSQL, MySQL and SQL Server. On SQLite, ClickHouse and Trino it cannot undo the change automatically, so check the result and remove the index yourself if it did not help.

The numbers are the planner's estimates (from EXPLAIN, not EXPLAIN ANALYZE), not timings. An investigation applies at most five fixes.

Production

Apply & prove runs as soon as you click it, without the extra confirmation that writes get elsewhere. On a production connection, prefer copying the fix and running it yourself, or connect read-only.

Read-only connections

On a read-only connection Investigate only proposes. It never applies anything, and that rule is built into how it runs, not just hidden in the interface.