EXPLAIN and slow queries

Visual plan

The Visual query plan button in the editor toolbar draws the plan of the current query as a tree, on PostgreSQL, MySQL and SQL Server.

• Each node shows its operation, the table or index it uses, the estimated rows and the cost.
• A bar shows each node's share of the cost, green to red.
• The most expensive node is marked bottleneck.

MySQL's access types get readable names (Full Table Scan, Index Lookup). The plan is the planner's estimate; the query is not executed. You can also open it from a query in Trace.

Text plan

Explain in the toolbar shows the database's own plan as text, for the first statement of the selection or the tab: EXPLAIN on PostgreSQL, MySQL, ClickHouse and Trino, and EXPLAIN QUERY PLAN on SQLite. On SQL Server, use the visual plan.

Every text plan goes to Query plan history (the clock icon), so you can compare a plan before and after a change. See History and Query Library.

AI › Explain this query is something else: a plain-language explanation of what the query does.

Speed up this query

On PostgreSQL, MySQL and SQL Server, with AI set up, AI › Speed up this query… proposes one index for a slow read and tests it. After a query takes five seconds or more, a Speed it up button also appears under the editor.

1. Lily removes every value from the query and sends it, its plan and the schema to your AI tool.
2. The AI proposes one CREATE INDEX on plain columns. Anything else (several statements, expression indexes) is rejected.
3. Apply & prove measures the planner's cost for your query, creates the index (CONCURRENTLY on PostgreSQL, so writes keep flowing) and measures again.

The result shows the cost before and after. It is the planner's estimate, not a timing; real speed depends on your data and cache.

If the estimate did not drop, the index stays. Drop it yourself if you do not want it. (Investigate does undo fixes that did not help.)

On a read-only connection the button is Copy index instead.

Suggest indexes

AI › Suggest indexes… reviews your recent reads on the connection and recommends indexes as text. Nothing is created. See AI in the editor.