DEV Community

lukman lukman
lukman lukman

Posted on

Database Index: Why Queries Become Slow as Data Grows

#postgres #database #backend #sql

This query looks ordinary:

SELECT *
FROM service
WHERE branch_id = 2
  AND status = 'FINISHED'
  AND service_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY service_date DESC;
Enter fullscreen mode Exit fullscreen mode

With a small dataset, a query like this may appear completely fine.

Lab 02 does not rely on assumptions. The query is tested using EXPLAIN (ANALYZE, BUFFERS) against the same dataset.

Dataset

Table: service

Total rows:

500,000
Enter fullscreen mode Exit fullscreen mode

Status distribution:

Status Fraction Rows
FINISHED 70.00% 350,000
CANCELLED 20.00% 100,000
IN_PROGRESS 5.00% 25,000
WAITING 4.90% 24,500
PENDING_REFUND 0.10% 500

Branch 2 is the busiest branch, containing 25.00% of the data.

Baseline

The baseline only has constraint-backed indexes:

service_pkey
service_invoice_no_key
Enter fullscreen mode Exit fullscreen mode

There is no secondary index on:

branch_id
status
service_date
Enter fullscreen mode Exit fullscreen mode

Baseline query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM service
WHERE branch_id = 2
  AND status = 'FINISHED'
  AND service_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY service_date DESC;
Enter fullscreen mode Exit fullscreen mode

What should be inspected:

  • plan node;
  • actual rows;
  • rows removed by filter;
  • explicit Sort;
  • shared read / shared hit;
  • planning time;
  • execution time.

Add the Candidate Index 

CREATE INDEX idx_service_branch_status_date
ON service(branch_id, status, service_date DESC);
Enter fullscreen mode Exit fullscreen mode

This index follows the query shape:

branch_id equality
→ status equality
→ service_date range/order
Enter fullscreen mode Exit fullscreen mode

It is a candidate for this specific query, not a universal index.

Run the Same Query Again 

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM service
WHERE branch_id = 2
  AND status = 'FINISHED'
  AND service_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY service_date DESC;
Enter fullscreen mode Exit fullscreen mode

Compare:

Condition What to inspect
Before index Seq Scan, Sort, buffers, rows, time
After index Index usage, Index Cond, Sort presence, buffers, rows, time

The lab does not store fixed execution-time numbers. Those values should come from the local run.

Cardinality Is Not Match Fraction

status has 5 distinct values. That is its cardinality.

Match fraction is the proportion of rows that match a predicate.

Predicate Match fraction Expected / likely plan
status = 'FINISHED' 70.0% Seq Scan likely cheaper
status = 'PENDING_REFUND' 0.1% index-based plan likely cheaper

The lesson is that low cardinality does not automatically make an index useless.

Column Order Matters

The lab compares:

CREATE INDEX idx_service_a_branch_status_date
    ON service(branch_id, status, service_date);

CREATE INDEX idx_service_b_date_branch_status
    ON service(service_date, branch_id, status);

CREATE INDEX idx_service_c_status_date_branch
    ON service(status, service_date, branch_id);
Enter fullscreen mode Exit fullscreen mode

For the main query, the first index becomes a strong candidate because the leading equality predicates can narrow the scan range more effectively.

ORDER BY + LIMIT

Dashboard query:

SELECT *
FROM service
WHERE branch_id = 2
  AND status = 'FINISHED'
ORDER BY service_date DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Index:

CREATE INDEX idx_service_branch_status_date_desc
    ON service(branch_id, status, service_date DESC);
Enter fullscreen mode Exit fullscreen mode

What to inspect:

  • whether Sort is absent;
  • whether the plan uses Index Scan or Index Only Scan;
  • how many rows are examined before LIMIT is satisfied.

Index Is Not Free

The lab also tests:

  • inserting 1,000 rows without secondary indexes;
  • inserting 1,000 rows with 1 composite index;
  • inserting 1,000 rows with 4 secondary indexes;
  • updating indexed vs non-indexed columns;
  • storage cost using pg_relation_size, pg_indexes_size, and pg_total_relation_size.

An index can improve specific reads, but it also adds write and storage cost.

Key Takeaways

  1. Do not optimize based on guesses.
  2. Use EXPLAIN (ANALYZE, BUFFERS).
  3. A composite index should follow the query shape.
  4. Column order matters.
  5. Cardinality is different from match fraction.
  6. A Seq Scan can be the correct plan.
  7. Indexes have write and storage costs.

Hands-on Lab

Software Engineering Lab #02 — Database Index

Author: Lukman

GitHub: lukman-ss

Repository:
https://github.com/lukman-ss/software-engineering-lab/tree/main/labs/02-database-index

Top comments (0)