8.8M rows
Dataset
666×
Best speedup
99.85%
Best latency reduction
Most performance complaints sound like: “The database is slow, we need a bigger server.” Usually, that’s the wrong diagnosis. The real culprit is often the query plan — how PostgreSQL decides to fetch your data. To demonstrate this, I built two identical APIs against the same 8.8 million row IMDB dataset. Same server. Same data. Same queries. Only the query plan was different.
Forced sequential scan on the unoptimized pool
Rather than writing a broken query, I disabled the index scanner at the PostgreSQL connection level using SET enable_indexscan = OFF, SET enable_bitmapscan = OFF, and SET enable_indexonlyscan = OFF. This replicates what happens in production when queries are written poorly — functions in WHERE clauses, type mismatches, or missing indexes on filtered columns.
Used proper indexes on the optimized pool
The optimized connection lets PostgreSQL use the indexes that are already in place: on title_principals(nconst), title_principals(category_id), and title_genres(genre_id). With indexes available, PostgreSQL jumps directly to relevant rows instead of scanning all 8.8 million of them.
Separated the two pools at the connection level
Each pool uses AfterConnect to apply its configuration on every new connection. This ensures the unoptimized pool consistently forces seq scans — making the benchmark repeatable and fair.
| Endpoint | Unoptimized | Optimized | Reduction | Speedup |
|---|---|---|---|---|
| /movies | 2,876 ms | 26.6 ms | 99.08% | 108× |
| /movies?title=batman | 3,711 ms | 409 ms | 88.97% | 9× |
| /movies/:id | 422 ms | 0.6 ms | 99.85% | 666× |
Before adding RAM or upgrading your database instance, run EXPLAIN ANALYZEon your slow queries. If you see “Seq Scan” on a large table, that’s your bottleneck — not the server. The right index on the right column can turn a 3-second query into a sub-millisecond one.