Documentationbreadcrumb arrow Grafana Cloudbreadcrumb arrow Monitor applicationsbreadcrumb arrow Database Observabilitybreadcrumb arrow Troubleshootbreadcrumb arrow Explain plans
Grafana Cloud

Troubleshoot explain plans

Database Observability can generate explain plans for captured query samples. Explain plans can be missing when Alloy intentionally skips a query, when the database returns no plan, or when the explain request fails.

Use this article to understand why an explain plan might not be returned, and how to resolve common issues. It’s best to first review your Alloy configuration for Database Observability, checking for incorrect or missing settings. Refer back to getting started guides for the reference configuration.

What you’ll achieve

In this article, you:

  • Understand why an explain plan might not be returned.
  • Identify when Alloy skips explain plan generation and why.
  • Resolve common causes of explain plan generation errors for MySQL and PostgreSQL.

Before you begin

Ensure you can access your Alloy deployment and view its logs. Keep the get started guides open to apply fixes:

How to interpret “no explain plan” outcomes

When Alloy processes explain plans, each query sample ends up in one of these outcomes:

  • success: Alloy generated and emitted an explain plan.
  • skipped: Alloy intentionally did not attempt to generate a plan for that query.
  • error: Alloy attempted to process the query but encountered an error.

The explain plan tab in the query details page will show the explain plan if it was generated successfully, or a more detailed message if it was skipped or failed.

Explain plans are skipped

Query belongs to an excluded schema

Alloy skips explain plans when the query belongs to a schema/database you excluded.

  • MySQL: SCHEMA_NAME excluded
  • PostgreSQL: database name (datname) excluded

What to do

  • If you want explain plans for that schema, remove it from your Database Observability component exclude_schemas configuration.
  • If you intended to exclude system databases, keep the denylist but confirm it does not include the application schema.

Query is truncated

Alloy skips explain plans when the captured query sample text ends with ..., which indicates it was truncated before Alloy received it.

Why this happens

  • The database only stored a shortened query sample text.
    • MySQL: query samples come from performance_schema.events_statements_summary_by_digest.QUERY_SAMPLE_TEXT
    • PostgreSQL: query text comes from pg_stat_statements.query

What to do

Query is denylisted

Alloy maintains an internal denylist for queries that hit certain non-recoverable failures. Once denylisted, Alloy skips them on subsequent scrapes.

What to do

  • Fix the underlying non-recoverable error condition (for example, permissions or connection policy).
    • Alloy will log the error the first time it occurs, and add the query to the denylist. Check the Alloy logs for details on what caused the error.
  • After the underlying issue is identified andfixed, the denylist may still take effect until Alloy is restarted (implementation-dependent). If you continue to see query denylisted, restart Alloy after validating the fix.

Query contains write, or non-query operations

Alloy will only generate explain plans for read-only queries which access tables or views. Consequently, this will skip queries which contain write verbs such as INSERT, UPDATE, DELETE, or CREATE, queries which do not access tables or views, or queries which contain non-query operations such as SELECT ... INTO.

What to do

  • Check the query sample text to confirm it is a read-only query which accesses tables or views.

Explain plans fail with errors

Failed to check for reserved keywords

Alloy checks queries for reserved keywords to determine if they should be skipped before running EXPLAIN. If that check fails, the reason will be shown in the explain plan tab, as well as recorded in the Alloy logs.

This should almost never occur, and might indicate the query is malformed or alloy is experiencing a broader issue.

What to do

  • Check Alloy logs for the reserved keyword check error.

Failed to run EXPLAIN / failed to scan explain plan JSON

Alloy can fail while executing the explain query or reading the result. The explain queries used for MySQL and PostgreSQL are:

  • MySQL: EXPLAIN FORMAT=JSON <query>
  • PostgreSQL: Alloy prepares the statement and runs:
    • PREPARE <name> AS <query>
    • SET search_path ...
    • SET plan_cache_mode = force_generic_plan
    • EXPLAIN (FORMAT JSON) EXECUTE <name>(...)

If any of these steps fail, Alloy logs the error and will retry on the next scrape interval. These errors will not be reflected in the explain plan tab of the UI, but will be recorded in the Alloy logs.

What to do

  • Confirm the monitoring user has enough permissions to explain the statement.
  • Confirm the query is valid SQL for the target database (query samples can include statements that aren’t explainable).
  • Confirm the query does not reference objects the monitoring user cannot access (schemas/tables/functions).
  • If you’re using schema/database exclusions, ensure the query’s schema/database is intended to be monitored.

Non-recoverable permission and connection-policy failures

Some failures are treated as non-recoverable and cause the query to be denylisted. Once denylisted, Alloy will skip it on subsequent scrapes.

PostgreSQL non-recoverable error examples

Alloy treats these as non-recoverable (examples include):

  • pq: permission denied
  • pq: pg_hba.conf rejects connection for host
  • pq: syntax error

What to do

  • Permissions:
    • Ensure the monitoring user can connect and has privileges required to prepare and explain statements.
  • pg_hba.conf:
    • Update host-based authentication to allow the Alloy host and monitoring user to connect.
  • Syntax errors:
    • Confirm the query sample text is complete and valid; truncated queries are skipped, but other malformed query samples can still occur.

MySQL non-recoverable error example

Alloy treats access denied errors as non-recoverable and denylists the query. One example is error code:

  • Error 1044 (access denied)

What to do

  • Ensure the monitoring user has permissions for the schema that contains the objects referenced by the query.
  • If you see access denied errors for application schemas, revisit:

Empty or invalid explain plan JSON

Alloy treats the following as failures:

  • Empty explain plan JSON response
  • Explain plan JSON that is not valid UTF-8
  • Explain plan JSON that cannot be parsed into the expected plan structure

These should be exceptionally rare, and might indicate the query is malformed or alloy is experiencing a broader issue.

What to do

  • Review the Alloy logs for details on what caused the error.
  • If you can reproduce with a specific query, run EXPLAIN FORMAT=JSON (MySQL) or EXPLAIN (FORMAT JSON) (PostgreSQL) manually to determine if the issue is with the query or alloy.

Next steps