Pattern QuestDesign Pattern · Java ⇄ GoDesign Patterns · Java ⇄ Go

#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

Giải thích 7 lỗi index thường gặp khiến query PostgreSQL chậm và cách khắc phục thực tiễn với SQL và Spring Boot.Explains 7 typical index mistakes that slow down PostgreSQL queries and shows practical fixes with SQL and Spring Boot.

2 tháng 11, 2026November 2, 20264 phút đọc4 min read300

Mục lụcContents· 9

1. Không có index cho cột WHERE
2. Index trên cột không đủ selectivity
3. Sử dụng hàm trên cột indexed
4. Điều kiện OR khiến planner bỏ index
5. Index không được sử dụng vì ORDER BY không phù hợp
6. Thống kê (ANALYZE) lỗi hoặc chưa chạy
7. Quá nhiều index gây overhead ghi
Quy tắc nhanh
Tóm tắt

Bạn có bao giờ chạy một truy vấn trong Spring Boot, thấy thời gian phản hồi tăng từ 20ms lên vài giây chỉ vì một thay đổi nhỏ trong bảng? Ở phần này mình sẽ liệt kê 7 lỗi index mà hầu hết dev Java/Go gặp, kèm ví dụ thực tế và cách sửa ngay trên PostgreSQL.

1. Không có index cho cột WHERE#

Khi câu lệnh SELECT … WHERE user_id = ? chạy trên bảng có hàng triệu bản ghi mà không có index, PostgreSQL phải thực hiện full table scan. Kết quả: p99 latency tăng mạnh.

SQL

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;
-- Seq Scan on orders  (cost=0.00..12345.00 rows=1 width=123) (actual time=0.015..250.123 rows=0 loops=1)

Vì sao query PostgreSQL chạy chậm: 7 lỗi index ai cũng gặp · Pattern Quest

Ever run a Spring Boot endpoint and watch the response time jump from 20 ms to several seconds after a tiny schema change? Most of the time it’s an index mistake in PostgreSQL. Below are the 7 most common index pitfalls you’ll encounter, with concrete SQL snippets and the exact fix you can apply right now.

1. Missing index on the WHERE column#

If you query SELECT … WHERE user_id = ? on a table with millions of rows and there is no index, PostgreSQL performs a full table scan. The p99 latency can explode.

SQL

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;
-- Seq Scan on orders  (cost=0.00..12345.00 rows=1 width=123) (actual time=0.015..250.123 rows=0 loops=1)

Fix: Create a simple index on the frequently filtered column.

SQL

CREATE INDEX idx_orders_user_id ON orders(user_id);

With Spring Data JPA you don’t need to change any Java code – just run the migration.

2. Low‑selectivity index#

An index on a column that only holds a few distinct values (e.g. status with ('NEW','PAID','CANCELLED')) provides little benefit because many rows share the same value.

SQL

CREATE INDEX idx_orders_status ON orders(status);
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'NEW';
-- Planner still prefers Seq Scan due to low selectivity.

Fix: Either drop the index or build a composite index that adds a more selective column.

SQL

CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Now the planner can filter by status and immediately order by created_at.

3. Applying a function on an indexed column#

When you write LOWER(email) = '[email protected]', PostgreSQL cannot use the plain idx_users_email because the column is wrapped in a function.

SQL

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

Fix: Create an expression index that stores the computed value.

SQL

CREATE INDEX idx_users_email_lower ON users((LOWER(email)));

Alternatively, store email already in lower‑case in your application layer.

4. OR conditions that defeat the planner#

A clause like WHERE country = 'VN' OR country = 'TH' may force the optimizer to fall back to a bitmap heap scan instead of using a simple index.

SQL

EXPLAIN ANALYZE SELECT * FROM customers WHERE country = 'VN' OR country = 'TH';

Fix: Prefer IN (PostgreSQL optimises it) or split into two queries and UNION ALL.

SQL

SELECT * FROM customers WHERE country IN ('VN','TH');

You can also add a partial index for the most common values.

SQL

CREATE INDEX idx_customers_vn_th ON customers(country) WHERE country IN ('VN','TH');

5. ORDER BY direction mismatches the index#

If you need ORDER BY created_at DESC but the index is built ASC, PostgreSQL will still have to sort after fetching rows.

SQL

CREATE INDEX idx_orders_created ON orders(created_at);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC;

Fix: Build a descending index or a covering index that matches both the filter and the sort order.

SQL

CREATE INDEX idx_orders_user_created_desc ON orders(user_id, created_at DESC);

Now the planner can return rows already sorted, eliminating the extra sort step.

6. Stale or missing statistics (ANALYZE)#

After bulk inserts, updates, or deletes, PostgreSQL needs fresh statistics for the planner to choose the right plan. Out‑of‑date stats lead to sub‑optimal index usage.

Shell

VACUUM ANALYZE orders;

In CI/CD pipelines, run ANALYZE after migrations or tune autovacuum settings appropriately.

7. Too many indexes – write‑side penalty#

Every INSERT/UPDATE must update all indexes on the table. If a table carries 10‑15 indexes, write throughput can drop dramatically.

SQL

-- List indexes on a table
SELECT indexname FROM pg_indexes WHERE schemaname='public' AND tablename='orders';

Fix: Remove indexes that are rarely used. Use pg_stat_user_indexes to see hit counts.

SQL

SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname='orders' ORDER BY idx_scan ASC;

Keep only those with a significant idx_scan count.

Quick rule‑of‑thumb checklist#

WHERE column → single‑column index.
If selectivity < 5 % → consider composite index.
Avoid functions on indexed columns; use expression indexes instead.
Prefer IN over multiple OR clauses.
Match index direction (ASC/DESC) with ORDER BY.
Run ANALYZE after large data changes.
Periodically prune unused indexes to protect write performance.

Follow this checklist and you’ll typically see query times drop from seconds to under 100 ms.

Key takeaways#

Index the columns used in WHERE and ORDER BY.
Use composite or expression indexes when needed.
Keep statistics fresh with ANALYZE.
Remove indexes that are never scanned.
Test with EXPLAIN ANALYZE to verify the planner’s choice.

Which index mistake surprised you the most? Share your story in the comments!

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

1. Không có index cho cột WHERE#

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

1. Không có index cho cột WHERE#

2. Index trên cột không đủ selectivity#

3. Sử dụng hàm trên cột indexed#

4. Điều kiện OR khiến planner bỏ index#

5. Index không được sử dụng vì ORDER BY không phù hợp#

6. Thống kê (ANALYZE) lỗi hoặc chưa chạy#

7. Quá nhiều index gây overhead ghi#

Quy tắc nhanh#

Tóm tắt#

1. Missing index on the WHERE column#

2. Low‑selectivity index#

3. Applying a function on an indexed column#

4. OR conditions that defeat the planner#

5. ORDER BY direction mismatches the index#

6. Stale or missing statistics (ANALYZE)#

7. Too many indexes – write‑side penalty#

Quick rule‑of‑thumb checklist#

Key takeaways#

Thảo luậnDiscussion

1. Không có index cho cột WHERE#

1. Không có index cho cột WHERE#

2. Index trên cột không đủ selectivity#

3. Sử dụng hàm trên cột indexed#

4. Điều kiện OR khiến planner bỏ index#

5. Index không được sử dụng vì ORDER BY không phù hợp#

6. Thống kê (ANALYZE) lỗi hoặc chưa chạy#

7. Quá nhiều index gây overhead ghi#

Quy tắc nhanh#

Tóm tắt#

1. Missing index on the WHERE column#

2. Low‑selectivity index#

3. Applying a function on an indexed column#

4. OR conditions that defeat the planner#

5. ORDER BY direction mismatches the index#

6. Stale or missing statistics (ANALYZE)#

7. Too many indexes – write‑side penalty#

Quick rule‑of‑thumb checklist#

Key takeaways#

Bài học liên quanRelated lessons

Repository

Thảo luậnDiscussion