Index Fundamentals and B-tree
An index is a separate data structure that helps PostgreSQL locate rows without scanning every row in a table. B-tree is the default and supports common equality and range comparisons, as well as ordered access. Indexes improve reads but consume storage and add work to inserts, updates, and deletes.
Create indexes based on actual query patterns, especially frequently filtered, joined, or sorted columns. A foreign key declaration does not automatically create an index on the referencing column, so evaluate whether one is needed. Avoid indexing every column by default; too many indexes can hurt write performance and increase maintenance cost.
Create an index
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
SELECT id, total
FROM orders
WHERE customer_id = 101;💡 The index may help find orders for a customer. The planner decides whether to use it based on table size, selectivity, statistics, and estimated cost; an index is not guaranteed to be chosen for every query.
Key Takeaways
- Indexes trade storage and write cost for faster access paths.
- B-tree is a versatile default index type.
- Create indexes based on workload evidence, not every column.
Knowledge Check
Which is a common cost of adding an index?