MySQL Slow Query Log vs pg_stat_statements: Finding Bad Queries

Ask a MySQL operator how they find bad queries and you will usually hear a two-part answer: the slow query log for the outliers, and performance_schema digests or pt-query-digest for the aggregate picture. Ask a PostgreSQL operator and the answer is almost always pg_stat_statements first, with auto_explain and the statement log filling in the details. The two workflows solve the same problem and end up in similar places, but the defaults, the units of analysis, and the gaps are different enough that a migrating team should not assume their MySQL habits transfer one-to-one.

I have run query triage on both engines. This is the mapping I wish someone had handed me before my first PostgreSQL on-call rotation.

How MySQL sees its workload

The slow query log is the oldest tool in the MySQL kit and still a good one. Enable slow_query_log, set long_query_time to something honest (the default of 10 seconds is a smoke alarm, not a monitoring threshold; busy systems run at 0.5 or lower), and optionally add log_queries_not_using_indexes. Each captured statement arrives with real execution context: query time, lock time, rows examined versus rows sent. The log is event-based, so it catches the actual slow execution with its actual parameters, which matters when a query is only slow for certain values.

The aggregate view lives in performance_schema. The events_statements_summary_by_digest table normalizes statements into digests, replacing literals with placeholders, and accumulates counts, total and maximum latency, rows examined, temporary table usage, and index-usage indicators per digest.

SELECT schema_name,
       LEFT(digest_text, 100) AS digest_sample,
       count_star AS calls,
       ROUND(sum_timer_wait / 1e12, 1) AS total_sec,
       ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
       sum_rows_examined,
       sum_created_tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 15;

Between the log and the digest table, MySQL gives you both the outlier events and the aggregate shape. The sys schema wraps the digest data in friendlier views, and pt-query-digest turns the slow log into a ranked workload report that has anchored MySQL performance reviews for well over a decade. The operational chores are familiar to anyone who has run this stack: rotate the slow log before it eats the disk, resist the temptation of log_output set to TABLE on busy servers because the mysql.slow_log table becomes its own hotspot, and size the digest table's max rows so a diverse workload does not overflow into the catch-all NULL digest row, at which point your biggest offender becomes invisible.

How PostgreSQL sees its workload

PostgreSQL's center of gravity is pg_stat_statements, an extension shipped with the server but not enabled by default: it must be in shared_preload_libraries, which requires a restart, so turn it on at provisioning time rather than during your first incident. Once loaded, it maintains cumulative statistics per query fingerprint: calls, total and mean execution time, rows, shared buffer hits and reads, temporary block I/O, and, since PostgreSQL 13, separate planning-time counters. WAL generation per query family is tracked too, which has no MySQL digest equivalent and is surprisingly useful for finding write-amplifying statements.

SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       shared_blks_read,
       temp_blks_written,
       left(query, 100) AS sample_query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

The equivalent of the slow query log is log_min_duration_statement, which writes any statement exceeding a threshold to the server log with its duration and parameters. It is the event-level complement to pg_stat_statements' aggregates, exactly as the slow log complements digests, and it deserves the same threshold honesty: pick a value that reflects your latency budget, not a default. Set log_line_prefix so every entry carries the application name, user, and database, because a slow statement you cannot attribute to a service is only half a clue. The PostgreSQL slow query monitoring guide goes deeper on wiring these pieces into an actual workflow rather than a pile of settings.

auto_explain fills the plan gap

Here PostgreSQL has a tool MySQL operators will envy. The auto_explain module logs the execution plan of any statement that exceeds a duration threshold, automatically, at the moment the slowness happened. With auto_explain.log_analyze enabled you get actual row counts per plan node, and with log_buffers you see block I/O per node. This closes the most frustrating loop in query triage: a statement that was slow at 3 a.m. with a bad plan but is fast when you EXPLAIN it by hand at 10 a.m., because the plan flipped back. MySQL's slow log can record the statement but not the plan it ran with; performance_schema can tell you a full table scan happened but not show the tree. Be measured with log_analyze on hot systems, since timing instrumentation has overhead; a sampling rate via auto_explain.sample_rate is the usual compromise.

Fingerprinting: digests versus queryid

Both engines normalize queries so that a million executions with different literals collapse into one row, but the mechanics differ in ways that matter operationally. MySQL computes a digest from the parsed statement text, controlled by max_digest_length; overly long IN lists can still fragment into distinct digests across versions, though 8.0 folds value lists better than 5.7 did. PostgreSQL computes queryid from the post-parse-analysis tree, so it reflects the actual referenced objects; the same SQL text hitting different schemas via search_path produces different queryids, which is correct but surprises people. Two practical PostgreSQL notes: set pg_stat_statements.max high enough that your long tail is not evicted (the default of 5000 fingerprints is fine for most, tight for multi-tenant), and enable compute_query_id-consistent tooling so the queryid in pg_stat_statements matches what log-based tools report.

Keeping history, because neither keeps it for you

The dirty secret on both sides is that the built-in aggregate views are cumulative counters since the last reset, not time series. events_statements_summary_by_digest accumulates until truncated or restarted; pg_stat_statements accumulates until pg_stat_statements_reset() or a crash. A cumulative view answers "what is expensive overall" but not "what changed at 14:20," and the second question is the one incidents ask.

The standard fix is the same in both worlds: snapshot the view on an interval into a history table or an external store, and analyze deltas between snapshots. On MySQL, teams either schedule pt-query-digest runs against the slow log or snapshot the digest table. On PostgreSQL, teams snapshot pg_stat_statements, and the delta of calls and total_exec_time between two snapshots gives per-interval workload attribution. If you self-host and want to own this pipeline, the self-hosted PostgreSQL monitoring guide covers what to build; the honest summary is that snapshot plumbing is undifferentiated work that every team either builds badly once or buys.

Regression detection in practice

With history in place, regression detection is the same discipline on both engines, so the muscle memory transfers. Watch three signals per fingerprint: mean latency shift (a query moving from 20 ms to 200 ms), call-rate shift (an ORM change turning one query into N+1), and total-time share shift (a fingerprint quietly climbing from 2 percent to 30 percent of database time). Anchor comparisons to deploys, because nearly every genuine regression correlates with a code or schema change, and the before/after framing turns an argument into a diff. On PostgreSQL specifically, pair the pg_stat_statements delta with auto_explain output from the regression window: the counter tells you the query family regressed, the logged plan tells you why, and shared_blks_read exploding while rows stayed flat is the classic missing-index or flipped-plan signature.

How MonPG helps after you land on PostgreSQL

MonPG monitors PostgreSQL only, and this article's subject is essentially its core loop. It keeps pg_stat_statements history automatically, so per-interval deltas, latency trends per queryid, and total-time share are available without building snapshot plumbing; it correlates query regressions with deploys, lock waits, and vacuum behavior; and it surfaces plan-level context alongside the counters so triage does not require grepping server logs for auto_explain output at 3 a.m. For a team arriving from MySQL, the practical pitch is that the pt-query-digest-plus-cron workflow you would otherwise rebuild is the product. The slow query monitoring page shows what that looks like against a live workload.