Postgres has 7 index types. Here is which one to use and when

-- Best for: =, <, >, BETWEEN, IN, ORDER BY, equality and range queries
-- Default when you write CREATE INDEX without specifying type

CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_users_email ON users (email);

-- Covering index: include extra columns to avoid heap fetch
CREATE INDEX idx_orders_covering ON orders (user_id, created_at DESC)
INCLUDE (status, amount_cents);  -- These columns included but not searchable

-- Composite B-tree: column order matters
-- This index helps: WHERE user_id = ? AND created_at > ?
-- This index does NOT help efficiently: WHERE created_at > ? (without user_id)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Best for: WHERE id = ? (equality only, no ranges)
-- Slightly faster than B-tree for equality, but cannot do ranges or ORDER BY
-- Only use if you exclusively do equality lookups

CREATE INDEX idx_sessions_token ON sessions USING hash (token);
-- Use when: SELECT * FROM sessions WHERE token = $1 (never range queries)

-- Best for: @>, ?  (containment/existence), @@ (full-text), array operations

-- For JSONB containment
CREATE INDEX idx_users_prefs ON users USING gin(preferences);
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';  -- Uses GIN

-- For array containment
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];  -- Uses GIN

-- For full-text search
CREATE INDEX idx_articles_search ON articles USING gin(search_vector);
SELECT * FROM articles WHERE search_vector @@ 'postgres & performance';  -- Uses GIN

-- GIN with pg_trgm for LIKE/ILIKE (install pg_trgm first)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%headphone%';  -- Now uses GIN!

-- Best for: geometric queries, range overlap, nearest-neighbor (PostGIS)

-- For date ranges
CREATE TABLE bookings (
    id UUID PRIMARY KEY,
    room_id UUID,
    period tstzrange NOT NULL
);
CREATE INDEX idx_bookings_period ON bookings USING gist(period);

-- Find overlapping bookings
SELECT * FROM bookings
WHERE period && tstzrange('2026-04-01', '2026-04-07');  -- Uses GiST

-- PostGIS spatial index
CREATE INDEX idx_stores_location ON stores USING gist(location);
-- Find stores within 5km
SELECT * FROM stores
WHERE ST_DWithin(location, ST_GeomFromText('POINT(72.8 19.0)'), 5000);

-- Best for: very large tables (100M+ rows) with naturally sequential data
-- (timestamps, auto-increment IDs, dates)
-- Tiny index size but only useful when physical table order matches query

-- For a large time-series table where rows are inserted in time order
CREATE INDEX idx_events_created_brin ON events USING brin(created_at);

-- This is 100x smaller than a B-tree but works because rows are
-- physically stored in time order (append-only)
-- Useful for: SELECT * FROM events WHERE created_at BETWEEN ? AND ?

-- Best for: when your queries always include a specific condition
-- Result: smaller index, faster updates, better query performance

-- You always query pending orders specifically
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- You only need to look up active users
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

-- Only index high-value transactions
CREATE INDEX idx_txns_high_value ON transactions (user_id)
WHERE amount_cents > 100000;

-- These are significantly smaller and faster than full indexes