PostgreSQL Query Optimization: Reducing API Response Time from 2.8 Seconds to 74 Milliseconds
PostgreSQL 查询优化：将 API 响应时间从 2.8 秒缩短至 74 毫秒
Introduction
Performance issues rarely announce themselves with obvious errors. More often, they appear as subtle increases in latency that gradually worsen as data volume grows. While working on a Node.js and PostgreSQL application, I noticed a user-facing API endpoint consistently taking approximately 2.8 seconds to respond. The endpoint was functionally correct, but its performance was unacceptable for production use. This article walks through the investigation process, root cause analysis, optimization strategy, and the changes that reduced response time to 74 milliseconds.
引言
性能问题很少会以明显的错误形式出现。更多时候，它们表现为延迟的细微增加，并随着数据量的增长而逐渐恶化。在开发一个基于 Node.js 和 PostgreSQL 的应用程序时，我注意到一个面向用户的 API 接口响应时间始终在 2.8 秒左右。该接口功能正常，但其性能无法满足生产环境的要求。本文将详细介绍调查过程、根本原因分析、优化策略，以及最终将响应时间缩短至 74 毫秒的改进措施。
The Problem
The API endpoint was responsible for retrieving customer orders. GET /api/orders?customerId=100 At first glance, nothing seemed unusual. However, monitoring revealed:
| Metric | Before |
|---|---|
| Average Response Time | 2.8s |
| Database Execution Time | 2.5s |
| Records in Orders Table | 1.2M+ |
| User Experience | Poor |
As traffic increased, response times continued to grow. The objective was clear: Identify the bottleneck and restore acceptable API performance.
问题所在
该 API 接口负责检索客户订单：GET /api/orders?customerId=100。乍一看，似乎没什么异常。然而，监控数据显示：
| 指标 | 优化前 |
|---|---|
| 平均响应时间 | 2.8秒 |
| 数据库执行时间 | 2.5秒 |
| 订单表记录数 | 120万+ |
| 用户体验 | 差 |
随着流量增加，响应时间持续增长。目标很明确：找出瓶颈并恢复可接受的 API 性能。
Application Stack
- Node.js
- Express.js
- PostgreSQL
- TypeScript
- REST APIs
- Database size: Orders Table: 1.2+ million rows, Customers Table: 350,000+ rows
技术栈
- Node.js
- Express.js
- PostgreSQL
- TypeScript
- REST APIs
- 数据库规模：订单表：120万+ 行，客户表：35万+ 行
Initial Investigation
The first assumption was that application logic might be responsible. I reviewed: Express middleware, API controller logic, Network latency, PostgreSQL logs. No significant issues appeared in the application layer. The next step was analyzing the database query itself.
初步调查
最初的假设是应用程序逻辑可能存在问题。我检查了：Express 中间件、API 控制器逻辑、网络延迟以及 PostgreSQL 日志。应用层并未发现明显问题。下一步是分析数据库查询本身。
The Query
The endpoint executed the following query:
SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC;
The query looked straightforward. However, execution time suggested otherwise.
查询语句
该接口执行了以下查询：
SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC;
查询看起来很简单，但执行时间却表明并非如此。
Query Analysis Using EXPLAIN ANALYZE
To understand PostgreSQL’s execution strategy, I used:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100 ORDER BY created_at DESC;
The output revealed the real problem: Seq Scan on orders. PostgreSQL was performing a sequential scan across the entire table. Instead of locating matching records efficiently, the database was examining over one million rows.
使用 EXPLAIN ANALYZE 进行查询分析
为了了解 PostgreSQL 的执行策略，我使用了：
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100 ORDER BY created_at DESC;
输出结果揭示了真正的问题：对 orders 表进行了全表扫描 (Seq Scan)。PostgreSQL 正在对整个表进行顺序扫描。数据库没有高效地定位匹配记录，而是检查了超过一百万行数据。
Root Cause
The filtering column lacked an index. Because customer_id was not indexed, PostgreSQL had no efficient path to locate matching records. Every request required: Reading large portions of the table, Filtering matching rows, Sorting results afterward. As table size increased, query performance degraded significantly.
根本原因
过滤列缺少索引。由于 customer_id 没有建立索引，PostgreSQL 没有高效的路径来定位匹配记录。每个请求都需要：读取表的大部分内容、过滤匹配行、随后对结果进行排序。随着表规模的增加，查询性能显著下降。
The Optimization Strategy
Step 1: Create an Index
The most obvious improvement was indexing the search column.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Performance improved immediately. However, there was still room for optimization.
Step 2: Optimize Sorting
The query also sorted results by creation date. A composite index was created:
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);
This allowed PostgreSQL to: Filter records efficiently, Return results in sorted order, Avoid additional sorting operations.
优化策略
第一步：创建索引
最明显的改进是对搜索列建立索引。
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
性能立即得到了提升。然而，仍有优化空间。
第二步：优化排序
查询还按创建日期对结果进行了排序。于是创建了一个复合索引：
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);
这使得 PostgreSQL 能够：高效过滤记录、按排序顺序返回结果、避免额外的排序操作。
Validation
After applying the changes, I reran EXPLAIN ANALYZE. The execution plan changed from Seq Scan to Index Scan. PostgreSQL was now using the new index correctly.
验证
应用更改后，我重新运行了 EXPLAIN ANALYZE。执行计划从 Seq Scan 变为了 Index Scan。PostgreSQL 现在可以正确使用新索引了。
Results
| Metric | Before Optimization | After Optimization |
|---|---|---|
| API Response Time | 2.8s | 74ms |
| Query Execution | Sequential Scan | Index Scan |
| CPU Usage | High | Reduced |
| User Experience | Poor | Excellent |
Improvement: 2.8 seconds → 74 milliseconds (Performance Improvement: ~97.4%). The endpoint became approximately 38 times faster.
结果
| 指标 | 优化前 | 优化后 |
|---|---|---|
| API 响应时间 | 2.8秒 | 74毫秒 |
| 查询执行方式 | 全表扫描 | 索引扫描 |
| CPU 使用率 | 高 | 降低 |
| 用户体验 | 差 | 极佳 |
提升： 2.8 秒 → 74 毫秒（性能提升约 97.4%）。该接口速度提升了约 38 倍。
Lessons Learned
- Database Performance Is Application Performance: Even perfectly written backend code cannot compensate for inefficient database queries. Understanding query execution plans is one of the most valuable skills for backend developers.
- Measure Before Optimizing: Assumptions often lead developers in the wrong direction. Tools such as
EXPLAIN ANALYZEprovide objective evidence of where bottlenecks exist. - Indexes Are Powerful but Strategic: Indexes dramatically improve read performance. However, they should be created thoughtfully because they introduce additional storage and write overhead.
- Small Changes Can Deliver Massive Impact: The final code change consisted of only a few lines of SQL. The majority of the effort was understanding the problem correctly.
经验教训
- 数据库性能即应用性能： 即使是编写得再完美的后端代码，也无法弥补低效数据库查询带来的损失。理解查询执行计划是后端开发人员最宝贵的技能之一。
- 优化前先测量： 假设往往会将开发人员引向错误的方向。像
EXPLAIN ANALYZE这样的工具可以提供关于瓶颈所在位置的客观证据。 - 索引功能强大但需策略： 索引能显著提高读取性能。但创建时应深思熟虑，因为它们会引入额外的存储和写入开销。
- 微小的改动能带来巨大的影响： 最终的代码更改仅包含几行 SQL。大部分工作在于正确理解问题本身。
Conclusion
This optimization reinforced an important engineering principle: Performance improvements are often achieved through better understanding, not more code. By analyzing query execution plans, identifying a missing index, and implementing a targeted optimization strategy, API response time dropped from 2.8 seconds to 74 milliseconds. For backend engineers working with PostgreSQL, regularly reviewing query plans and indexing strategies can prevent scalability bottlenecks long before they impact users.
结论
这次优化强化了一个重要的工程原则：性能提升往往是通过更好的理解，而不是编写更多的代码来实现的。通过分析查询执行计划、识别缺失的索引并实施针对性的优化策略，API 响应时间从 2.8 秒降至 74 毫秒。对于使用 PostgreSQL 的后端工程师来说，定期审查查询计划和索引策略，可以在扩展性瓶颈影响用户之前就将其防患于未然。