0Pricing
SQL Academy · Lesson

Identifying and Fixing Slow Queries

Use pg_stat_statements, log_min_duration_statement, and EXPLAIN to find slow queries and apply targeted fixes.

Identifying and Fixing Slow Queries is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Step 1: Find the Slow Ones

Don't optimise blind. Use:

  • pg_stat_statements — top queries by total time
  • log_min_duration_statement — log queries above a threshold
  • pgBadger — pretty reports from logs

pg_stat_statements Setup

Enable the extension and configure shared_preload_libraries:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- After restart:
CREATE EXTENSION pg_stat_statements;

Top-10 Heaviest Queries

The single most useful query for any DBA:

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Log Slow Queries

Set a threshold and read the log:

-- postgresql.conf
log_min_duration_statement = '500ms'
-- All queries running > 500ms are logged.

Step 2: Reproduce with EXPLAIN ANALYZE

For each slow query, run EXPLAIN ANALYZE in a representative environment (production-like data). Look at:

  • Largest node by actual time
  • Biggest gap between estimated and actual rows
  • Whether the right indexes are used

Common Fixes

  • Missing index on a WHERE / JOIN column
  • Non-sargable predicate (function on column) — add expression index or rewrite
  • Stale stats — run ANALYZE
  • Wrong data type (causing implicit cast) — fix the column type
  • OR conditions — rewrite as UNION of single-condition queries
  • SELECT * fetching too much — narrow the projection

Stale Statistics

If estimated rows differ wildly from actual rows, ANALYZE first:

ANALYZE orders;
-- Or rely on autovacuum to do it periodically.

Index Sanity Check

List indexes on a table and their sizes:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

Unused Indexes

Find indexes that never get used:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Consider dropping them — they slow writes for no read benefit.

Lock Contention

Sometimes a query is "slow" because it's waiting for a lock. Check pg_stat_activity for wait_event:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

Query Rewrite Patterns

  • Move filters to WHERE
  • Replace correlated subquery in SELECT with JOIN + GROUP BY
  • Replace OR with UNION ALL of indexed queries
  • Use window functions instead of self-joins
  • Materialise repeated subqueries with CTEs (when planner is confused)

Iterate

Performance tuning is a loop: measure → hypothesise → change → measure. Don't guess.

Recap

Find slow queries with pg_stat_statements, diagnose with EXPLAIN ANALYZE, fix with indexes / ANALYZE / rewrites, and iterate.

Quick Check

Which PostgreSQL extension surfaces the top time-consuming queries by total runtime?

Frequently asked questions

Is the “Identifying and Fixing Slow Queries” lesson free?

Yes — the full text of “Identifying and Fixing Slow Queries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Identifying and Fixing Slow Queries”?

Use pg_stat_statements, log_min_duration_statement, and EXPLAIN to find slow queries and apply targeted fixes. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Identifying and Fixing Slow Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Reading EXPLAIN and EXPLAIN ANALYZE
  2. Sequential Scans vs Index Scans
  3. Hash Join vs Merge Join vs Nested Loop
  4. Identifying and Fixing Slow Queries
← Back to SQL Academy