Skip to content
The latest version of DbVisualizer was released 2024-03-11DOWNLOAD HERE ->

Analyzing (explain) Query Performance

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

You can analyze how a query is executed by the database, e.g. whether indexes are used or if the database has to do an expensive full scan. To analyze a query:

  1. Enter the query in the SQL Commander editor,
  2. Click Execute Explain Plan button in the toolbar,
  3. Look at the result in the results area.

Explain Plan is supported for Azure SQL Database, Db2 LUW, Exasol, Greenplum, H2, JavaDB/Derby, MariaDB, Microsoft Access, Mimer SQL, MySQL, Netezza, NuoDB, Oracle, PostgreSQL, Presto, Amazon Redshift, SQLite, Microsoft SQL Server, SingleStore, Trino, Vertica, and Yellowbrick. The available presentations options vary per database as shown in this table.

DatabaseGraph FormatTree FormatText FormatGrid FormatNode Cost Coloring (Graph and Tree only)
Azure SQL DatabaseCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
ClickHouseCheckmark icon indicating availability
Db2 LUWCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
ExasolCheckmark icon indicating availability
GreenplumCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
H2Checkmark icon indicating availability
JavaDB/DerbyCheckmark icon indicating availability
Microsoft AccessCheckmark icon indicating availability
Mimer SQLCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
MariaDBCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
MySQLCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
NetezzaCheckmark icon indicating availability
NuoDBCheckmark icon indicating availability
OracleCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
PostgreSQLCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
PrestoCheckmark icon indicating availability
RedshiftCheckmark icon indicating availability
SingleStoreCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
SQLiteCheckmark icon indicating availability
Microsoft SQL ServerCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability
TrinoCheckmark icon indicating availability
VerticaCheckmark icon indicating availability
YellowbrickCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availabilityCheckmark icon indicating availability

Explain Plan executes your query and records the plan that the database devises to execute it. By examining this plan, you can find out if the database is picking the right indexes and joining your tables in the most efficient manner. The explain plan feature works much the same as executing SQLs to present result sets; you may highlight statements, run a script or load from file. The explain plan results can easily be compared by pinning the tabs for different runs.

DbVisualizer presents the plan either in a tree style format or in a graph, or in a simple text format. The information depends on the database you use. In the tree view, put the mouse pointer on the column header for a tooltip description what that column represents.

The following screenshot shows the SQL in the editor at top and the resulting explain plan as a tree view. The relative cost is indicated using colored adornments on each node and you can select a node to see the details (if Show Details is checked).

Explain Plan result in a tree view format

The Graph View shows the plan as a graph. You can zoom in or out, choose detail levels, export it to an image file or print it using the toolbar buttons. The relative cost is indicated by node color and you can select a node to see the details (if Show Details is checked).

Explain Plan result in a graph view format

The databases use different techniques to manage their explain plan support. You can make database-specific configurations in the Properties tab for a connection, in the Explain Plan category.