Top SQL Analysis
The Top SQL section is one of the most valuable parts of the AWR report. It identifies the SQL statements that consumed the most resources during the report period, ranked by multiple dimensions across four tabs.
{screenshot: awr-top-sql-section}
Data Source
Top SQL data comes from pg_stat_statements deltas — the difference in cumulative counters between the begin and end snapshots. This gives you the exact resource consumption attributable to the report period, regardless of when those statements were first executed.
Ranking Tabs
Tab 1 — By Elapsed Time
Ranks queries by total elapsed execution time during the report period.
Best for: Identifying queries that consumed the most total database time — the primary bottleneck indicator.
| Column | Description |
|---|---|
| Rank | Position by elapsed time |
| Elapsed Time (s) | Total execution time |
| % of DB Time | Share of total DB Time |
| Executions | Number of executions |
| Avg Elapsed (ms) | Average time per execution |
| Rows | Total rows returned or affected |
| Query | Normalized SQL text |
Tab 2 — By CPU Time
Ranks queries by total CPU time consumed.
Best for: Identifying CPU-intensive queries — sorting, hashing, complex expressions.
Tab 3 — By Executions
Ranks queries by number of executions during the report period.
Best for: Identifying the highest-frequency queries — often small, fast queries that add up to significant load at scale.
Tab 4 — By I/O (Blocks Read)
Ranks queries by total physical block reads from disk.
Best for: Identifying queries causing the most disk I/O — often missing indexes or large sequential scans.
{screenshot: awr-top-sql-tabs}
Query Detail
Click on any query row to expand the full SQL text and additional statistics:
- Full normalized query text
- Execution plan hint (if available)
- Per-execution averages (time, rows, I/O)
- Database and user context
{screenshot: awr-top-sql-detail-expand}
Interpreting the Results
High Elapsed Time, Low Executions
→ A few executions of a slow query. Investigate the query plan — likely a missing index or a poorly optimized join.
High Executions, Low Avg Time
→ A high-frequency lightweight query. Usually acceptable, but check whether the cumulative load is significant. Consider connection pooling or query result caching at the application level.
High I/O, High Elapsed Time
→ Queries reading large amounts of data from disk. Suspect missing indexes, inadequate shared_buffers, or workload that cannot fit in memory.
High % of DB Time
→ Any query consuming more than 10–15% of total DB Time warrants immediate investigation.