Cutting PostgreSQL query times from 14 seconds to 38 milliseconds
A B2B analytics platform reached out after their main tenant overview page started timing out with 504 Gateway errors. Their database was an AWS RDS PostgreSQL instance with 40 million event records. Whenever a user filtered logs by date range and tenant ID, the query took between 12 and 18 seconds to finish.
Reading the EXPLAIN ANALYZE truth
The developer team had already added indexes on tenant_id, created_at, and status as separate individual indexes. But running `EXPLAIN (ANALYZE, BUFFERS)` revealed that Postgres was performing a Sequential Scan, reading 3.4GB of table pages from disk into memory.
Why? Because individual single-column indexes forced Postgres to perform Bitmap Index Scans and merge bitmaps on millions of pointers. The query optimizer decided reading the whole heap sequentially was faster than hopping back and forth across index trees.
The composite and partial index fix
We restructured the indexing strategy based on exact query filter equality and range patterns:
1. Composite Index ordering: Equality columns first, range columns last. We created a composite index on `(tenant_id, status, created_at DESC)`. 2. Partial Indexing: 90% of dashboard queries only asked for records where `status = "completed"`. By creating a partial index `WHERE status = "completed"`, we shrunk the index size on disk from 2.8GB down to 310MB. 3. Index-Only Scans: We included the dashboard summation columns using the `INCLUDE` clause, allowing Postgres to return aggregate sums directly from the B-tree without touching the table heap.
Query refactoring for planner efficiency
We also discovered an antipattern in their ORM-generated SQL: `WHERE tenant_id = $1 AND DATE(created_at) = $2`. Wrapping indexed columns in functions disables B-tree index lookup entirely. Changing the condition to an explicit timestamp range `WHERE created_at >= $2 AND created_at < $3` allowed direct index range scans.
The result
Execution time dropped from 14,200ms to 38ms. RDS CPU utilization plummeted from 85% peak to under 12%, allowing the client to downgrade their instance type and save over $900 per month on cloud spend.
Have a similar challenge?
We build production-grade software for companies that need it done right.
Let's Talk
