No query examples in PMM QAN with pg_stat_monitor normalized queries enabled

Percona Monitoring and Management (PMM)
1

Hi

I’m using PMM 3.6.0 to monitor PostgreSQL and I’m seeing an issue in QAN: for some queries, the Examples section shows the message:

“Sorry, no examples found for this query”

This happens when pg_stat_monitor is enabled and I use normalized queries.

Environment

  • PMM: 3.6.0

  • Database: PostgreSQL

  • Extension: pg_stat_monitor loaded via shared_preload_libraries

pg_stat_monitor settings

pg_stat_monitor.pgsm_normalized_query = on
pg_stat_monitor.pgsm_extract_comments = off
pg_stat_monitor.pgsm_query_max_len = 8192
pg_stat_monitor.pgsm_enable_query_plan = on

What I observed

  • With pg_stat_monitor.pgsm_normalized_query=on, QAN shows the aggregated query, but no query example is available (message above).

  • If I set pg_stat_monitor.pgsm_normalized_query=off, then QAN does show an example query.

  • However, with normalized_query=off, QAN no longer shows Table and Plan details (at least not as expected).

Questions

  1. Is this expected behavior when pgsm_normalized_query=on, or is this a bug/compatibility issue between PMM QAN and pg_stat_monitor?

  2. Does PMM require raw (non-normalized) query text to display Examples, while Table/Plan require normalized queries?

  3. Are there additional settings or known limitations (e.g., query length, permissions, plan collection requirements) that would prevent Examples from being stored/displayed when normalized queries are enabled?

If needed, I can provide screenshots or additional logs/metadata.

Thanks.

2

Hi @choi,

This is expected behavior. Our PMM PostgreSQL setup docs recommend pgsm_normalized_query=1 but don’t explain this trade-off, so the confusion makes sense.

When pgsm_normalized_query=on, pg_stat_monitor replaces all literal values with placeholders ($1, $2, etc.) in the query column, and that is the only place the query text is stored. The original query with actual parameter values is never retained. PMM’s QAN agent reads that column, and since it only contains placeholders, there is nothing to show as an example. The “no examples found” message is accurate: the data genuinely does not exist.

I verified this on percona-distribution-postgresql:17 with pg_stat_monitor 2.3.2. With pgsm_normalized_query=off, the query column stores the first-seen actual text (e.g., WHERE id = 1 instead of WHERE id = $1), so PMM can use it as an example. Aggregation in pg_stat_monitor stays correct either way because queryid is always computed from the normalized form. The Table/Plan issues you saw when switching to off likely come from the PMM agent side, which handles fingerprint generation differently depending on the mode.

One thing to note: if your application uses prepared statements or parameterized queries (most ORMs and connection pools do), the query text already contains $1, $2 at the protocol level. In that case pgsm_normalized_query=off only helps for queries with inline literal values.

Also worth checking: confirm the QAN agent type is postgresql_pgstatmonitor_agent (not pgstatstatements) on the PMM Inventory page, and that the service was not added with --disable-queryexamples.

This gap is tracked in PMM-13195. There is also an open pg_stat_monitor PR #350 that would add a bind_variables column to store actual values alongside normalized queries, but it has been stalled since 2023.

References: