PostgreSQL — Indexes

Basic Index

CREATE INDEX idx_users_email ON users(email);

Unique Index

CREATE UNIQUE INDEX idx_users_email ON users(email);

Composite Index

CREATE INDEX idx_users_name_email ON users(name, email);

Partial Index

CREATE INDEX idx_active_users ON users(email) WHERE active = true;

List Indexes

SELECT * FROM pg_indexes WHERE tablename = 'users';

Drop Index

DROP INDEX idx_users_email;

Index Types

Type	Description
B-tree	Default, most common
Hash	Equality lookups
GiST	Geometric/text
GIN	Arrays/jsonb
BRIN	Large tables

When to Index

Column Type	Recommendation
Primary key	Always
Foreign key	Usually
WHERE clause	Yes
ORDER BY	Yes
Low cardinality	No

Mini Practice

1. Create basic index
2. Create unique index
3. Create composite index
4. Check index usage

Up Next

Continue with Constraints — table constraints.