Django Index Design

Use this skill to add or change indexes only after profiling shows a specific query can benefit. Indexes speed selected reads and constraints, but they add write cost, migration risk, and maintenance overhead.

Workflow

1. Start from a query plan.

   • Capture the SQL, filters, joins, ordering, selected columns, table sizes, and current indexes.
   • Confirm that the query is important enough to optimize.

2. Choose the index shape.

   • Equality filters first, then range/order fields when useful.
   • Match ORDER BY for LIMIT queries when possible.
   • Use partial indexes for highly queried subsets.
   • Use covering indexes only when selected columns are stable and index-only scans are plausible.
   • Use GIN for JSONB containment, arrays, and full-text patterns; do not expect a B-tree to solve those.