Skip to main content
View rawEdit

02. Query performance analysis (top-N)

Identify which queries consume the most resources. This dashboard ranks queries by various dimensions so you can find the ones worth optimizing.

When to use​

  • Response time is high and you need to find the heaviest queries
  • Checkup reports pg_stat_statements issues (D004)
  • You need a queryid to drill into with Single query analysis

Key panels​

All panels show the top N queries (configurable via dashboard variable), ranked by different metrics:

By time​

  • Top N by execution time per second — total CPU/time consumed
  • Top N by execution time per call — average latency per query execution
  • Top N by planning time per second — planning overhead
  • Top N by planning time per call — average planning time

By throughput​

  • Top N by calls per second — most frequently executed queries
  • Top N by rows per second — queries returning/affecting the most rows
  • Top N by rows per call — queries with the largest result sets

By I/O​

  • Top N by shared_blks_hit per second — queries hitting the buffer cache most
  • Top N by shared_blks_read per second — queries reading from disk most
  • Top N by shared_blks_written per second — queries dirtying the most buffers
  • Top N by shared_blks_dirtied per second — queries creating the most dirty pages

What good looks like​

  • No single query dominates more than 30-40% of total execution time
  • Mean execution time per call is stable over time
  • Calls and rows per second align with expected application workload
  • Disk reads are a small fraction of buffer hits (good cache hit ratio)

What to investigate​

SignalNext step
One query dominates total timeClick the queryid to open Single query analysis
High execution time per callCheck the query plan — may need an index or rewrite
High planning timeConsider pg_hint_plan, prepared statements, or simpler query structure
High disk readsCheck if the working set fits in shared_buffers, or if the query needs a better index
Sudden spike in callsApplication behavior change — verify if expected
  • D004 — pg_stat_statements and pg_stat_kcache settings (CLI)
  • K003 — top queries by total time (monitoring stack)
  • K004 — top queries by temp bytes written (monitoring stack)
  • K006 — top queries by shared blocks read (monitoring stack)