Postgres slow-query audit, proven on a copy by Jago WesterduinPostgres slow-query audit, proven on a copy by Jago Westerduin

Postgres slow-query audit, proven on a copy

Portfolio project
Jago Westerduin
A Postgres slow-query audit that proves its own advice. For each slow query it reads the real execution plan, names the cause with the numbers from the plan, and proposes the fix: an index, a rewritten query, or both.
Then it proves the fix on a copy of the database. It builds the indexes, checks that every query returns the same rows, and measures before and after on the server. An index that does not pay for itself is dropped from the advice.
On a fictional shop with 1 million orders, ten slow queries went from 13.4 s to 116 ms in total. The slowest, the last order of each customer, read 249 million rows and took 11.6 s. One index brought it to 1.3 ms, and the same index served a second query.
Tested on 15 queries written after it was built, with the answers committed first. It named the cause in 8 of 10 problem queries, fixed 6, and left all 5 healthy queries alone.
Known limits, stated up front. The four it did not fix were predicted in advance: an OR across two columns, a prefix search, a cast to date, and deep paging on a query with its own filter. Standard-library Python and psql only, 14 automated tests.

0 likes · 0 views · Nov 21, 2026

Reads each slow query's plan, names the cause, proves the fix on a copy of the database. 13.4 s to 116 ms; held-out: 8 of 10 named, 5 of 5 healthy left alone.

Tags