PostgreSQL Indexes

CREATE INDEX idx_email ON users(email); # default B-tree index
CREATE INDEX idx_name ON users USING BTREE(name); # explicit B-tree

CREATE UNIQUE INDEX idx_username ON users(username); # unique constraint

CREATE INDEX idx_name_age ON users(last_name, first_name); # multi-column index

CREATE INDEX idx_active_users ON users(email)
WHERE active = true; # index subset of rows

CREATE INDEX idx_lower_email ON users(LOWER(email)); # index on expression

CREATE INDEX idx_tags ON articles USING GIN(tags); # for arrays, JSONB

CREATE INDEX idx_location ON places USING GIST(location); # for geometric data

\di # list indexes
DROP INDEX idx_email; # drop index
REINDEX INDEX idx_email; # rebuild index
REINDEX TABLE users; # rebuild all table indexes