HIGHPerformancesmallSQL & Databases

Add Indexes for Frequent Queries

Add database indexes for columns used in WHERE, JOIN, and ORDER BY clauses. Without indexes, the database scans every row in the table for every query — a table with 1M rows takes seconds instead of milliseconds.

Why This Matters

Without indexes, the database performs a sequential scan — reading every row in the table to find matches. On a table with 1 million rows, a query that should take 1ms takes 500ms+. As the table grows, performance degrades linearly. Every unindexed WHERE clause, JOIN condition, and ORDER BY is a ticking time bomb that will cause performance issues as data grows.

Related Rules

Avoid SELECT * in Production

Performance

MEDIUM

Use Parameterized Queries

Security

CRITICAL

Normalize Data Appropriately

Architecture

MEDIUM

Add Foreign Key Constraints

Quality

HIGH

Catch this automatically on every PR

BeforeMerge scans your pull requests against this rule and 4+ others. Get actionable feedback before code ships.

Join Waitlist Browse All Rules

Why this matters

A database index is like a book's index — instead of reading every page to find a topic, you look it up in the index and jump directly to the right page. Without an index, the database must perform a sequential scan, reading every row to find matches.

The impact scales with data:

Rows	Without index	With index
1,000	1ms	<1ms
100,000	50ms	<1ms
1,000,000	500ms	1ms
10,000,000	5,000ms	1ms

A query that works fine in development with 100 rows becomes unusable in production with 1 million rows. This is the #1 cause of "it was fast, then it got slow" database performance issues.

The rule

Add indexes for:

Every column used in WHERE clauses
Every column used in JOIN conditions
Every column used in ORDER BY clauses
Composite indexes for queries that filter on multiple columns
Unique indexes for columns with uniqueness constraints

Bad example

-- Table with no indexes beyond the primary key
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_amount numeric NOT NULL
);
 
-- This query scans every row in the orders table
SELECT * FROM orders
WHERE customer_id = 'abc-123'
  AND status = 'pending'
ORDER BY created_at DESC;

Good example

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_amount numeric NOT NULL
);
 
-- Index for the most common query pattern
CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);
 
-- The same query now uses the index — milliseconds instead of seconds
SELECT * FROM orders
WHERE customer_id = 'abc-123'
  AND status = 'pending'
ORDER BY created_at DESC;

How to detect

Use EXPLAIN ANALYZE to check if queries use indexes:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'abc-123';

If the output shows Seq Scan instead of Index Scan, the query is not using an index.

Also check for missing indexes on foreign keys:

SELECT
  tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes
    WHERE tablename = tc.table_name
      AND indexdef LIKE '%' || kcu.column_name || '%'
  );

Remediation

Identify slow queries with pg_stat_statements or application logging
Run EXPLAIN ANALYZE on each slow query
Add indexes for columns that appear in Seq Scans
Use composite indexes for multi-column WHERE clauses (put equality columns first, range/sort columns last)
Monitor index usage with pg_stat_user_indexes — drop unused indexes to save write performance