Skip to main content

Queries tab

The Queries tab is built around pg_stat_statements. It is used to find query patterns that consume the most time or show unstable latency.

Requirements​

pg_stat_statements must be installed and loaded.

Without it:

  • query-level ranking is limited
  • several tuning signals become weaker
  • the app cannot show the same level of query evidence

Top queries​

Queries are primarily ranked by total execution time. Each row can show:

  • normalized query text from pg_stat_statements
  • total execution time
  • calls
  • average time
  • rows
  • P95 and P99 latency estimates

Tail latency estimation​

pg_stat_statements is a summary extension. It does not store every execution sample.

When only summary timing data is available, pocketPG estimates tail latency from those summary fields. Very low-volume queries are filtered so one-off calls do not create misleading alerts.

Tail latency insights​

The page highlights queries whose latency tail, variance, or total time looks operationally important.

The purpose is not to claim that every slow query needs tuning. The purpose is to separate:

  • expensive query patterns
  • unstable query patterns
  • noisy but low-impact query patterns

Wait event correlation​

On PostgreSQL 14 and later, pocketPG can add current wait-event context from pg_stat_activity when a matching query is actively running. That helps distinguish lock waits, I/O waits, lightweight lock pressure, and other runtime causes.

Extension recommendations​

After query analysis, the app may also suggest extension-related follow-up ideas such as:

  • trigram indexing for substring search workloads
  • JSONB indexing when JSON containment or path access appears frequently
  • partition-management tooling for large time-oriented workloads
  • plan-capture tooling for highly variable or very slow statements
  • maintenance tooling when bloat and vacuum lag appear together
  • prepared-statement guidance when planning overhead is visible

Already-installed extensions are excluded automatically.

Query regression detection​

The browser keeps a lightweight local baseline between page loads. If a query becomes materially slower relative to that baseline, the UI can mark it as regressed.

This is a convenience signal, not a historical performance warehouse.

Caching​

Query data is short-lived cached so repeated page loads do not hammer pg_stat_statements unnecessarily.