SQL When an Index Is Not Used

Having an index does not guarantee the planner will use it.

What & Why

An index can exist while PostgreSQL still judges another plan cheaper.

Small tables are often cheaper to scan completely.

Low-selectivity predicates can match too much of the table for indexed lookup to help.

Wrapping an indexed column in a different expression can prevent the plain index from matching the predicate.

A leading wildcard does not provide a known B-tree prefix to seek from.

Stale statistics can make the planner misestimate row counts and choose the wrong access path.

The worked query keeps growth.users.created_at unwrapped and uses a half-open timestamp range so an ordinary timestamp index remains eligible.

See How It Works

BUSINESS QUESTION

Growth rewrites a date cast into a half-open timestamp range that can use an ordinary created_at index.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

SELECT
  id,
  channel,
  created_at
FROM growth.users
WHERE created_at >= TIMESTAMPTZ '2024-01-15 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2024-01-16 00:00:00+00'
ORDER BY created_at, id;

RESULT — users inside the January 15 boundary

id	channel	created_at
101	organic	2024-01-15 09:10:00+00

The half-open fixed-day range contains only user 101.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice