Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

PostgreSQLFlexQueryStoreRuntime — Query Store Runtime

Aggregated execution statistics per normalized query, per collection interval (default 15 min). Sourced from pg_stat_statements via Azure Query Store. Every execution is counted — no data is lost between collection windows.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
| project Queryid_str_s, Dbid_d, Userid_d, Query_type_s, Is_system_query_b,
          Start_time_t, End_time_t,
          Calls_d, Rows_d, Total_time_d, Mean_time_d, Min_time_d, Max_time_d, Stddev_time_d,
          Shared_blks_hit_d, Shared_blks_read_d, Shared_blks_dirtied_d, Shared_blks_written_d,
          Local_blks_hit_d, Local_blks_read_d, Local_blks_dirtied_d, Local_blks_written_d,
          Temp_blks_read_d, Temp_blks_written_d, Blk_read_time_d, Blk_write_time_d,
          AdditionalFields

Fields

Query identity

  • Queryid_d (number) — query fingerprint (numeric; may lose precision — prefer Queryid_str_s).
  • Queryid_str_s (string) — same fingerprint as a string (use this for joins/exact match).
  • Dbid_d (number) — database OID.
  • Userid_d (number) — role OID that ran the query.
  • Query_type_s (string) — statement type: select, insert, update, delete, or empty for utility commands.
  • Is_system_query_b (boolean) — whether it's an internal/system query.
  • AdditionalFields (dynamic) — extra context such as Search_path.

Collection window

  • Start_time_t / End_time_t (datetime) — bounds of the aggregation interval.
  • Runtime_stats_entry_id_d (number) — internal ID of the stats bucket in azure_sys.

Execution counters & timing (cumulative over the window)

  • Calls_d (number) — number of executions.
  • Rows_d (number) — total rows returned/affected.
  • Total_time_d (number, ms) — total execution time.
  • Mean_time_d / Min_time_d / Max_time_d / Stddev_time_d (number, ms) — per-call time distribution.

Buffer / block I/O

  • Shared_blks_hit_d / Shared_blks_read_d / Shared_blks_dirtied_d / Shared_blks_written_d (number) — shared buffer cache hits, disk reads, dirtied and written blocks.
  • Local_blks_hit_d / Local_blks_read_d / Local_blks_dirtied_d / Local_blks_written_d (number) — same for local (temp-table) buffers.
  • Temp_blks_read_d / Temp_blks_written_d (number) — temp file blocks read/written (spills).
  • Blk_read_time_d / Blk_write_time_d (number, ms) — time spent reading/writing blocks (needs track_io_timing).

For a deep explanation of the buffer I/O fields see Postgres — Buffer I/O explained.

Use cases — troubleshooting

  • Find the most expensive queries — rank by Total_time_d (overall load) or Mean_time_d (per-call slowness).
  • High CPU investigation — top queries by Total_time_d / Calls_d driving CPU.
  • Cache efficiency / I/O-bound queries — low Shared_blks_hit_d vs high Shared_blks_read_d, or high Blk_read_time_d.
  • Temp-file spills (work_mem too low) — non-zero Temp_blks_read_d / Temp_blks_written_d.
  • Write amplification — high Shared_blks_dirtied_d / Shared_blks_written_d.
  • Plan/perf regressions over time — compare Mean_time_d / Stddev_time_d for the same Queryid_str_s across windows.
  • Chatty queries — high Calls_d with low per-call cost (N+1 patterns).
  • Result-set blowups — unexpectedly large Rows_d.

Q&A

Q&A

Q: What does an empty Query_type_s mean?

Query_type_s classifies the statement as select, insert, update, or delete. An empty value means the statement is a utility command — anything outside those four DML categories: CREATE, ALTER, DROP, TRUNCATE, VACUUM, ANALYZE, COPY, SET, SHOW, BEGIN, COMMIT, ROLLBACK, EXPLAIN, etc. Query Store tracks these too (they have execution time and I/O cost), but they don't map to a DML type so the field is left blank.

Q: What is the "stats bucket" behind Runtime_stats_entry_id_d?

Query Store does not keep one row per execution. Instead it aggregates all executions of the same query fingerprint within a fixed time window (default 15 minutes, configurable via pg_qs.interval_length_minutes). That aggregated row is the stats bucket: one row per (Queryid, Dbid, Userid, time-window). Runtime_stats_entry_id_d is its internal primary key in query_store.runtime_stats in the azure_sys schema on the server. Not useful for direct analysis.

Q: What exactly does Calls_d count? And what does Rows_d count?

Calls_d is the number of times the Postgres query executor completed a full execution of that query pattern within the window — one parse → plan → execute cycle per call. It does not count individual syscalls, sub-queries, or I/O operations underneath.

Rows_d is the total rows processed at the output boundary of the executor across all calls:

TypeWhat Rows_d counts
SELECTRows returned to the client
INSERTRows inserted
UPDATERows updated
DELETERows deleted
UtilityUsually 0

A SELECT that scans 1 million rows to return 5 has Rows_d = 5 — the scan cost shows up in Shared_blks_read_d instead.

Q: What is Stddev_time_d?

Population standard deviation of per-execution duration (ms) across all Calls_d in the window: sqrt(sum((t_i − mean)²) / n).

MeanStddevInterpretation
lowlowFast and consistent — healthy
highlowSlow but consistent — likely a plan or index problem
anyhighUnpredictable — sometimes fast, sometimes slow
high>> meanBimodal: the query occasionally takes dramatically longer

A high stddev relative to mean points to: intermittent lock waits, parameter-dependent plan choices, cache cold-start effects, or data skew. Cross-reference with PostgreSQLFlexQueryStoreWaitStats for the same Queryid_str_s and window to see what the outlier executions were waiting on.

Q: Why are some Queryid values negative?

The query ID is a 64-bit hash of the normalized query parse tree (literals replaced with $1, $2… then hashed). Hash functions distribute values uniformly across all 64-bit patterns. When stored as a signed 64-bit integer, any hash whose most significant bit is 1 reads as negative — about half of all IDs. The sign is an artifact of signed integer interpretation, not a semantic distinction.

64-bit hash output:
  0xxxxxxx xxxxxxxx … → positive when read as int64
  1xxxxxxx xxxxxxxx … → negative when read as int64  (MSB = 1)

The normalization step makes the ID stable: WHERE id = 42 and WHERE id = 99 produce the same Queryid because both normalize to WHERE id = $1.

Queryid_d (float column) has only 53 bits of mantissa — a full 64-bit integer silently loses precision when cast to double, so two distinct queries can collide. Always use Queryid_str_s for joins and filtering.