Indexing Strategy AI Prompt

Design an indexing strategy for this table and query workload.

Table: {{table_name}} with {{row_count}} rows
Query patterns: {{query_patterns}} (filter columns, join columns, sort columns, aggregations)
Database: {{database}}
Write vs read ratio: {{write_read_ratio}}

1. Index types:

   B-tree (default):
   - Best for: equality (=), range (<, >, BETWEEN), ORDER BY, most queries
   - Used for: 95% of indexes; the safe default

   Hash:
   - Only equality lookups (=); faster than B-tree for pure equality at high cardinality
   - PostgreSQL: hash indexes are now WAL-logged (safe); MySQL: InnoDB does not support hash

   GIN (Generalized Inverted Index):
   - For: full-text search, JSONB containment (@>), array operators (@>, &&)
   - Slower to build and update; fast for containment queries

   GiST:
   - For: geometric data, range types (tsrange, daterange), PostGIS

   Partial index:
   - Index only a subset of rows matching a WHERE condition
   - CREATE INDEX ON orders (customer_id) WHERE status = 'active';
   - Much smaller and faster than a full index when only a small fraction of rows match

   Covering index (INCLUDE clause):
   - Include additional columns in the index leaf nodes
   - Allows index-only scans (no heap access needed)
   - CREATE INDEX ON orders (customer_id) INCLUDE (order_amount, created_at);

2. Composite index column order:
   - Put the most selective column first
   - Put range conditions last
   - An index on (a, b, c) supports queries filtering on a, a+b, or a+b+c; not on b or c alone

3. Index bloat and maintenance:
   - REINDEX or VACUUM on PostgreSQL to reclaim dead index space
   - Monitor index size and usage: pg_stat_user_indexes (use_count = 0 → unused index)
   - Unused indexes hurt write performance with no read benefit — drop them

4. Write performance trade-off:
   - Each index slows INSERT, UPDATE, DELETE
   - High write ratio: minimize indexes to only the most critical
   - Read-heavy OLAP tables: more indexes acceptable

Return: index recommendations per query pattern, DDL for each index, covering index opportunities, and maintenance schedule.