Why a query can be slow even when an index exists

Backend Fundamentals · Databases · 10 min read

1. What problem does this solve?

Motivation
When you add an index to a table you expect queries that filter on that column to become fast. In practice, many production systems still see slow queries even after indexes are in place. Understanding why helps you:

• Diagnose performance regressions.
• Write queries that actually use the index.
• Avoid over‑indexing and the write‑side cost it brings.

Why it matters

• User experience – A 5 s database hit can turn a good product into a frustrating one.
• Cost – Cloud DB instances charge for I/O, CPU, and storage. Unnecessary scans waste money.
• Scalability – As data grows, a full table scan can become the bottleneck before indexes help.

Consequences of ignoring the concept

• Queries that hit the disk for every row (full scans) instead of a few index pages.
• Higher CPU and memory usage on the DB server.
• Slower application response times and higher error rates.

Where it appears

• E‑commerce product search (filtering by category, price, etc.).
• Analytics dashboards that aggregate large tables.
• Real‑time recommendation engines that join on user IDs.
• Any backend that relies on a relational DB for filtering or ordering.

2. Mental model

Analogy
Imagine a library with a perfect index (the card catalog). If you want a book on “Quantum Mechanics,” you look up the card, find the shelf, and grab the book. But if the card lists “Quantum Mechanics” under “Physics” and you look under “Quantum,” you’ll still have to scan the whole shelf. The index exists, but you’re not using it correctly.

Engineering model

• Index = a sorted lookup table that maps a column value to the row’s physical location.
• Query planner = the librarian that decides whether to use the card catalog or just flip through every book.
• Selectivity = how many rows match a predicate; low selectivity means the index is less useful.
• Cost estimation = the planner’s way of guessing how many disk reads a plan will need.

3. Deep explanation

Internals

Important terminology

• Selectivity – Fraction of rows that satisfy a predicate.
  selectivity = rows_matched / total_rows.
  Low selectivity (e.g., WHERE status = 'active' on a table where 90 % are active) makes an index less useful.
• Coverage – Whether the index contains all columns needed by the query.
  If yes, the planner can do an index‑only scan.
• Correlated subquery – A subquery that depends on the outer query; often forces a scan.
• Function on column – Using a function (e.g., LOWER(name)) in the predicate prevents the planner from using a plain index.

Trade‑offs

Failure modes

1. Missing or wrong index – Planner falls back to a sequential scan.
2. Stale statistics – Planner thinks the index is selective when it isn’t (or vice versa).
3. Function on column – WHERE LOWER(name) = 'john' ignores a plain name index.
4. Wrong column order in composite index – WHERE a = 1 AND b = 2 may not use CREATE INDEX ON t(b, a).
5. Low selectivity – Index adds overhead but yields little benefit.
6. Index bloat – Frequent updates cause fragmentation; scans become slower.
7. Missing covering columns – Planner must fetch rows from the table anyway.

Where this concept fits in a backend architecture

• Database layer – Indexes are a core part of query optimization.
• ORM / query builder – May generate suboptimal queries that ignore indexes.
• Caching layer – Sometimes a cache (Redis) can replace a slow DB query.
• Analytics pipeline – Materialized views or pre‑aggregated tables can avoid expensive scans.

Interaction with related concepts

• Sharding / partitioning – Indexes are per‑partition; planner must consider cross‑partition scans.
• Replication – Write‑side index maintenance happens on the primary; replicas may lag.
• Connection pooling – Long‑running queries that don’t use indexes can block pool slots.
• Monitoring – pg_stat_user_indexes shows index usage; pg_stat_user_tables shows sequential scans.

4. How it works

1. Client sends query to the DB.
2. Planner receives the query and gathers statistics.
3. Planner estimates cost for each possible plan (sequential scan, index scan, index‑only scan, etc.).
4. Planner chooses the cheapest plan based on cost estimates.
5. Executor runs the chosen plan:
   • If index scan: reads index pages → fetches rows.
   • If sequential scan: reads every page.
6. Result returned to client.

sequenceDiagram
    participant App
    participant DB
    participant Planner
    participant Executor
    participant Storage

    App->>DB: SELECT * FROM users WHERE email = 'alice@example.com';
    DB->>Planner: parse & analyze
    Planner->>Planner: gather stats
    Planner->>Planner: estimate costs
    Planner->>DB: choose plan (index scan)
    DB->>Executor: execute plan
    Executor->>Storage: read index page
    Storage->>Executor: return row pointer
    Executor->>Storage: read table row
    Storage->>Executor: return row
    Executor->>DB: return result
    DB->>App: result set

5. Practical implementation

5.1. Setup (PostgreSQL + Node.js)

# Create a sample database
createdb demo

# Install dependencies
npm init -y
npm install pg

5.2. Node.js script

// index.ts
import { Client } from 'pg';

const client = new Client({
  connectionString: 'postgres://postgres:postgres@localhost:5432/demo',
});

async function main() {
  await client.connect();

  // 1️⃣ Create table
  await client.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email TEXT NOT NULL,
      name TEXT,
      created_at TIMESTAMP DEFAULT now()
    );
  `);

  // 2️⃣ Populate with 1M rows
  console.log('Inserting rows...');
  await client.query(`
    INSERT INTO users (email, name)
    SELECT
      md5(random()::text) || '@example.com',
      'User ' || generate_series(1, 1000000)
    FROM generate_series(1, 1000000);
  `);

  // 3️⃣ Create index on email
  console.log('Creating index...');
  await client.query(`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);`);

  // 4️⃣ Run a slow query (no index used)
  console.log('Running slow query (no index)...');
  const slowRes = await client.query(`
    EXPLAIN ANALYZE
    SELECT * FROM users WHERE email = 'nonexistent@example.com';
  `);
  console.log(slowRes.rows.map(r => r['QUERY PLAN']).join('\n'));

  // 5️⃣ Run a fast query (index used)
  console.log('Running fast query (index used)...');
  const fastRes = await client.query(`
    EXPLAIN ANALYZE
    SELECT * FROM users WHERE email = 'alice@example.com';
  `);
  console.log(fastRes.rows.map(r => r['QUERY PLAN']).join('\n'));

  await client.end();
}

main().catch(console.error);

Important notes

• The EXPLAIN ANALYZE output shows whether the planner used the index.
• The first query searches for a non‑existent email; the planner may still use the index because it can quickly determine zero rows.
• The second query uses an existing email; the planner should choose an index scan.

5.3. Common pitfall: function on column

// Query that ignores the index because of LOWER()
await client.query(`
  EXPLAIN ANALYZE
  SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
`);

The planner cannot use the plain email index because the predicate applies a function.
Fix: create an expression index:

await client.query(`
  CREATE INDEX IF NOT EXISTS idx_users_email_lower ON users (LOWER(email));
`);

Now the planner can use the new index.

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

What changes at scale?

• Index strategy – Choose index types per workload: B‑tree for equality, GiST/Gin for ranges or full‑text, BRIN for very large tables with natural ordering.
• Partitioning – Combine indexes with table partitioning to keep index size manageable.
• Monitoring – Continuously track pg_stat_user_indexes and pg_stat_user_tables to spot regressions.
• Maintenance – Automate VACUUM, ANALYZE, and REINDEX in CI/CD pipelines.
• Cost vs. benefit – Weigh disk space and write latency against query latency; sometimes a small cache layer (Redis) is cheaper than a massive index.

Bottlenecks

• Disk I/O – Index scans still hit disk; SSDs mitigate but not eliminate.
• CPU – Sorting, hashing, and expression evaluation can dominate.
• Memory – Large indexes may not fit in RAM, causing page faults.

Failure scenarios

• Index corruption – Query fails or returns wrong results.
• Missing index after migration – Query times out.
• Index bloat – Reads become slower over time.

Monitoring

• pg_stat_user_indexes.idx_scan – How often an index is used.
• pg_stat_user_tables.seq_scan – How often full scans happen.
• pg_stat_all_tables.idx_tup_fetch – Rows fetched via index.
• Custom dashboards in Grafana/Prometheus.

Cost/performance considerations

• Storage – Indexes can double table size.
• Write latency – Each insert/update touches every relevant index.
• Read latency – Indexes reduce I/O but add CPU cost for index traversal.

When NOT to use an index

• Write‑heavy tables where the cost of maintaining the index outweighs read benefits.
• Very low cardinality columns (e.g., is_active BOOLEAN) where a full scan is cheaper.
• One‑time analytics – Use materialized views or external analytics engines instead.

9. Interview questions

10. Hands‑on task

Goal: Create a Node.js script that demonstrates a slow query due to a missing index, then refactor the query or index to make it fast.

Steps

1. Create a PostgreSQL table with 5 million rows (use generate_series).
2. Add an index on a column that is frequently queried (status).
3. Run a query that filters on that column and measure the time with EXPLAIN ANALYZE.
4. Introduce a function (LOWER(status)) in the predicate and observe the planner ignoring the index.
5. Create an expression index on LOWER(status) and rerun the query.
6. Compare execution times and explain the difference.

Deliverable: A single index.ts file that can be run with ts-node and prints the before/after execution plans and times.

11. Quick revision

• Indexes are lookup tables that can reduce I/O but add write overhead.
• The planner chooses a plan based on cost estimates derived from statistics.
• Low selectivity, functions on columns, wrong column order, and stale stats can cause indexes to be ignored.
• Index‑only scans require the index to cover all requested columns.
• Partial and expression indexes solve many “index not used” problems.
• Regular ANALYZE, VACUUM, and monitoring are essential in production.
• Over‑indexing hurts write performance and storage; under‑indexing hurts read performance.
• In distributed systems, index maintenance must be coordinated across replicas.
• Use EXPLAIN ANALYZE to debug slow queries; look for Seq Scan vs Index Scan.
• At scale, choose index types (B‑tree, GiST, GIN, BRIN) based on data patterns.
• Monitor pg_stat_user_indexes and pg_stat_user_tables for regressions.

12. References

• PostgreSQL Documentation – Indexes
• PostgreSQL Documentation – Query Planner and Optimizer
• PostgreSQL Documentation – EXPLAIN
• PostgreSQL Documentation – Statistics and ANALYZE
• PostgreSQL Documentation – VACUUM
• Node.js pg Library – Official Docs
• PostgreSQL Documentation – Partial Indexes
• PostgreSQL Documentation – Expression Indexes
• PostgreSQL Documentation – BRIN Indexes
• PostgreSQL Documentation – GIN Indexes