PostgreSQL Indexes in Practice: When They Work and When They Don't

I work on a school management system: students, attendance, grades — the tables grow fast, and the attendance table reaches millions of rows within a few months. One day the attendance report page started taking 3-4 seconds to load. "We'll add an index, done," I said. I added one — nothing changed. That's when I truly understood: an index existing and an index working are two different things. In this article we'll look at real examples of when PostgreSQL indexes work and when they just sit there taking up space.

The examples use this simplified schema:

CREATE TABLE students (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    class_id  int  NOT NULL,
    full_name text NOT NULL,
    email     text,
    status    text NOT NULL DEFAULT 'active'  -- 'active' | 'archived'
);

CREATE TABLE attendance (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  bigint NOT NULL REFERENCES students (id),
    lesson_date date   NOT NULL,
    status      text   NOT NULL  -- 'present' | 'absent' | 'late'
);

How a B-tree works — a one-minute intuition

When you write CREATE INDEX, PostgreSQL builds a B-tree by default. Picture it as the alphabetical index at the back of a book: the values sit in the tree in sorted order, and each leaf holds a value plus the row's address in the table. A lookup descends from root to leaf in a few hops — even in a million-row table, reading 3-4 pages is enough.

One important conclusion follows from this: a B-tree is a sorted structure, so it is strong at =, <, >, BETWEEN, ORDER BY, and prefix searches. And it is powerless for any condition that doesn't rely on sort order. Every "doesn't work" case below stems from this single cause.

The index exists but doesn't work: four classic cases

1. Applying a function to the column. The most common mistake:

-- there is a plain index on the email column:
CREATE INDEX idx_students_email ON students (email);

-- this query bypasses the index entirely:
SELECT * FROM students WHERE lower(email) = 'aziz@example.com';

The index is sorted by email values, not by lower(email) — to PostgreSQL that is a completely different value. The fix is an expression index:

CREATE INDEX idx_students_email_lower ON students (lower(email));

Now the expression in the query matches the expression in the index exactly, and the Index Scan works.

2. LIKE with a leading `%`. full_name LIKE 'Aziz%' is a prefix search, and a sorted tree handles it well: everything starting with "A-z-i-z" sits next to each other in the tree. But LIKE '%aziz%' means "occurs anywhere" — such a search has no entry point into the tree, and PostgreSQL falls back to a full scan. If you need to find a student by a name fragment, the answer is the pg_trgm extension with a GIN index.

3. Low selectivity. Say we put an index on students.status, but 95 percent of students are active. For WHERE status = 'active', PostgreSQL does the math from its statistics: reading the table sequentially from start to finish is cheaper than visiting 95 percent of the rows one by one through the index. So it picks a Seq Scan. That's not a bug — it's the right call; a standalone index on such a column was never needed.

4. Implicit type cast. If your ORM or driver sends a parameter of the wrong type, the index silently switches off:

-- student_id is bigint and indexed. The parameter arrived as numeric:
EXPLAIN SELECT * FROM attendance WHERE student_id = 100234::numeric;
-- Filter: ((student_id)::numeric = '100234'::numeric)  →  Seq Scan

PostgreSQL casts the column to numeric — and we're back to the same "function on a column" case. The telltale sign: an unexpected :: next to a column in the EXPLAIN output. The fix is to match the parameter type to the column type.

In a composite index, column order decides everything

A composite index is like a phone book: entries are sorted by (last name, first name). Know the last name — you find it instantly; know only the first name — you flip through the whole book.

CREATE INDEX idx_att_student_date ON attendance (student_id, lesson_date);

With this index:

• WHERE student_id = 42 — works (leftmost prefix);
• WHERE student_id = 42 AND lesson_date >= '2026-05-01' — works fully;
• WHERE lesson_date = '2026-05-10' — does not work: without the first column there is no entry point into the tree.

If the "whole-school attendance for a date" query is frequent, it gets its own (lesson_date) index. Practical rule: columns filtered by equality (=) go at the front of the index, the range column (>=, BETWEEN) goes last.

Learning to read EXPLAIN ANALYZE

Whether an index is working or not — don't guess, ask:

EXPLAIN ANALYZE
SELECT * FROM attendance
WHERE student_id = 42 AND lesson_date >= date '2026-05-01';

Index Scan using idx_att_student_date on attendance
  (cost=0.43..12.10 rows=38 width=29)
  (actual time=0.031..0.058 rows=41 loops=1)
  Index Cond: ((student_id = 42) AND (lesson_date >= '2026-05-01'::date))
Planning Time: 0.210 ms
Execution Time: 0.089 ms

What I look at first:

• Seq Scan — the whole table is being read. Fine for a small table; a red flag for a filtered query on a big one.
• Index Scan — only the needed rows are reached through the tree.
• Bitmap Heap Scan — the middle ground: a "map" of matching pages is collected from the index, then the pages are read in one pass. Normal at medium selectivity.
• rows (estimate) vs actual rows — the planner's prediction against reality. If they differ by more than 10x, the statistics are stale — run ANALYZE attendance;, or the planner will keep choosing bad plans.

Also watch the Index Cond line: is your condition there, or did it fall down into Filter below — that shows whether the index actually covers the condition.

Partial and covering indexes

A partial index indexes only the part of the table you need. In attendance, ~95 percent of rows are present, but reports almost always care about who was absent:

CREATE INDEX idx_att_absent ON attendance (lesson_date)
WHERE status = 'absent';

This index stores only the absent rows — it's twenty times smaller than a full one, cheap on writes, and ideal for "who missed class that day" queries. The condition must be repeated verbatim in the query: WHERE status = 'absent' AND lesson_date = ....

A covering index (INCLUDE) adds extra columns the query needs into the index leaf:

CREATE INDEX idx_att_report ON attendance (student_id, lesson_date)
INCLUDE (status);

If the report query asks for only these three columns, PostgreSQL performs an Index Only Scan — it never touches the table at all. Our monthly attendance report sped up noticeably thanks to exactly this index. One caveat: to see Heap Fetches: 0, VACUUM has to run regularly on the table.

Too many indexes hurt too

An index isn't free. Every INSERT and UPDATE updates every index on the table. attendance receives hundreds of rows every lesson — each unnecessary index on it slows down every write and eats disk space. Statistics will tell you which indexes are dead weight:

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Non-unique indexes with idx_scan = 0 are candidates for removal. I run this query once a quarter.

Takeaways

• An index is a sorted structure: a function on the column, a leading %, or a wrong type cast switches it off.
• A Seq Scan at low selectivity is not a bug — it's the planner making the right call.
• Composite index order matters: equality columns first, the range column last; without the leftmost prefix the index doesn't work.
• Don't guess — run EXPLAIN ANALYZE and compare the rows estimate against actual.
• Partial and INCLUDE indexes in the right place beat plain indexes by a wide margin.
• Check pg_stat_user_indexes quarterly and drop the dead indexes.

An index isn't "add it and forget it" — it's a tool that lives alongside your queries. Make friends with EXPLAIN ANALYZE, and it will tell you all its secrets itself.