CREATE INDEX
intermediateddl
2 min read
What is CREATE INDEX?
CREATE INDEX builds an index on one or more columns of a table. Indexes are data structures that allow the database to find rows much faster than scanning the entire table.
Syntax
CREATE INDEX index_name ON table_name (column_name);Unique index:
CREATE UNIQUE INDEX idx_email ON students (email);Multi-column index:
CREATE INDEX idx_name ON students (last_name, first_name);Partial index:
CREATE INDEX idx_active ON products (name) WHERE price > 0;When to Use
- Columns frequently used in WHERE clauses
- Columns used in JOIN conditions (especially foreign keys)
- Columns used in ORDER BY
- Columns with high selectivity (many unique values)
Key Points
- B-tree Default — The default index type handles =, <, >, <=, >=, BETWEEN.
- UNIQUE Index — Prevents duplicates and serves as a fast lookup.
- Multi-Column — Index on (a, b) supports queries filtering on a, or on both a and b, but not on b alone.
- Partial — Only indexes rows matching a WHERE condition. Smaller and faster.
- CONCURRENTLY — CREATE INDEX CONCURRENTLY does not lock the table for writes.
- Cost — Indexes slow down INSERT/UPDATE/DELETE. Only create indexes you actually need.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
CREATE INDEX Challenge
Write a query that solve this task: speed up joins on enrollments.student_id.
Expected result
An index on student_id. Queries joining or filtering on this column will be faster.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is CREATE INDEX?
Initializing database...Each run starts from fresh sample data.
More Examples
Unique index on email
Ensure emails are unique and speed up lookups.
Initializing database...Each run starts from fresh sample data.
Partial index for active products
Index only products with a positive price.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Does PostgreSQL automatically index foreign keys?
No. Unlike primary keys, foreign key columns are not automatically indexed. You should create indexes on FK columns manually.
How do I drop an index?
Use DROP INDEX index_name; or DROP INDEX IF EXISTS index_name;