# How to Generate Advanced Database Indexes with Cursor

- Tool: Cursor
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Cursor Free+, PostgreSQL projects
- Last updated: March 2026

## TL;DR

Cursor can generate basic CREATE INDEX statements but often misses advanced PostgreSQL features like partial indexes, expression indexes, and covering indexes. By providing schema context via @file, using a .cursor/rules/ entry with index best practices, and prompting with specific performance requirements, you can get Cursor to produce optimized index definitions that match your query patterns.

## Getting advanced PostgreSQL indexes from Cursor

PostgreSQL supports partial indexes, expression indexes, GIN indexes for JSONB, and covering indexes that can dramatically improve query performance. Cursor often defaults to simple B-tree indexes on single columns. This tutorial teaches you how to provide enough context and rules so Cursor generates indexes tailored to your actual query patterns and data distribution.

## Before you start

- Cursor installed with a PostgreSQL project open
- Basic understanding of SQL indexes and EXPLAIN output
- Access to your database schema or migration files
- Familiarity with Cursor Chat (Cmd+L)

## Step-by-step guide

### 1. Create a database indexing rule for Cursor

Add a project rule with PostgreSQL indexing best practices. This tells Cursor to consider partial indexes, expression indexes, and covering indexes instead of defaulting to simple single-column B-tree indexes on every column.

```
---
description: PostgreSQL indexing best practices for generated migrations
globs: "*.sql,*.migration.ts,**/migrations/**"
alwaysApply: false
---

# PostgreSQL Index Rules
- Consider partial indexes (WHERE clause) when queries filter on a common condition
- Use expression indexes for computed lookups like LOWER(email)
- Use GIN indexes for JSONB columns and full-text search
- Use covering indexes (INCLUDE) to enable index-only scans
- Always add CONCURRENTLY to CREATE INDEX in production migrations
- Name indexes descriptively: idx_{table}_{columns}_{type}
- Add comments explaining which query pattern each index supports
- Never create indexes on low-cardinality boolean columns without a partial WHERE clause
```

**Expected result:** Cursor considers advanced index types when generating database migration code.

### 2. Provide your schema as context for index generation

Cursor needs to see your table definitions to generate useful indexes. Open Chat with Cmd+L and reference your schema file directly. Include information about your most common query patterns so Cursor can match indexes to actual usage.

```
@src/db/schema.sql @postgres-indexes.mdc

Here are our most common query patterns for the orders table:
1. Find active orders for a specific user: WHERE user_id = $1 AND status = 'active'
2. Search orders by date range: WHERE created_at BETWEEN $1 AND $2
3. Find orders by lowercase email: WHERE LOWER(customer_email) = $1
4. Full-text search on order notes: WHERE notes @@ to_tsquery($1)
5. Dashboard count of pending orders: WHERE status = 'pending'

Generate optimal PostgreSQL indexes for each query pattern.
Use partial indexes where most rows would be excluded.
Include CONCURRENTLY and descriptive names.
```

> Pro tip: Paste EXPLAIN ANALYZE output from slow queries directly into the Chat. Cursor reads query plans and can suggest indexes specifically targeting sequential scans.

**Expected result:** Cursor generates five targeted indexes including partial indexes with WHERE clauses, an expression index on LOWER(email), and a GIN index for full-text search.

### 3. Generate a migration file with the indexes

Ask Cursor to wrap the generated indexes in a proper migration file. Reference your migration tool so Cursor generates the correct format. This step produces a ready-to-run migration rather than loose SQL statements.

```
@postgres-indexes.mdc

Create a database migration file that adds these indexes to the orders table:
1. Partial index on (user_id) WHERE status = 'active'
2. Partial index on (created_at) WHERE status != 'archived'
3. Expression index on LOWER(customer_email)
4. GIN index on notes using to_tsvector('english', notes)
5. Partial index on (status) WHERE status = 'pending' for dashboard counts

Use CREATE INDEX CONCURRENTLY for all indexes.
Format as a Knex.js migration with up and down functions.
```

**Expected result:** Cursor generates a complete Knex migration file with five CONCURRENTLY-created indexes and proper rollback in the down function.

### 4. Use Cursor to analyze query plans and suggest improvements

Copy the output of EXPLAIN ANALYZE from a slow query and paste it into Cursor Chat. Cursor can read query plans and identify missing indexes, inefficient sequential scans, and opportunities for covering indexes that enable index-only scans.

```
@postgres-indexes.mdc

Here is the EXPLAIN ANALYZE output for a slow query:

Seq Scan on orders (cost=0.00..15420.00 rows=52 width=204)
  Filter: ((status = 'active') AND (user_id = 'abc-123'))
  Rows Removed by Filter: 499948
Planning Time: 0.15 ms
Execution Time: 142.8 ms

The table has 500,000 rows. Only 2% have status = 'active'.
Suggest the optimal index to eliminate the sequential scan.
Explain why a partial index is better than a regular index here.
```

**Expected result:** Cursor recommends a partial index on (user_id) WHERE status = 'active' and explains that it covers only 2% of rows, making it smaller and faster than a full index.

### 5. Verify indexes with an EXPLAIN check prompt

After applying your migration, use Cursor to generate verification queries. This step ensures the indexes are actually being used by your query patterns and not being ignored by the PostgreSQL query planner.

```
@postgres-indexes.mdc

Generate EXPLAIN ANALYZE statements to verify these indexes are used:
1. SELECT * FROM orders WHERE user_id = $1 AND status = 'active'
2. SELECT * FROM orders WHERE LOWER(customer_email) = $1
3. SELECT * FROM orders WHERE notes @@ to_tsquery('english', $1)
4. SELECT COUNT(*) FROM orders WHERE status = 'pending'

For each, show what the expected plan should look like
(Index Scan or Index Only Scan, not Seq Scan).
Include the SET enable_seqscan = off trick for testing.
```

**Expected result:** Cursor generates EXPLAIN ANALYZE queries for each pattern and describes the expected Index Scan behavior.

## Complete code example

File: `migrations/20260325_add_order_indexes.ts`

```typescript
import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  // Partial index: active orders by user (covers 2% of rows)
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id_active
    ON orders (user_id)
    WHERE status = 'active'
  `);

  // Partial index: date range queries excluding archived
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at_not_archived
    ON orders (created_at)
    WHERE status != 'archived'
  `);

  // Expression index: case-insensitive email lookup
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_email_lower
    ON orders (LOWER(customer_email))
  `);

  // GIN index: full-text search on notes
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_notes_fts
    ON orders USING GIN (to_tsvector('english', notes))
  `);

  // Partial index: pending status for dashboard counts
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_pending
    ON orders (status)
    WHERE status = 'pending'
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id_active');
  await knex.raw('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_created_at_not_archived');
  await knex.raw('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_email_lower');
  await knex.raw('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_notes_fts');
  await knex.raw('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status_pending');
}
```

## Common mistakes

- **Asking Cursor for indexes without providing the table schema** — Without knowing column types, data distribution, and existing indexes, Cursor generates generic single-column B-tree indexes that may not match your query patterns. Fix: Always reference your schema file with @file and describe your most common query patterns in the prompt.
- **Creating indexes without CONCURRENTLY on production tables** — Standard CREATE INDEX locks the table for writes. On large tables this can cause minutes of downtime. Fix: Include CONCURRENTLY in your indexing rules. Note that CONCURRENTLY cannot run inside a transaction, so use knex.raw instead of the schema builder.
- **Creating full indexes on boolean or low-cardinality columns** — A full B-tree index on a boolean column wastes space and may not be used by the query planner. A partial index on the minority value is far more efficient. Fix: Specify in your rules to use partial indexes for columns where the filtered condition matches a small percentage of total rows.

## Best practices

- Always provide EXPLAIN ANALYZE output when asking Cursor to optimize queries
- Include data distribution estimates in your prompts so Cursor can judge index selectivity
- Use descriptive index names with the pattern idx_{table}_{columns}_{type}
- Add SQL comments above each index explaining which query it supports
- Test index usage with EXPLAIN ANALYZE after creation to verify the planner uses them
- Keep indexing rules separate from general SQL rules for cleaner rule management
- Review Cursor-generated indexes against your actual slow query log before deploying

## Frequently asked questions

### Can Cursor read my actual database to suggest indexes?

Not directly, but you can use a Database MCP server to connect Cursor to your database. Alternatively, paste EXPLAIN ANALYZE output and table statistics into Chat for Cursor to analyze.

### Should I create indexes on every column Cursor suggests?

No. Each index adds write overhead. Only create indexes for columns that appear in frequent WHERE, JOIN, or ORDER BY clauses. Use pg_stat_user_indexes to check if existing indexes are actually being used.

### How do I know if a partial index is worth it?

A partial index is beneficial when the WHERE condition matches a small fraction of total rows, typically under 20%. The smaller the fraction, the bigger the performance gain compared to a full index.

### Does Cursor know about PostgreSQL 16 features?

Cursor's knowledge depends on the underlying model. Reference @docs with the PostgreSQL 16 documentation URL to ensure Cursor generates syntax compatible with the latest features.

### Can I use this approach with MySQL or SQLite?

The principles apply, but the syntax differs. MySQL supports partial indexes only through generated columns, and SQLite has limited partial index support. Adjust your rules file for your specific database.

### Can RapidDev help optimize our database indexes?

Yes. RapidDev provides database performance audits that analyze slow query logs, identify missing indexes, and configure Cursor rules for ongoing index optimization as your schema evolves.

---

Source: https://www.rapidevelopers.com/cursor-tutorial/how-to-instruct-cursor-ai-to-create-partial-index-definitions-for-postgresql
© RapidDev — https://www.rapidevelopers.com/cursor-tutorial/how-to-instruct-cursor-ai-to-create-partial-index-definitions-for-postgresql