Indexes

Code

-- Create index
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON employees(dept, salary);

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

-- Partial index
CREATE INDEX idx_active_users ON users(last_login)
WHERE active = true;

-- View indexes
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'users';

-- Drop index
DROP INDEX IF EXISTS idx_user_email;

-- Explain query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Explanation

Indexes speed up queries but add write overhead; composite indexes follow the leftmost prefix rule.

More SQL Snippets