Index Types in PostgreSQL

-- B-tree index (default)
CREATE INDEX idx_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Multicolumn index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Partial index (filtered)
CREATE INDEX idx_active ON orders(user_id)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- DETAILED_TAB:
-- GIN index for full-text search
CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', content));

-- GiST index for geometric data
CREATE INDEX idx_location ON stores USING gist(location);

-- BRIN index for large sorted tables
CREATE INDEX idx_created ON logs USING brin(created_at);

-- Hash index for equality checks only
CREATE INDEX idx_status ON orders USING hash(status);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);