---
title: "Analyze explain plans | Database Observability documentation"
description: "Use explain plans to understand query execution and identify optimization opportunities."
---

> For a curated documentation index, see [llms.txt](/llms.txt). For the complete documentation index, see [llms-full.txt](/llms-full.txt).

# Analyze explain plans

Explain plans show how the database executes your queries, including which indexes are used, join strategies, and estimated costs. Use this information to identify performance bottlenecks and optimization opportunities.

An explain plan describes the database’s query execution strategy:

- **Access methods**: How the database reads data (index scan, table scan)
- **Join strategies**: How tables are combined (nested loop, hash join, merge join)
- **Cost estimates**: Relative expense of each operation
- **Row estimates**: Expected number of rows processed at each step
- **Sort operations**: When and how data is sorted
- **Filter conditions**: Where row filtering occurs

## Access explain plans

To view an explain plan in Database Observability:

1. Navigate to **Database Observability** in Grafana Cloud.
2. In the Queries Overview, click a query to open its details.
3. Select the **Explain Plan** tab.
4. View the visual graph representation of the execution plan.

> Note
> 
> Explain plans are only available for SELECT queries. Write operations like INSERT, UPDATE, or DELETE, and non-query operations like COMMIT, SET, or VACUUM cannot be explained.

## Understand the visual graph

Database Observability displays explain plans as an interactive visual graph where each operation appears as a node connected by directed edges showing data flow.

### Graph navigation

Use these controls to navigate complex explain plans:

- **Zoom controls**: Located in the bottom-right corner for zooming in and out
- **Pan**: Click and drag to move around the graph
- **Fit to view**: Reset the view to show all nodes
- **Mini-map**: Appears in the bottom-left corner when the plan has more than three nodes, providing an overview and quick navigation

Data flows from left to right through the graph, with child operations feeding into parent operations.

### Node structure

Each node in the explain plan graph contains:

**Header:**

- **Operation name**: The type of operation (for example, Table Scan, Index Scan, Hash Join, Nested Loop Join)
- **Info icon**: Hover to see a plain-English explanation of what the operation does

**Statistics:**

- **Rows**: Estimated number of rows returned by this operation
- **Cost**: A color-coded bar gauge showing the operation’s percentage of total query cost, plus the absolute cost value

**Details section** (when available):

Expand table

| Field     | Description                                    |
|-----------|------------------------------------------------|
| Table     | Table name and alias being accessed            |
| Access    | Access type or scan method used                |
| Key       | Index or key used for the operation            |
| Join Type | Type of join being performed                   |
| Algorithm | Join algorithm used                            |
| Warning   | Database-reported warnings about the operation |
| Condition | Filter or join conditions applied              |

### Cost visualization

The cost bar gauge uses color coding to help you quickly identify expensive operations:

Expand table

| Color  | Cost percentage  | Meaning                                 |
|--------|------------------|-----------------------------------------|
| Green  | Less than 30%    | Normal cost                             |
| Yellow | 30% to 80%       | Elevated cost - review for optimization |
| Red    | Greater than 80% | High cost - likely optimization target  |

The absolute cost value appears next to the bar for precise comparison between operations.

### Slow operation highlighting

Operations that consume 30% or more of the total query cost are automatically highlighted:

- **Orange border** with glow effect around the node
- **Alert banner** displaying: “This step takes X% of the total query cost”

Focus your optimization efforts on these highlighted operations first, as they have the greatest impact on overall query performance.

## PostgreSQL operations

PostgreSQL explain plans use specific terminology for access methods and operations. The following operations appear as node titles in the visual graph.

### Scan types

Expand table

| Scan type           | Description                                         | Performance                                      |
|---------------------|-----------------------------------------------------|--------------------------------------------------|
| **Index Scan**      | Uses an index to find rows, then fetches from table | Fast for selective queries                       |
| **Index Only Scan** | Retrieves all needed data from index alone          | Fastest when possible                            |
| **Bitmap Scan**     | Builds a bitmap of matching rows from index         | Good for moderate selectivity                    |
| **Table Scan**      | Reads entire table row by row                       | Slow for large tables, acceptable for small ones |

### When table scans indicate problems

A table scan (sequential scan) isn’t always bad. Consider these factors:

Expand table

| Situation                          | Is it a problem?                            |
|------------------------------------|---------------------------------------------|
| Small table (fewer than 1000 rows) | No - table scan may be faster than index    |
| Query returns most rows            | No - index wouldn’t help                    |
| No suitable index exists           | Yes - consider adding an index              |
| Index exists but not used          | Yes - check query predicates and statistics |

### Join types

Expand table

| Join type            | Description                                          | Best for                                      |
|----------------------|------------------------------------------------------|-----------------------------------------------|
| **Nested Loop Join** | For each outer row, scan inner table                 | Small outer set, indexed inner table          |
| **Hash Join**        | Build hash table from smaller set, probe with larger | Medium to large tables without useful indexes |
| **Merge Join**       | Merge pre-sorted inputs                              | Pre-sorted data or when sort cost is low      |

### Other operations

Expand table

| Operation         | Description                                    |
|-------------------|------------------------------------------------|
| **Sort**          | Sorts input rows based on specified columns    |
| **Group**         | Groups rows with same values for aggregation   |
| **Filter**        | Eliminates rows that don’t satisfy conditions  |
| **Aggregate**     | Computes aggregate values (COUNT, SUM, AVG)    |
| **Limit**         | Restricts the number of rows returned          |
| **Hash**          | Creates a hash table for joins or aggregations |
| **Materialize**   | Saves intermediate results for repeated access |
| **Unique**        | Removes duplicate rows                         |
| **Append**        | Combines results from multiple operations      |
| **Subquery Scan** | Scans the results of a subquery                |
| **CTE Scan**      | Scans a Common Table Expression result         |

## MySQL operations

MySQL explain plans have different terminology and focus on access types. The following information appears in the node details.

### Access types

MySQL indicates the access method in the **Access** field, from best to worst:

Expand table

| Type        | Description                                                 | Performance                          |
|-------------|-------------------------------------------------------------|--------------------------------------|
| **system**  | Table has one row                                           | Fastest                              |
| **const**   | At most one matching row (primary key lookup)               | Very fast                            |
| **eq\_ref** | One row per combination from previous tables (unique index) | Fast                                 |
| **ref**     | Multiple rows using non-unique index                        | Good                                 |
| **range**   | Index range scan                                            | Good for bounded queries             |
| **index**   | Full index scan                                             | Better than ALL                      |
| **ALL**     | Full table scan                                             | Slowest - usually needs optimization |

### Watch for warning values

Watch for these values in the **Warning** field:

Expand table

| Warning           | Meaning                      | Optimization action                  |
|-------------------|------------------------------|--------------------------------------|
| `Using filesort`  | Results sorted on disk       | Add index that supports ORDER BY     |
| `Using temporary` | Temporary table created      | Simplify query or add covering index |
| `Using where`     | Filter applied after reading | Consider index on filter columns     |
| `Using index`     | Index-only access            | Good - no table access needed        |

## Common patterns and fixes

Use these patterns to identify and address common plan issues.

### Full table scan on large table

**Symptom:** Table Scan (PostgreSQL) or Access type ALL (MySQL) shown on nodes with high row counts.

**Causes:**

- No index on filtered columns
- Index exists but query predicate doesn’t match
- Statistics are outdated

**Fixes:**

1. Add an index on the WHERE clause columns
2. Update table statistics
3. Check if functions prevent index usage (for example, applying functions to columns in WHERE clauses)

### Sort operation without index

**Symptom:** Sort node appears in the plan, often with elevated cost.

**Fix:** Create an index that supports the ORDER BY clause columns.

### Inefficient join order

**Symptom:** Large row estimates flowing through Nested Loop Join nodes shown in high cost.

**Fixes:**

1. Ensure statistics are current on all joined tables
2. Add indexes on join columns

### Index not used despite existing

**Possible causes:**

Expand table

| Cause               | Solution                                          |
|---------------------|---------------------------------------------------|
| Type mismatch       | Ensure column and value types match               |
| Function on column  | Rewrite to apply functions to values, not columns |
| Outdated statistics | Update table statistics                           |
| Low selectivity     | Index may not help if most rows match             |

## Use explain insights for optimization

Follow this workflow when analyzing explain plans:

1. **Identify the most expensive operation**: Look for highlighted slow operations or nodes with high cost percentages
2. **Check access methods**: Are appropriate indexes being used? Look for Table Scan or ALL access types
3. **Look for warning signs**: Sort operations, temporary tables, full scans
4. **Test changes**: Add indexes or rewrite queries
5. **Verify improvement**: Compare before and after explain plans

After making changes, measure optimization impact:

1. Re-run the query and view the new explain plan
2. Compare cost percentages (should be lower for previously expensive operations)
3. Monitor actual performance in Database Observability
4. Check the Query Performance tab for duration changes

## Troubleshooting

Use these checks to resolve common issues when you view explain plans.

### Explain plan not available

If the Explain Plan tab shows no data or an error:

1. **Check permissions**: The monitoring user needs SELECT permissions on the tables referenced in the query
2. **Verify query syntax**: Some complex queries may fail to generate explain plans
3. **Check Alloy logs**: Look for explain plan collection errors

### Plan doesn’t match actual performance

Explain plans show estimates, not actual execution times. The estimates can be inaccurate when:

- Table statistics are outdated
- Data distribution is skewed
- Parameter values affect cardinality

Update table statistics regularly to improve estimate accuracy.

## Related documentation

- [Examine query samples](/docs/grafana-cloud/monitor-applications/database-observability/investigate/examine-query-samples/): See actual query executions
- [Understand wait events](/docs/grafana-cloud/monitor-applications/database-observability/investigate/understand-wait-events/): Identify resource bottlenecks
- [Improve slow queries](/docs/grafana-cloud/monitor-applications/database-observability/optimize/improve-slow-queries/): Complete optimization workflow
- [Troubleshoot explain plans](/docs/grafana-cloud/monitor-applications/database-observability/troubleshoot/explain/): Resolve explain plan issues