Kenhuang Academy
  • English

      • PostgreSQL Indexes

      Overview

      Indexes improve query performance by allowing faster data retrieval. They work like a book index.

      Create Index

      -- Simple index
CREATE INDEX idx_users_email ON users(email);

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

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

      Index Types

      B-tree Index

      -- Default index type
CREATE INDEX idx_users_name ON users USING btree (name);

      Hash Index

      -- For equality comparisons only
CREATE INDEX idx_users_email ON users USING hash (email);

      GIN Index

      -- For arrays, full-text search, JSONB
CREATE INDEX idx_products_tags ON products USING gin (tags);
CREATE INDEX idx_content ON articles USING gin (to_tsvector('english', content));

      GiST Index

      -- For geometric data, full-text search
CREATE INDEX idx_locations ON locations USING gist (coordinates);

      Partial Index

      -- Index only active users
CREATE INDEX idx_active_users_email ON users(email) WHERE status = 'active';

      Expression Index

      -- Index on expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Use expression index
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

      Managing Indexes

      -- Rename index
ALTER INDEX idx_users_name RENAME TO idx_users_email_name;

-- Drop index
DROP INDEX IF EXISTS idx_users_name;

-- Reindex
REINDEX TABLE users;

      Summary

      Index key points:

      • B-tree (default), Hash, GIN, GiST
      • Partial and expression indexes
      • Improve query performance
      • Add overhead on writes