Index Types

Code

-- B-tree (default): equality and range
CREATE INDEX idx_orders_cust ON orders(customer_id, created_at);

-- Partial index: smaller, targeted
CREATE INDEX idx_orders_pending ON orders(customer_id)
WHERE status = 'pending';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- GIN index for arrays and full-text
CREATE INDEX idx_tags ON posts USING gin(tags);

-- GiST for geometric or range types
CREATE INDEX idx_locations ON places USING gist(location);

-- Covering index (INCLUDE)
CREATE INDEX idx_orders_covering ON orders(customer_id)
INCLUDE (total, status);

-- Concurrent creation (no write lock)
CREATE INDEX CONCURRENTLY idx_orders_total ON orders(total);

Explanation

PostgreSQL offers multiple index types: B-tree for default lookups, GIN for arrays and full-text, and GiST for geometric and range data. Partial indexes only index matching rows, saving space when queries filter on a common predicate. CREATE INDEX CONCURRENTLY builds the index without blocking writes.

More PostgreSQL Snippets