Why should you care?

PostgresVerse order lookup by customer_id without an index scans millions of rows; B-Tree makes WHERE customer_id = ? milliseconds.

See it live — copy this example

Run in pgAdmin or psql.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

SELECT order_id FROM orders
WHERE customer_id = 501
ORDER BY created_at DESC
LIMIT 5;

What happened?

• Composite index leads with customer_id then created_at descending.
• The query filters one customer and walks the index in sort order — often no extra sort step.

Practice next

1. Run EXPLAIN on the SELECT before creating the index.
2. CREATE INDEX and run EXPLAIN again.
3. Note Index Scan vs Seq Scan in the plan.
4. Create UNIQUE INDEX on customers(email).
5. Use CONCURRENTLY in production to avoid long write locks.

Remember

B-Tree suits =, <, >, BETWEEN, ORDER BY on scalars. Composite index column order matters. Indexes speed reads and slow writes slightly.

PostgresVerse order history index

Support portal loads last five orders per customer instantly after composite B-Tree deploy.

Outcome: Seq Scan disappears from pg_stat_statements top offenders.