Four Postgres indexes that fixed a 135,000-candidate search

The candidate search screen was a recruitment platform's most-used feature and its slowest: 135,000 candidate rows, recruiters filtering by name, skills, status and job, and a p95 of a little over four seconds. Every fix that eventually worked was an index — but four different indexes, for four completely different reasons. The interesting part isn't the DDL; it's matching each index type to the query shape that deserves it.

One rule governed the whole week, so I'll state it first: no index without an EXPLAIN (ANALYZE, BUFFERS) before and after, on a production-sized copy. Guessing at indexes adds write cost and vacuum load for speculative benefit. Every number below is from that discipline.

Index 1 — trigram GIN, for search-as-you-type

Recruiters search names and skills the way humans do: fragments, middles of words. That means ILIKE '%hasan%' — and a leading wildcard makes a normal B-tree useless, so Postgres walks all 135k rows:

SELECT id, full_name, headline
FROM candidates
WHERE full_name ILIKE '%hasan%' OR skills_text ILIKE '%hasan%';
-- Seq Scan on candidates ... Execution Time: 3411.208 ms

The pg_trgm extension indexes every three-character sequence, which is exactly the structure substring matching needs:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX candidates_name_trgm
    ON candidates USING gin (full_name gin_trgm_ops);
CREATE INDEX candidates_skills_trgm
    ON candidates USING gin (skills_text gin_trgm_ops);
-- Bitmap Index Scan ... Execution Time: 47.615 ms

3,411ms to 48ms, and it accelerates ILIKE, regex and similarity ranking alike. Cost worth naming: GIN indexes are big (ours was about a third of the table) and slow inserts a little. For a read-dominated search table, that trade is a bargain.

Index 2 — partial, for the rows everyone actually looks at

The default screen shows active candidates — about a fifth of the table. Archived and hired candidates sit in the same table, mostly untouched. A partial index only bothers indexing rows matching its predicate:

CREATE INDEX candidates_active_updated
    ON candidates (updated_at DESC)
    WHERE status = 'active';
-- 912 ms → 21.847 ms on the default listing

The index covers 27k rows instead of 135k: a fifth of the size, hotter in cache, cheaper to maintain — and archived candidates never touch it again after their final update. The condition to remember: the query must repeat the WHERE status = 'active' literally for the planner to use it. Ours always did, because the default view is generated by one query builder in one place.

Index 3 — composite, for filter-then-sort

The per-job pipeline view was the sneaky one: filter by job, order by application date, paginate.

SELECT id, full_name, stage
FROM applications
WHERE job_id = 4102
ORDER BY applied_at DESC
LIMIT 25;

There was an index on job_id. The plan used it, fetched all nine thousand applications for the job, and then sorted them — every page load paid for a sort of which it kept 25 rows. The composite index bakes the sort into the index order, so Postgres just walks it:

CREATE INDEX applications_job_applied
    ON applications (job_id, applied_at DESC);
-- Sort node gone. 1,688 ms → 7.9 ms

Column order is the whole trick: equality columns first, then the sort column, matching the query's shape exactly. This one line was the largest single win of the week, and it's the index people most often almost-have.

Index 4 — covering, for the list that only shows five fields

After the first three fixes, the listing was fast but still touched the table heap for every row — the index found the matches, then fetched full rows just to display name, headline and stage. INCLUDE stores those display columns inside the index leaf, upgrading the plan to an index-only scan:

CREATE INDEX applications_pipeline_covering
    ON applications (job_id, applied_at DESC)
    INCLUDE (candidate_id, stage, rating);
-- Index Only Scan, Heap Fetches: 0.  131 ms → 11.2 ms

Honesty about this one: it's a refinement, not a rescue — the milliseconds mattered because recruiters hammer this screen hundreds of times a day, and it replaced index 3 (same key columns) rather than stacking a fourth write penalty on the table. Watch Heap Fetches in the plan: if it isn't zero, autovacuum isn't keeping the visibility map fresh enough for the index to pay off.

Before and after

The screen's p95 went from just over four seconds to about 120 milliseconds end to end, and it held there as the table grew — because each index matches a query shape, and shapes scale where lucky plans don't.

The part that isn't DDL

Three habits mattered more than any single index. Test against a production-sized copy, because the planner changes its mind with scale — two of these four indexes are ignored on a 5k-row dev database. Change one thing at a time, or you won't know which change worked (and one of our four originally "worked" only because a different index had landed in the same batch). And read the plan's reasons, not just its total: Seq Scan says missing index, a Sort node says wrong column order, Heap Fetches says almost-covering. The four seconds were never one problem. They were four problems, and the plan named each one — every fix was already written down in the output of EXPLAIN ANALYZE, waiting for someone to read it slowly.