PostgreSQL Basics #4 Index Fundamentals: When Indexes Get Used and When They Don't

Infrastructure PostgreSQL Database
4 min read

When a query is slow, an index is one of the first things to consider. The general principles of what an index is were covered in a separate article, so this chapter focuses on the PostgreSQL practice — less “how to create one” and more “why isn’t the one I created being used.” EXPLAIN, the diagnostic tool, gets its own treatment next chapter; here we establish the principles and rules.

B-tree: what the default index changes #

The default type for CREATE INDEX is B-tree. It keeps column values in a sorted tree, enabling lookups that walk down the tree instead of reading everything (a Seq Scan). That is the substance behind the dramatic improvement where a query that took seconds over millions of rows drops to milliseconds.

Create index 
CREATE INDEX idx_orders_user_id ON orders (user_id);

Because the structure maintains sort order, it supports not just = but ranges (<, >, BETWEEN), sorting (ORDER BY), and LIKE 'abc%' (prefix match). Conversely, LIKE '%abc' (suffix match) cannot be solved with a sorted structure and won’t use it. Indexes are created automatically for PK and UNIQUE constraints, but a common misconception is that foreign keys get one automatically — they do not. An FK column like orders.user_id is used constantly for joins and parent-deletion checks, so creating an index on it yourself is basic technique.

Reason 1: the leading column of a composite index is missing #

A multi-column composite index is used from the left. It’s the same structure as a phone book sorted by (last name, first name): without the last name, you can’t look someone up by first name alone.

Composite index and leading column 
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- Used: starts with user_id
WHERE user_id = 42 AND created_at >= '2026-12-01'
WHERE user_id = 42

-- Not used: created_at alone, without the leading column (user_id)
WHERE created_at >= '2026-12-01'

The base rule for composite-index column order: equality columns first, range columns after. Advanced strategies (partial, covering, GIN, and so on) come in the practice series.

Reason 2: transform the column and you lose the index #

An index is sorted on the stored values as they are. Wrap the column in a function and that sort order can’t be used.

The function-on-column trap 
-- Not used: function applied to the column
WHERE lower(email) = 'kim@example.com';
WHERE created_at::date = '2026-12-25';

-- Used: leave the column alone and rewrite the condition side
WHERE email = 'kim@example.com';
WHERE created_at >= '2026-12-25' AND created_at < '2026-12-26';

The second example — turning a date comparison into a range — comes up especially often in practice. If you genuinely must search by a transformed value, the answer is an expression index on that expression itself (CREATE INDEX ... ON users (lower(email))). In the same family of traps, type mismatch (comparing a text column with a number, for example) also defeats the index, because the implicit cast lands on the column side.

Reason 3: sometimes not using it is faster #

Even with an index present, the planner will sometimes deliberately skip it. The classic case is a low-selectivity condition. If status = 'done' matches 90% of the table, visiting 90% of rows one by one through the index costs more than just reading the whole table in order. The same holds for very small tables. So “there’s a Seq Scan” does not equal “there’s a problem” — the criterion is whether the condition narrows the rows enough, and the tool for checking that judgment with your own eyes is next chapter’s EXPLAIN.

The price: indexes are not free #

An index is “one more sorted copy of the table.” Every INSERT, UPDATE, and DELETE must update all indexes along with it, so more indexes mean slower writes and more storage. An index created “just in case” is a pure write tax. That makes the working order clear: don’t build from imagination in advance — build from the WHERE, JOIN, and ORDER BY columns of queries measured to be slow. The system for finding what’s slow (pg_stat_statements) and the cleanup of unused indexes are practice-series topics.

Summary #

  • The default B-tree index supports equality, ranges, sorting, and prefix LIKE. FK columns get no automatic index — create one yourself.
  • Composite indexes work from the left. Equality columns first, range columns after is the base ordering rule.
  • Transform the column and the index is lost. Rewrite the condition side (dates become range comparisons) or use an expression index. Type mismatch is the same family.
  • With low selectivity, skipping the index is correct behavior. A Seq Scan can be the result of judgment, not a crime.
  • Indexes are a write tax. The right order is building from measured slow queries, not imagination — and the observation tool, EXPLAIN, is next.
X