A single Postgres index cut query time from 118ms to 0.06ms

A developer used Postgres’s EXPLAIN ANALYZE tool to find a performance bottleneck on a 1.2 million row table. A one-line fix delivered a 1,900x speedup.

A single query on a 1.2 million row table took 118.47 milliseconds to execute. The fix, a single line of SQL to add a database index, dropped that time to 0.061 milliseconds. This is a 1,900-fold performance improvement, documented in a technical walkthrough by a developer writing on Dev.to.

The case study provides a playbook for using built-in database tooling to diagnose and resolve a common scaling bottleneck. The performance degradation is often invisible during development with small datasets but becomes critical once a product has real user volume.

The 118ms sequential scan

The problem began with a simple lookup query on an orders table to find all records for a specific customer_id. The developer used the Postgres command EXPLAIN ANALYZE to inspect the database's execution plan. The output revealed a Seq Scan, or sequential scan.

Seq Scan on orders (cost=0.00..21453.00 rows=42 width=96) (actual time=0.021..118.442 rows=41 loops=1)
  Filter: (customer_id = 48291)
  Rows Removed by Filter: 1199959
Planning Time: 0.112 ms
Execution Time: 118.471 ms

A sequential scan forces the database to read every single row in the table and check if it matches the WHERE clause. The plan shows that out of approximately 1.2 million rows, 1,199,959 were read and discarded to find the 41 matching records. This is computationally expensive and scales linearly with table size. Double the rows, double the query time.

The one-line fix

To fix the sequential scan, the developer added an index to the customer_id column. An index is a separate, sorted data structure that maps column values to their physical row locations, allowing the database to find matching rows directly without scanning the entire table.

The command was a standard SQL statement:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

This trades slightly slower writes and increased storage for significantly faster reads on the indexed column. For a column frequently used in lookups, this is a standard and necessary trade-off.

The 0.06ms index scan

After the index was created, the developer ran the same EXPLAIN ANALYZE command. The new query plan showed a fundamental change in strategy from the database planner.

Index Scan using idx_orders_customer_id on orders (cost=0.42..8.53 rows=42 width=96) (actual time=0.018..0.041 rows=41 loops=1)
  Index Cond: (customer_id = 48291)
Planning Time: 0.098 ms
Execution Time: 0.061 ms

The plan now uses an Index Scan. Instead of reading all 1.2 million rows, the database used the new B-tree index to locate the 41 relevant rows in logarithmic time. The total execution time fell to 0.061 milliseconds. Execution time drops from 118ms to 0.06ms, roughly 1,900x, and the gap only widens as the table grows.

What We'd Change

This playbook is a textbook example of a fundamental database optimization. Its strength is its simplicity. The primary limitation is its narrow focus on a single, ideal use case. A founder applying this tactic needs to consider what the source material omits.

The post does not cover how to choose which columns to index. The benefit was clear for a foreign key (customer_id) used in a WHERE clause. The decision is less clear for columns with low cardinality (few unique values) or columns that are rarely queried. Over-indexing is a common performance anti-pattern, as every write to an indexed table must also update each of its indexes, slowing down INSERT and UPDATE operations.

It also does not address composite indexes, which cover multiple columns. These are essential for optimizing queries that filter or sort on several conditions simultaneously. Finally, the analysis is specific to Postgres. While the principles are universal, the diagnostic tools and syntax (EXPLAIN vs. EXPLAIN ANALYZE) differ across databases like MySQL or SQL Server.

Landing

This tactic is not a novel growth hack. It is a demonstration of the technical discipline required to scale a software product beyond its initial user base. As an application grows, performance bottlenecks migrate from inefficient application code to database constraints. The ability to use the database’s own diagnostic tools to identify and resolve these issues is a core engineering competency. It represents the shift from building a prototype to operating a service.

The investor read

This is a signal of technical execution and capital efficiency, not a market insight. For investors conducting due diligence on a product with early traction, this playbook is a proxy for evaluating engineering quality. A team that can't explain its indexing strategy or read a query plan may face significant, expensive scaling hurdles. The ability to diagnose and fix these bottlenecks with built-in tooling, rather than by throwing money at larger database instances, indicates a team that can manage technical debt and scale infrastructure cost-effectively. It is a positive indicator for bootstrapped or seed-stage companies where efficient scaling is paramount.

Pull quote: “Execution time drops from 118ms to 0.06ms, roughly 1,900x, and the gap only widens as the table grows.”