Demystify query performance in PostgreSQL. Learn how sequential scans, index scans, bitmap heap scans, and memory work behind every high-speed SQL query.
## Why Your Queries Are Slow: Index Scans vs Seq Scans
When you execute `SELECT * FROM orders WHERE customer_id = 4521`, PostgreSQL's query planner evaluates multiple candidate execution paths:
1. **Sequential Scan (Seq Scan):** Reads every single 8KB disk page on the table sequentially. O(N) complexity.
2. **Index Scan:** Traverses the balanced tree (B-Tree) in O(log N) steps to fetch exact row pointers (TIDs).
3. **Bitmap Index Scan:** Constructs an in-memory bitmask of matching disk pages when returning multiple matches.
---
## Visualizing EXPLAIN (ANALYZE, BUFFERS)
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
e.ename,
d.dname,
e.sal
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.sal >= 2000
ORDER BY e.sal DESC;
```
Look for **`Buffers: shared hit=4`** — when shared hits equal total blocks, your data is served 100% from PostgreSQL RAM cache without touching slow NVMe storage.
