Query Optimization Agent

Suggests rewrites and indexes for a slow query, based on how the database plans to run it.

What you provide

• Connection (required).
• Query (required): one statement. Text with more than one statement is refused with how many it has.

How the plan is read

The agent asks the database for the plan without running the query wherever the engine allows it, so the statement cannot change anything. On PostgreSQL and MySQL engines the request is also made inside a read-only transaction.

• PostgreSQL and engines built on it: EXPLAIN (FORMAT JSON); on CockroachDB, its own EXPLAIN text plan.
• MySQL, MariaDB, Aurora MySQL and TiDB: EXPLAIN FORMAT=JSON.
• SQL Server: the estimated XML plan (SHOWPLAN_XML).
• Oracle: EXPLAIN PLAN read back with DBMS_XPLAN.DISPLAY; the plan rows are rolled back afterwards.
• SQLite: EXPLAIN QUERY PLAN, as a tree.
• MongoDB: the query planner's explain for a find, count or aggregate, given as the IDE's JSON query or as db.<collection>.find(...).
• Neo4j: EXPLAIN, as an operator tree with estimated rows.
• Elasticsearch: the profile of a search or count, run with size: 0 so no documents are returned.
• Apache Cassandra: CQL has no EXPLAIN, so a SELECT is run once with tracing and the trace events are sent; other statements are not traced, because tracing runs them.
• Redis: commands have no plan; the answer says so and the advice relies on each command's documented time complexity.

When the database cannot plan the query, the answer says so and the advice is based on the query and the schema alone.

What you get

• A diagnosis and, when the plan shows one, the main bottleneck.
• Rewrites, and index suggestions with the table, the columns, the reason and the CREATE INDEX statement.
• An estimated impact of low, medium or high.