You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Indexes are the single most impactful tool for query performance. This lesson covers B-tree, GIN, GiST, and hash indexes, plus partial indexes, expression indexes, covering indexes, and how to use EXPLAIN ANALYZE to verify your choices.
Without an index, PostgreSQL must perform a sequential scan — reading every row in the table. With millions of rows, this is unacceptably slow.
-- Without an index: sequential scan O(n)
SELECT * FROM orders WHERE customer_id = 12345;
-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=120)
-- Filter: (customer_id = 12345)
-- With an index: index scan O(log n)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- Index Scan using idx_orders_customer_id (cost=0.42..8.44 rows=50 width=120)
B-tree is the default index type. It supports equality (=) and range (<, >, BETWEEN, <=, >=) queries.
-- Single-column B-tree
CREATE INDEX idx_orders_date ON orders (order_date);
-- Multi-column B-tree (column order matters!)
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.