Vì sao query PostgreSQL chạy chậm: 7 lỗi index ai cũng gặp · Pattern Quest
#postgresql
#performance
#sql
#spring-boot
#java
Vì sao query PostgreSQL chạy chậm: 7 lỗi index ai cũng gặpWhy PostgreSQL Queries Are Slow: 7 Common Index Mistakes
Liệt kê 7 lỗi thiết kế index thường gặp khiến PostgreSQL chậm và cách khắc phục trong SQL và Spring Boot.List 7 typical index design mistakes that slow PostgreSQL queries and show how to fix them in raw SQL and Spring Boot code.
Bạn đang ngồi trước màn hình, query PostgreSQL chạy chậm hơn dự kiến và log cho thấy "Seq Scan …". Thông thường nguyên nhân không phải do phần cứng mà là một vài lỗi thiết kế index mà hầu hết chúng ta vô tình mắc phải. Bài viết này liệt kê 7 lỗi index phổ biến và cách sửa ngay trong dự án Spring Boot / Java hoặc trong script SQL thuần.
1. Không có index cho cột dùng trong WHERE / JOIN#
Khi bạn lọc hoặc join bằng một cột không có index, PostgreSQL buộc phải thực hiện Seq Scan toàn bảng. Với bảng có hàng triệu bản ghi, thời gian có thể lên tới hàng giây.
SQL
-- Bảng đơn giảnCREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, created_at TIMESTAMP NOT NULL);-- Truy vấn chậmSELECT * FROM orders WHERE customer_id = 12345;
Giải pháp: tạo index trên customer_id.
SQL
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Sau khi tạo, EXPLAIN ANALYZE sẽ cho thấy Index Scan, thời gian giảm đáng kể.
2. Index trên cột có độ chọn lọc thấp (low‑selectivity)#
Nếu cột chỉ chứa một vài giá trị lặp lại (ví dụ status với "NEW", "PAID", "CANCELLED"), index sẽ không hữu ích vì PostgreSQL vẫn phải đọc phần lớn trang dữ liệu.
SQL
CREATE INDEX idx_orders_status ON orders(status);
Trong nhiều trường hợp, planner sẽ bỏ qua index này. Thay vì tạo index, bạn nên cân nhắc partial index hoặc bitmap index cho các giá trị quan trọng.
SQL
CREATE INDEX idx_orders_status_paid ON orders(status) WHERE status = 'PAID';
Partial index chỉ chứa các hàng có status='PAID', giảm kích thước và tăng hiệu quả.
Bạn có thể vô tình tạo nhiều index chứa cùng một cột, ví dụ idx_orders_customer_id và idx_orders_customer_id_status. Khi chỉ dùng customer_id trong truy vấn, PostgreSQL sẽ chọn một trong chúng, nhưng các index còn lại chỉ tiêu tốn không gian lưu trữ và làm chậm INSERT/UPDATE vì phải duy trì nhiều cấu trúc.
SQL
DROP INDEX IF EXISTS idx_orders_customer_id_status;
PostgreSQL không lưu các giá trị NULL trong b‑tree index, vì vậy nếu một cột có >90% giá trị là NULL, index sẽ gần như vô dụng.
SQL
ALTER TABLE orders ALTER COLUMN notes DROP NOT NULL; -- giả sử notes thường NULLCREATE INDEX idx_orders_notes ON orders(notes);
Thay vì tạo index, bạn nên chuyển cột sang jsonb và dùng GIN index nếu cần tìm kiếm nội dung.
6. Index bloat do UPDATE/DELETE liên tục và thiếu VACUUM#
Các bảng ghi nhật ký hoặc giỏ hàng thường được cập nhật liên tục. Nếu không chạy VACUUM (FULL) hoặc REINDEX, index sẽ bám đầy dead tuples, làm tăng I/O.
SQL
VACUUM (VERBOSE, ANALYZE) orders;REINDEX INDEX idx_orders_customer_id;
Lên lịch tự động autovacuum phù hợp với tần suất thay đổi.
Trong Java 21 + Spring Boot 3, bạn thường dùng Spring Data JPA. Đảm bảo repository method phản ánh đúng cột đã được index.
Java
@Repositorypublic interface OrderRepository extends JpaRepository<Order, Long> { // Spring sẽ tạo query dựa trên tên phương thức, sử dụng index nếu có List<Order> findByCustomerId(Long customerId); // Tránh dùng hàm trong JPQL, thay vào đó dùng native query nếu cần expression index @Query(value = "SELECT * FROM orders WHERE LOWER(status) = :status", nativeQuery = true) List<Order> findByStatusIgnoreCase(@Param("status") String status);}
Nếu bạn đã tạo idx_orders_status_lower, native query trên sẽ tận dụng index.
Đặt index trước khi viết code: xác định các cột dùng trong WHERE/JOIN.
Kiểm tra planner bằng EXPLAIN (ANALYZE, BUFFERS).
Tránh index low‑selectivity, dùng partial hoặc bitmap.
Xóa index thừa, giảm overhead ghi.
Sử dụng expression index khi cần hàm/ép kiểu.
Quản lý bloat: autovacuum, REINDEX định kỳ.
Chọn loại index phù hợp với dữ liệu (btree, GIN, GiST).
Áp dụng những bước này, hầu hết các query “Seq Scan” sẽ biến thành “Index Scan” và thời gian trả lời sẽ giảm từ giây xuống mili‑giây.
You are probably staring at a PostgreSQL query that takes seconds while the log says "Seq Scan …". Most of the time the bottleneck isn’t the hardware but a handful of index design mistakes most developers make without noticing. This article lists the 7 most common index errors and shows how to fix them both in raw SQL and in a Spring Boot / Java 21 project.
When you filter or join on a column that has no index, PostgreSQL is forced to perform a sequential scan of the whole table. On tables with millions of rows, that easily becomes a multi‑second operation.
SQL
-- Simple table definitionCREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, created_at TIMESTAMP NOT NULL);-- Slow query without an indexSELECT * FROM orders WHERE customer_id = 12345;
Solution: create an index on customer_id.
SQL
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
After the index, EXPLAIN ANALYZE will show an Index Scan and the latency drops dramatically.
If a column only contains a few distinct values (e.g., status with "NEW", "PAID", "CANCELLED"), an index is rarely useful because PostgreSQL would still need to read a large fraction of the table. The planner often ignores such an index.
SQL
CREATE INDEX idx_orders_status ON orders(status);
Instead, consider a partial index for the values you actually query.
SQL
CREATE INDEX idx_orders_status_paid ON orders(status) WHERE status = 'PAID';
The partial index stores only rows with status='PAID', making it small and fast.
It’s easy to end up with several indexes that contain the same leading column, for example idx_orders_customer_id and idx_orders_customer_id_status. When a query only uses customer_id, PostgreSQL will pick one of them, but the extra indexes still consume space and slow down INSERT/UPDATE because every index must be maintained.
SQL
DROP INDEX IF EXISTS idx_orders_customer_id_status;
PostgreSQL does not store NULL values in a b‑tree index. If a column has >90 % NULLs, the index provides almost no benefit while still adding write overhead.
SQL
ALTER TABLE orders ALTER COLUMN notes DROP NOT NULL; -- assume notes is often NULLCREATE INDEX idx_orders_notes ON orders(notes);
Instead of a regular index, consider moving the data to a jsonb column and using a GIN index if you need to search inside it.
6. Index bloat caused by frequent UPDATE/DELETE without VACUUM#
Tables that receive a lot of updates (e.g., shopping‑cart or audit‑log tables) can accumulate dead tuples inside their indexes. Without regular VACUUM or REINDEX, the index size grows and I/O increases.
SQL
VACUUM (VERBOSE, ANALYZE) orders;REINDEX INDEX idx_orders_customer_id;
Tune autovacuum parameters to match the update rate, or schedule periodic REINDEX.
In a Java 21 + Spring Boot 3 project you typically use Spring Data JPA. Make sure your repository methods target the indexed columns.
Java
@Repositorypublic interface OrderRepository extends JpaRepository<Order, Long> { // Spring derives the query from the method name and will use the index if it exists List<Order> findByCustomerId(Long customerId); // Avoid JPQL functions; use native query when you need an expression index @Query(value = "SELECT * FROM orders WHERE LOWER(status) = :status", nativeQuery = true) List<Order> findByStatusIgnoreCase(@Param("status") String status);}
If you created idx_orders_status_lower, the native query above will benefit from that index.
