Partial and expression indexes

You know B-tree and composite indexes. PostgreSQL also offers partial indexes that only include matching rows and expression indexes that index computed values. Both make indexes smaller and faster.

partial_expression.sql
sql
-- Partial index: only index rows that match a condition
CREATE INDEX idx_orders_processing
  ON orders(order_purchase_timestamp)
  WHERE order_status = 'processing';
-- Smaller index = less disk, faster lookups

-- Expression index: index on a function result
CREATE INDEX idx_orders_month
  ON orders(DATE_TRUNC('month', order_purchase_timestamp));

Partial indexes only include matching rows (smaller and faster). Expression indexes let you index computed values.

No! Each index slows down writes (INSERT/UPDATE/DELETE) because the index must be updated too. Indexes also consume disk space. Only index columns you frequently filter (WHERE), join (ON), or sort (ORDER BY). Run EXPLAIN ANALYZE to check if your query actually uses the index.

Fill in the blanks: Create an index

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Create and verify indexes

Loading practice…

Checkpoint: Performance check

Loading practice…