You do not need to know how a B-tree is implemented. You do need to know which index answers which question, and how to check whether Postgres agrees with you.
Start with EXPLAIN ANALYZE
Guessing is expensive. Ask:
EXPLAIN ANALYZE
SELECT id, title FROM "Post"
WHERE status = 'PUBLISHED'
ORDER BY "publishedAt" DESC
LIMIT 12;If you see Seq Scan on a large table, the index you thought you had is not being used. Index Scan or Index Only Scan means it is.
Composite indexes are ordered
This is the detail that trips people up. An index on (status, published_at) can serve:
WHERE status = ?WHERE status = ? ORDER BY published_at
but not WHERE published_at > ? on its own. Think of it as a phone book sorted by last name then first name: useless for finding everyone named "Thanh".
Put the equality column first, the range or sort column second.
Partial indexes for skewed data
If ninety percent of your rows are drafts and you only ever list published posts, index the slice you read:
CREATE INDEX post_published_idx
ON "Post" ("publishedAt" DESC)
WHERE status = 'PUBLISHED';Smaller index, faster writes, same reads.
Text search needs a different index type
B-trees cannot help with ILIKE '%react%'. For substring search, a trigram index does:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX post_title_trgm_idx ON "Post" USING GIN (title gin_trgm_ops);For real full-text search with ranking and stemming, use tsvector instead:
CREATE INDEX post_search_idx ON "Post"
USING GIN (to_tsvector('english', title || ' ' || coalesce(excerpt, '')));Reach for this when ILIKE stops being fast enough — not before. On a few thousand rows, ILIKE is genuinely fine.
Indexes are not free
Every index has to be updated on every write and occupies real disk. A table with twelve indexes has slow inserts and a confused planner. Add them in response to a slow query, remove them when the query is gone, and check pg_stat_user_indexes occasionally to find the ones nobody reads.