CREATE INDEX
An index is an extra sorted structure that lets PostgreSQL find rows without scanning the whole table.
In this page:
Syntax
CREATE INDEX index_name ON table_name (column);
CREATE UNIQUE INDEX index_name ON table_name (column1, column2);CREATE INDEX
CREATE INDEX name ON table (column) builds a B-tree index by default. UNIQUE indexes enforce uniqueness, multicolumn indexes cover several columns, and CREATE INDEX CONCURRENTLY builds without blocking writes.
Indexes speed reads but add cost to writes and use disk space. Primary keys and unique constraints create indexes automatically.
Note:
Use CREATE INDEX CONCURRENTLY on busy production tables.
Example: CREATE INDEX
CREATE TABLE emails (id INTEGER PRIMARY KEY, address TEXT, domain TEXT);
INSERT INTO emails VALUES (1,'[email protected]','x.com'),(2,'[email protected]','y.org'),(3,'[email protected]','x.com');
CREATE INDEX idx_emails_domain ON emails (domain);
CREATE UNIQUE INDEX idx_emails_address ON emails (address);
-- Queries look the same; the planner decides whether the index helps
SELECT address FROM emails WHERE domain = 'x.com' ORDER BY id;
SELECT COUNT(*) AS total FROM emails;
-- Output:
-- address
-- [email protected]
-- [email protected]
-- total
-- 3Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Indexing every column
- Forgetting foreign key columns
- Never checking whether the index is used
Chapter Summary
- CREATE INDEX name ON table (column)
- UNIQUE enforces uniqueness
- CONCURRENTLY avoids blocking writes
- Indexes cost writes and space
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: