Profiler quick start

The Query Profiler shows what your PostgreSQL server runs, live, in a grid modelled on Microsoft SQL Server Profiler. PostgreSQL has no trace API, so the profiler combines three sources: the server log, activity sampling (pg_stat_activity) and statement statistics (pg_stat_statements).

Your first trace

1. Profiler ▸ New Trace… (Ctrl+Alt+N, or Profiler in the toolbar). Pick or enter a connection; profiles are shared with the Database Explorer. The Trace Properties dialog opens with the default template. (In Tools ▸ Options you can skip the dialog and start immediately.)
2. Choose a template and adjust the events and columns. On the Capture tab, check the log source:
   • Automatic reads the server log through SQL when logging_collector = on and the role may read server files.
   • Otherwise pick a local file (for example /var/log/postgresql/…) or a command. For Docker the dialog suggests docker logs -f --tail 0 <container>.
   • Enable required logging settings automatically lets the profiler run ALTER SYSTEM for the parameters the selected events need (log_statement, log_min_duration_statement, log_connections, log_lock_waits, …) plus a machine-readable log_line_prefix, and restore them when the trace stops. Untick it on servers you must not reconfigure.
3. Press Run (F5) and use your application as usual.
4. Stop the trace (F7), then group it (Profiler ▸ Grouped View, Ctrl+G), save it, extract the SQL or replay it.

No server changes allowed? The Activity_Sampler template needs nothing beyond the pg_read_all_stats role. Very short statements can be missed between samples.

Templates

Standard, SQL, SQL_Duration, SQL_Grouped, SQL_Locks, SQL_Replay, SQL_SPs, Tuning, Errors_and_Warnings, Activity_Sampler, Server_Health and Blank. You can create your own templates (.pgtdf), derive one from a running trace, import and export them, and set one as the default.

Filters

Column filters use the Profiler operators and are applied at capture time:

• Like / Not like for text
• Equals / Not equal / Greater than or equal / Less than or equal for numbers and times
• Exclude rows that do not contain values

After capture you can also Filter displayed events, or right-click a value and choose Filter by this value or Exclude this value.

Keyboard shortcuts

Action	Key
Run / Pause / Stop	F5 / F6 / F7
Find / Find next	Ctrl+F / F3
Bookmark	Ctrl+F2
Grouped view	Ctrl+G
Estimated / actual plan	Ctrl+L / Ctrl+M
Replay: breakpoint / step	F9 / F10

Server settings used

Setting	Needed for
log_statement = all	SQL:StmtStarting, Extended:Parse/Bind/ExecuteStarting
log_min_duration_statement = 0	SQL:StmtCompleted, Extended:ExecuteCompleted / Fetch (durations)
log_connections = on, log_disconnections = on	Audit Login / Logout, Connection Received / Authenticated
log_lock_waits = on	Lock:Wait, Lock:Acquired (after deadlock_timeout)
log_temp_files = 0	Temp File
log_checkpoints = on, log_autovacuum_min_duration = 0	Server events
log_min_messages = notice	User Message (RAISE NOTICE)
auto_explain.log_min_duration = 0	Showplan (auto_explain must be preloaded)
log_line_prefix = '%m|%p|%c|%l|%q%u|%d|%h|%v|%x|%e|%Q|%b|%a|'	Session identity in stderr logs (any prefix is parsed; this one is recommended)

logging_collector and shared_preload_libraries need a server restart and are never changed by the profiler.

On production servers

Logging every statement has a cost on busy servers. Use column filters, limit the events, or use the sampler-only template on production systems: the same advice Microsoft gives for SQL Server Profiler.

Saving and replaying

• Save to .pgtrace files (JSON Lines, with a size limit and rollover) or to a PostgreSQL table, and open them later.
• Export CSV or JSON, or extract the statements as a SQL script.
• Replay captured statements against any server, with one connection per original session, original timing (with a speed factor) or as fast as possible, single step, run to cursor, breakpoints and pause on error.

Next

Select a statement and open the Execution Plan tab, then ask the AI Advisor.