Composite and covering indexes

Real queries filter on more than one column and want results in some order. A single-column index often can't serve them well. A composite index on several columns can, if the columns are in the right order.

The phone-book rule

A composite index on (last_name, first_name) sorts like a phone book: by last name, then by first name within each last name.

• Find "Smith, Ana": jump straight there. Uses both columns.
• Find all "Smith"s: one contiguous range. Uses the first column.
• Find everyone named "Ana": they're scattered across every last name. The index doesn't help.

That is the leftmost-prefix rule: an index on (a, b, c) serves filters on a, on a, b, and on a, b, c, but not on b or c alone.

Ordering columns

A practical recipe for column order:

For "the latest 20 posts by author 7", an index on (author_id, created_at DESC) lets the database jump to author 7's section and read 20 entries in order, then stop. No sorting, no scanning other authors. This exact shape (partition by an id, order by time) appears in feeds, chat histories, order lists and audit logs.

1 create index posts_by_author on posts (author_id, created_at desc);
2 
3 select id, title from posts
4 where author_id = 7
5 order by created_at desc
6 limit 20;

Covering indexes

After finding entries in the index, the database usually fetches each matching row from the table, often a random read per row. If the index already holds every column the query needs, it can skip that step entirely: an index-only scan.

1 -- Covers "id, title of the latest posts by an author"
2 create index posts_by_author_covering
3   on posts (author_id, created_at desc) include (title);

Covering indexes are a great fix for a hot query that reads many rows. They cost more space and slower writes, so use them for specific, important queries.

Selectivity and when the planner ignores you

The planner uses an index when it expects to read a small fraction of the table. An index on a boolean is_active where 95% of rows are active won't be used for is_active = true; scanning is cheaper. Two useful tools:

• Partial indexes index only the rows you query: create index on orders (created_at) where status = 'pending'. Small, fast and cheap to maintain.
• Expression indexes index a computed value: create index on users (lower(email)) makes case-insensitive lookups fast.