Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- B-tree (default) — general purpose
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Compound B-tree — supports prefix queries
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Hash — equality-only, slightly smaller than B-tree
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

-- Partial index — only index active products
CREATE INDEX idx_active_products_price
  ON products (price)
  WHERE is_active = TRUE;

-- Partial unique — only one active record per user
CREATE UNIQUE INDEX idx_one_active_session_per_user
  ON sessions (user_id)
  WHERE is_active = TRUE;

-- Covering index (INCLUDE) — include extra columns to avoid table fetch
CREATE INDEX idx_orders_covering
  ON orders (user_id, status)
  INCLUDE (total, created_at);

-- GIN index for full-text search
CREATE INDEX idx_products_fts
  ON products USING GIN (to_tsvector('english', name || ' ' || description));

-- List all indexes with size
SELECT indexname, indexdef, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';