How to Generate Advanced Database Indexes with Cursor

1---
2description: PostgreSQL indexing best practices for generated migrations
3globs: "*.sql,*.migration.ts,**/migrations/**"
4alwaysApply: false
5---
6
7# PostgreSQL Index Rules
8- Consider partial indexes (WHERE clause) when queries filter on a common condition
9- Use expression indexes for computed lookups like LOWER(email)
10- Use GIN indexes for JSONB columns and full-text search
11- Use covering indexes (INCLUDE) to enable index-only scans
12- Always add CONCURRENTLY to CREATE INDEX in production migrations
13- Name indexes descriptively: idx_{table}_{columns}_{type}
14- Add comments explaining which query pattern each index supports
15- Never create indexes on low-cardinality boolean columns without a partial WHERE clause

1@src/db/schema.sql @postgres-indexes.mdc
2
3Here are our most common query patterns for the orders table:
41. Find active orders for a specific user: WHERE user_id = $1 AND status = 'active'
52. Search orders by date range: WHERE created_at BETWEEN $1 AND $2
63. Find orders by lowercase email: WHERE LOWER(customer_email) = $1
74. Full-text search on order notes: WHERE notes @@ to_tsquery($1)
85. Dashboard count of pending orders: WHERE status = 'pending'
9
10Generate optimal PostgreSQL indexes for each query pattern.
11Use partial indexes where most rows would be excluded.
12Include CONCURRENTLY and descriptive names.

1@postgres-indexes.mdc
2
3Create a database migration file that adds these indexes to the orders table:
41. Partial index on (user_id) WHERE status = 'active'
52. Partial index on (created_at) WHERE status != 'archived'
63. Expression index on LOWER(customer_email)
74. GIN index on notes using to_tsvector('english', notes)
85. Partial index on (status) WHERE status = 'pending' for dashboard counts
9
10Use CREATE INDEX CONCURRENTLY for all indexes.
11Format as a Knex.js migration with up and down functions.

1@postgres-indexes.mdc
2
3Here is the EXPLAIN ANALYZE output for a slow query:
4
5Seq Scan on orders (cost=0.00..15420.00 rows=52 width=204)
6  Filter: ((status = 'active') AND (user_id = 'abc-123'))
7  Rows Removed by Filter: 499948
8Planning Time: 0.15 ms
9Execution Time: 142.8 ms
10
11The table has 500,000 rows. Only 2% have status = 'active'.
12Suggest the optimal index to eliminate the sequential scan.
13Explain why a partial index is better than a regular index here.

1@postgres-indexes.mdc
2
3Generate EXPLAIN ANALYZE statements to verify these indexes are used:
41. SELECT * FROM orders WHERE user_id = $1 AND status = 'active'
52. SELECT * FROM orders WHERE LOWER(customer_email) = $1
63. SELECT * FROM orders WHERE notes @@ to_tsquery('english', $1)
74. SELECT COUNT(*) FROM orders WHERE status = 'pending'
8
9For each, show what the expected plan should look like
10(Index Scan or Index Only Scan, not Seq Scan).
11Include the SET enable_seqscan = off trick for testing.