7 lỗi index PostgreSQL khiến query chậm và cách khắc phục · Pattern Quest
#postgresql
#performance
#sql
#spring-boot
#index
7 lỗi index PostgreSQL khiến query chậm và cách khắc phục7 PostgreSQL Index Mistakes That Slow Down Queries and How to Fix Them
Khám phá 7 lỗi thường gặp khi tạo index trong PostgreSQL và học cách sửa để query nhanh hơn.Discover the 7 most common PostgreSQL index mistakes and learn how to fix them for faster queries.
Bạn đang xem log và thấy một query chạy 5 giây, trong khi đồng nghiệp nói "đúng là chỉ mất 10 ms"? Thông thường vấn đề không phải ở hardware mà ở cách chúng ta thiết kế index. Bài viết này sẽ đưa ra 7 lỗi phổ biến mà hầu hết dev Java/Go gặp, kèm ví dụ thực tế và cách khắc phục ngay trong code.
1. Tạo index trên cột không được dùng trong WHERE hoặc JOIN#
Nếu bạn tạo CREATE INDEX idx_user_email ON users(email); nhưng hầu hết query chỉ dùng id để tìm, PostgreSQL sẽ bỏ qua index này và thực hiện seq scan. Kết quả: space lãng phí và planner phải cân nhắc nhiều hơn.
Cách sửa: Kiểm tra EXPLAIN (ANALYZE, BUFFERS) để xác định cột nào thực sự tham gia điều kiện, rồi chỉ tạo index trên những cột đó.
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 123;
Khi thực hiện SELECT * FROM orders o JOIN users u ON o.user_id = u.id, nếu orders.user_id không có index, PostgreSQL sẽ dùng nested loop với seq scan trên orders, gây nghẽn cổ chai.
Cách sửa: Tạo index bôi đè (covering index) hoặc composite index nếu có nhiều điều kiện.
SQL
CREATE INDEX idx_orders_user_id ON orders(user_id);
3. Index không phù hợp với kiểu dữ liệu hoặc biểu thức#
Ví dụ: cột created_at TIMESTAMP nhưng query lại dùng DATE(created_at) = '2024-09-01'. Index trên created_at không được sử dụng vì hàm DATE() làm mất khả năng sắp xếp.
Cách sửa: Tạo functional index.
SQL
CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));
Mỗi index tăng thời gian INSERT/UPDATE/DELETE vì PostgreSQL phải duy trì chúng. Nếu một bảng có 10+ index, mỗi lần ghi có thể mất vài ms, dẫn đến slowdown tổng thể.
Cách sửa: Đánh giá lại tần suất truy vấn, giữ lại chỉ những index thực sự cần thiết. Dùng pg_stat_user_indexes để xem số lần scan.
SQL
SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes WHERE relname = 'orders';
5. Không sử dụng partial index cho dữ liệu thưa xuất hiện#
Nếu bảng orders có 99 % trạng thái completed = true và chỉ 1 % là pending, một index toàn bộ trên status sẽ không giúp gì. Thay vào đó, tạo partial index cho pending.
SQL
CREATE INDEX idx_orders_pending ON orders(id) WHERE status = 'pending';
6. Không cân nhắc colummn order trong composite index#
Composite index (a, b) chỉ hữu ích khi WHERE a = ?hoặcWHERE a = ? AND b = ?. Nếu query chỉ lọc b mà không có a, index sẽ không được dùng.
Cách sửa: Đặt cột có tính selectivity cao (giá trị phân tán) ở vị trí đầu.
SQL
-- Đúng: a là country_code, b là user_idCREATE INDEX idx_users_country_user ON users(country_code, user_id);
7. Không cập nhật thống kê (ANALYZE) sau thay đổi dữ liệu lớn#
Planner dựa vào thống kê để quyết định dùng index hay seq scan. Khi bảng tăng nhanh mà chưa chạy ANALYZE, planner có thể chọn kế hoạch kém.
Cách sửa: Thiết lập autovacuum hoặc chạy thủ công.
Trong Spring Data JPA, bạn có thể chỉ định index trực tiếp trong entity bằng annotation @Table(indexes = @Index(columnList = "user_id")). Khi thêm hoặc sửa index, nhớ re‑generate migration và run ANALYZE.
Java
@Entity@Table(name = "orders", indexes = {@Index(name = "idx_orders_user_id", columnList = "user_id")})public class Order { @Id private Long id; private Long userId; private String status; private Timestamp createdAt;}
Sau khi áp dụng các fix, luôn chạy EXPLAIN (ANALYZE, BUFFERS) để xác nhận planner đã chuyển sang Index Scan. Nếu vẫn thấy Seq Scan, kiểm tra lại thứ tự cột trong composite index hoặc xem có hàm nào đang làm mất index.
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
Kiểm tra thực tế: dùng EXPLAIN để biết planner đang dùng gì.
Tạo index chỉ trên cột tham gia vào WHERE/JOIN, và đặt thứ tự hợp lý.
Sử dụng functional/partial index cho các biểu thức và dữ liệu thưa.
Giảm số lượng index không cần thiết, cập nhật thống kê thường xuyên.
Trong Spring Boot, khai báo index ở entity và chạy migration + ANALYZE.
Bạn đã gặp lỗi index nào trong dự án? Hãy chia sẻ trong phần bình luận!
You’re staring at a log line that says a query took 5 seconds, while a teammate swears the same call should finish in 10 ms. In most cases the culprit isn’t the hardware but the way we design indexes. This post walks through the 7 most common PostgreSQL index mistakes that even seasoned Java/Go devs make, with real‑world examples and instant fixes you can apply today.
1. Indexing a column that never appears in WHERE or JOIN#
Creating CREATE INDEX idx_user_email ON users(email); is useless if the majority of your queries filter by id only. PostgreSQL will ignore the index and fall back to a sequential scan, wasting disk space and planner time.
Fix: Run EXPLAIN (ANALYZE, BUFFERS) on your typical queries and only index columns that actually participate in predicates.
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 123;
Consider SELECT * FROM orders o JOIN users u ON o.user_id = u.id. If orders.user_id lacks an index, PostgreSQL will perform a nested‑loop with a seq scan on orders, becoming a bottleneck under load.
Fix: Add a simple index, or a covering/composite index if you also filter by other columns.
SQL
CREATE INDEX idx_orders_user_id ON orders(user_id);
A common pattern is a created_at TIMESTAMP column, but the query uses DATE(created_at) = '2024-09-01'. The plain index on created_at is useless because the DATE() cast breaks the ordering.
Fix: Create a functional index that stores the expression.
SQL
CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));
Every index adds overhead to INSERT/UPDATE/DELETE because PostgreSQL must maintain them. A table with ten+ indexes can add a few milliseconds per write, which adds up quickly in high‑throughput services.
Fix: Audit your indexes regularly. Use pg_stat_user_indexes to see how often each index is scanned and drop the dead weight.
SQL
SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes WHERE relname = 'orders';
If 99 % of rows in orders have status = 'completed' and only 1 % are pending, a full index on status provides little benefit. A partial index that only covers the pending rows is far more efficient.
SQL
CREATE INDEX idx_orders_pending ON orders(id) WHERE status = 'pending';
A composite index (a, b) is useful only when the query filters on aor on both a and b. If you query only b without a, the index is ignored. Place the column with the highest selectivity (most distinct values) first.
SQL
-- Correct: country_code is highly selective, user_id is secondaryCREATE INDEX idx_users_country_user ON users(country_code, user_id);
7. Forgetting to refresh statistics after massive data changes#
The planner relies on statistics to decide between an index scan and a sequential scan. When a table grows dramatically and you haven’t run ANALYZE, PostgreSQL may still choose a sub‑optimal plan.
Fix: Enable autovacuum or run it manually after bulk loads.
With Spring Data JPA you can declare indexes directly on the entity using @Table(indexes = @Index(columnList = "user_id")). After adding or modifying an index, generate a migration script and remember to run ANALYZE so the planner sees the new stats.
Java
@Entity@Table(name = "orders", indexes = {@Index(name = "idx_orders_user_id", columnList = "user_id")})public class Order { @Id private Long id; private Long userId; private String status; private Timestamp createdAt;}
After applying the changes, always re‑run EXPLAIN (ANALYZE, BUFFERS) to ensure the plan switched to an Index Scan. If you still see a Seq Scan, double‑check the column order in composite indexes or whether any function in the predicate is breaking the index.
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';