Postgres with Drizzle · Chapter 38 of 40
The query detective
By the end of this chapter you can ask Postgres which queries are slow, why, and which tables are read row by row, from statistics it already keeps. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Mastery
- 7 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Rohan's dashboard takes seconds to load and nobody knows which query is to blame: the feed, the notifications, or the friend suggestions. Vikram asks for the slowest queries by tomorrow.
Wrapping every database call in console.time() would mean editing hundreds of call sites, and would still miss queries from scripts, migrations and psql. It also measures the network and the wait for a pool connection, not the work Postgres did. Postgres can count and time every statement itself, and explain how it ran each one.
What you build: 7 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
enablePgStatStatements: turn on the statement counter
Write enablePgStatStatements(). It creates the extension pg_stat_statements if it does not exist yet, then counts the rows of the pg_stat_statements view.
Send raw SQL with getPool().query. Return an object with enabled: true and queryCount: that count, a number, not a string.
- Task 2
getTopSlowQueries: the slowest statements first
Write getTopSlowQueries(limit). limit is a number. It reads pg_stat_statements and returns at most limit rows, slowest first by mean_exec_time (the average time per call), with the columns query, calls, avg_ms (mean_exec_time), total_ms (total_exec_time) and rows.
Return result.rows. Order: mean_exec_time, highest first. The check asks for 5 and compares the first two avg_ms values.
- Task 3
explainQuery: the plan of any query, run for real
Write explainQuery(query). query is a complete SQL statement as a string (the check passes a select on users). It runs that statement under EXPLAIN with the options ANALYZE, BUFFERS and FORMAT JSON.
Return the plan itself: the value of the QUERY PLAN column in the one row EXPLAIN returns, an array whose first element has Plan (with its Node Type) and Execution Time (a number).
- Task 4
detectSeqScans: how often a table was read row by row
Write detectSeqScans(tableName). tableName is a table's name (the check passes users). It reads that table's row from pg_stat_user_tables, with the columns schemaname, relname, seq_scan, seq_tup_read, idx_scan and idx_tup_fetch.
Return that row as an object. seq_scan and idx_scan must be numbers, not strings.
- Task 5
detectSeqScans: null for a table that does not exist
The same detectSeqScans(tableName) must return null, not undefined and not an empty object, when no table has that name (the check passes nonexistent_table_xyz).
- Task 6
suggestIndexes: does a table need better indexes?
Write suggestIndexes(tableName). It reads seq_scan, idx_scan and n_live_tup for that table from pg_stat_user_tables, and the table's indexes from pg_stat_user_indexes: index_name (indexrelname) and times_used (idx_scan), most used first.
Return an object with exactly tableName, seqScans, idxScans and rowEstimate (all three numbers, not strings), needsAttention (a boolean: true when seqScans is greater than idxScans and rowEstimate is over 100) and existingIndexes (the index rows, an array). The check passes users and expects at least its primary key's index.
- Task 7
resetQueryStats: start counting from zero
Write resetQueryStats(). It empties pg_stat_statements by calling the function pg_stat_statements_reset(), then counts the rows of the view again.
Return an object with reset: true and queryCountAfter: that count, a number. Right after a reset it is small (the check wants under 10).
What you will learn
- pg_stat_statements: one row per kind of statement, with how often it ran and how long it took.
- EXPLAIN ANALYZE: the plan Postgres chose, run for real, with real times and row counts.
- pg_stat_user_tables and pg_stat_user_indexes: how often each table was read row by row, and how often each index was used.
- pg_stat_statements_reset(): start counting again from zero.
More in Mastery
Build The query detective yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.