Bahadır Bilir

27 Oct 2026

Database optimisation: 876ms → 17ms

A factory measurement database where one search took most of a second. Three kinds of change, measured one at a time; the same search now takes 17 ms.

The problem

A process-control application on a factory measurement line keeps its reports in PostgreSQL: 9 million measured points, written into four tables, about 84 points to a report. The operator’s data page searches the recent 522 thousand. One search sends six SQL statements and took 681 ms with no filters and 876 ms with all 30 filters set, on a fast machine; on the original one, seconds. Every column was text, the measured values included, and no table had a key or an index.

The table the data page searches, before and after
Beforerecent measurement522 thousand rows, no key, no indexreport numbertexttaken attextpart identifiertextmeasured valuetextfilter fields × 30textAfterreport number in recent measurement points to report. dropdown fields × 2 in recent measurement points to dropdown value.reportnew table4,641 rows, 1.4 MBreport numbertextkeytaken attextindexpart identifiertextrecent measurement522 thousand rows, 5 indexesreport numbertexttaken attextindexpart identifiertextindexmeasured valuetextmeasured value as a numbernumericnewdropdown fields × 2textindexmost selective filters × 3textindexdropdown valuenew tablevaluetextkey

Names are general, not the application's. A line joins a column to the table it points to. The other 27 filter fields stay as they were.

What I changed

  • Indexes that match the questions: the page’s sort order, the distinct part count, the two dropdowns, and the three most selective filters. With every filter set, the page went from reading 26,301 blocks to 975.
  • Numbers stored as numbers: a numeric copy of each measured value, so a filter compares numbers instead of testing text with a regular expression and converting it on every row.
  • A table with one row per report: four of the six statements ask about reports, not points; 4,641 rows answer each in about 1 ms.

The result

One click, six statements: warm median at the client
  • No filters
  • Every filter set
  1. Original schema and statements681 ms876 ms
  2. 1. Index in the page's sort order432 ms766 ms
  3. 2. Index for the distinct count169 ms548 ms
  4. 3. Indexes for the two dropdowns171 ms438 ms
  5. 4. Index on the three most selective filters145 ms94 ms
  6. 5. Measured values also stored as numbers143 ms82 ms
  7. 6. A table with one row per report6.9 ms16.7 ms
No filtersEvery filter set
Original schema and statements681 ms876 ms
1. Index in the page's sort order432 ms766 ms
2. Index for the distinct count169 ms548 ms
3. Indexes for the two dropdowns171 ms438 ms
4. Index on the three most selective filters145 ms94 ms
5. Measured values also stored as numbers143 ms82 ms
6. A table with one row per report6.9 ms16.7 ms

Each step includes the ones before it. Measured on a restored copy (PostgreSQL 15.8), one change at a time, the median of three warm runs at the client.

17 ms instead of 876 with every filter set, 6.9 ms instead of 681 with none: 50 to 100 times faster. Each change was a migration of a few seconds; the indexes and numeric columns take 124 MB beside a 206 MB table, and the application’s original statements kept working after every step.