Why Postgres isn't using your index: seven causes, with EXPLAIN output you can reproduce

You ran CREATE INDEX, you ran the query, and EXPLAIN still says Seq Scan. Before you blame the planner, know that in most cases it is right about what you asked for and wrong only about what you meant. This post covers the seven reasons we see most often for PostgreSQL ignoring an index: a function on the column, a type mismatch, a leading wildcard, a filter that matches most of the table, stale statistics, a multicolumn index in the wrong order, and a table that is simply too small. Every case comes with a reproduction on PostgreSQL 17.11, the exact plan it produced, and the fix. Paste the setup into psql and, on default settings, you will get the same plans.

Put EXPLAIN in front of the query. A plan node called Index Scan, Index Only Scan or Bitmap Index Scan means an index is in use. Seq Scan (or Parallel Seq Scan) means the planner reads the whole table. EXPLAIN only plans the query; EXPLAIN ANALYZE also runs it and shows actual row counts, which you need for the stale statistics case below.

Two habits make plans easier to read and compare:

• EXPLAIN (COSTS OFF) removes the cost numbers, which change with table size and settings.
• EXPLAIN (ANALYZE, BUFFERS) shows how many pages a query really touched, not just how many the planner expected.

All seven cases use one table of 200,000 users. The data is generated, so there is nothing to download:

SET TimeZone = 'UTC';
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  status     text NOT NULL,
  org_id     int  NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g,
       'User' || g || '@Example.com',
       CASE WHEN g % 100 = 0 THEN 'banned' ELSE 'active' END,
       (g % 500) + 1,
       timestamptz '2026-01-01' + (g || ' seconds')::interval
FROM generate_series(1, 200000) g;

CREATE INDEX users_email_idx ON users (email);
ANALYZE users;

As a baseline, an exact match on the indexed column uses the index:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';

 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)

Everything below is a way of changing that query slightly and losing the index.

An index on email stores the values of email. A condition on lower(email) is a question about different values, so the index cannot answer it:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)

The plan is parallel, but it is still a sequential scan. The fix is an expression index, an index on the same expression the query uses:

CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (lower(email) = 'user42@example.com'::text)
   ->  Bitmap Index Scan on users_lower_email_idx
         Index Cond: (lower(email) = 'user42@example.com'::text)

The expression in the query has to match the expression in the index. lower(email) and lower(trim(email)) are different expressions. The same rule covers coalesce(col, 0), arithmetic such as price * 100, and date functions. Date functions have one more catch: on a timestamptz column, date_trunc('day', created_at) and created_at::date depend on the session time zone, so Postgres will not let you index them directly (functions in index expression must be marked IMMUTABLE). Index an expression that fixes the zone instead, such as ((created_at AT TIME ZONE 'UTC')::date), and use the same expression in the query.

When you can, rewrite the condition instead. created_at >= '2026-01-02' AND created_at < '2026-01-03' can use a plain index on created_at, while created_at::date = '2026-01-02' cannot.

When the two sides of a comparison have different types, Postgres has to convert one of them. If it converts the column, an ordinary index on the column no longer applies. Here id is a bigint, and the literal 42.0 is a numeric:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42.0;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id::text = '42';

 Index Scan using users_pkey on users
   Index Cond: (id = 42)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::numeric = 42.0)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::text = '42'::text)

Look at the Filter line: (id)::numeric is the column being cast. That cast is the giveaway, and it is the thing to hunt for whenever a plan shows Seq Scan on an indexed column.

The fix is to make the literal or the parameter match the column. In application code the usual source is a driver that sends a number as numeric, or a value that arrives as a string. Cast the parameter in the query (WHERE id = $1::bigint) or fix the type at the source. If the column itself has the wrong type, such as an identifier stored as text and compared to numbers, changing the column type is the real fix.

A B-tree index is sorted, so it can find everything that starts with User4 quickly. It cannot find everything that ends with 1234@Example.com, because the values are not sorted by their endings:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)

The pg_trgm extension indexes three-character fragments, which lets Postgres use an index for patterns with a leading wildcard, for ILIKE, and for % similarity searches. pg_trgm is among the extensions available on Relyt One. The index is a GIN index with the gin_trgm_ops operator class:

CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (email ~~ '%1234@Example.com'::text)
   ->  Bitmap Index Scan on users_email_trgm_idx
         Index Cond: (email ~~ '%1234@Example.com'::text)

Trigram indexes are larger than B-tree indexes and slower to update, and they help most when the search string is long enough to contain at least one full trigram. A pattern with fewer than three characters gives them little to work with. If your real need is searching words in text, use full-text search (tsvector) rather than LIKE.

An index pays off when it lets the database skip most of the table. If a condition matches 99% of the rows, reading the whole table in order is cheaper than jumping through the index 198,000 times, and the planner knows it:

CREATE INDEX users_status_idx ON users (status);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'active';
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'banned';

 Seq Scan on users
   Filter: (status = 'active'::text)

 Index Scan using users_status_idx on users
   Index Cond: (status = 'banned'::text)

This is the index working as designed: the same index is used for the 1% of rows and skipped for the 99%. Nothing needs fixing in the planner. If the query for the common value matters, the question is whether it should return that many rows at all (add a LIMIT, an extra filter or an aggregate).

The rare value is where you can improve things. A partial index covers only the rows you look up, so it is small and cheap to maintain:

DROP INDEX users_status_idx;
CREATE INDEX users_banned_idx ON users (id) WHERE status = 'banned';
EXPLAIN (COSTS OFF) SELECT id FROM users WHERE status = 'banned';

 Index Only Scan using users_banned_idx on users

The query's WHERE clause has to imply the index's WHERE clause. A query that asks for status = 'banned' can use this index. A query for another status cannot.

The planner does not look at your data when it plans a query. It looks at statistics collected by ANALYZE and estimates from those. When the table changes a lot after the last ANALYZE, the estimates stop matching reality and the plan can be wrong in either direction, including picking an index when the index is a bad idea.

Autovacuum normally refreshes statistics once about 10% of a table has changed (autovacuum_analyze_scale_factor = 0.1, plus a base of 50 rows). To show the effect deterministically we turn it off for one table, analyze it when it holds five rows, and then bulk-load 300,000:

CREATE TABLE events (id bigint, kind text, created_at timestamptz);
ALTER TABLE events SET (autovacuum_enabled = false);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events SELECT g, 'click', now() FROM generate_series(1, 5) g;
ANALYZE events;
INSERT INTO events SELECT g, 'view', now() FROM generate_series(1, 300000) g;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Aggregate  (cost=4.31..4.32 rows=1 width=8) (actual rows=1 loops=1)
   ->  Index Only Scan using events_kind_idx on events  (cost=0.29..4.30 rows=1 width=0) (actual rows=300000 loops=1)
         Index Cond: (kind = 'view'::text)
         Heap Fetches: 300000

The planner expected one row (rows=1) and got 300,000 (actual rows=300000). It chose an index for a lookup that reads the whole table, and Heap Fetches: 300000 means every row needed a visit to the table as well. After ANALYZE, the same query changes plan:

ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Finalize Aggregate  (cost=5558.22..5558.23 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather  (cost=5558.11..5558.22 rows=1 width=8) (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=4558.10..4558.11 rows=1 width=8) (actual rows=1 loops=2)
               ->  Parallel Seq Scan on events  (cost=0.00..4116.92 rows=176474 width=0) (actual rows=150000 loops=2)
                     Filter: (kind = 'view'::text)
                     Rows Removed by Filter: 2

The exact cost numbers vary a little from run to run, so do not compare them digit by digit. The lesson is about how to read a plan. Compare rows= with actual rows= on every node. When they differ by orders of magnitude, the problem is the estimate, not the index. Run ANALYZE after a bulk load or a large backfill, and check last_autoanalyze in pg_stat_user_tables if you suspect autovacuum is falling behind. For a column whose distribution is skewed, raising the statistics target (ALTER TABLE … ALTER COLUMN … SET STATISTICS 500) gives the planner a finer picture than the default of 100.

A multicolumn B-tree index is sorted by its first column, then by the second within each value of the first, and so on. A query that filters on the leading column can use it well. A query that skips the leading column cannot:

CREATE INDEX users_org_created_idx ON users (org_id, created_at);
ANALYZE users;

-- leading column constrained: the index is used
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE org_id = 7 AND created_at >= '2026-01-02';

-- leading column missing: it is not
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Bitmap Heap Scan on users
   Recheck Cond: ((org_id = 7) AND (created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on users_org_created_idx

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

If you need to filter on created_at alone, you need an index that leads with it. Putting the column with equality conditions first, and the range column last, is the usual rule for indexes that serve both kinds of query:

CREATE INDEX users_created_org_idx ON users (created_at, org_id);
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Index Scan using users_created_org_idx on users
   Index Cond: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

This one depends on your version. PostgreSQL 18 added skip scans for B-tree indexes, which let a query that omits the leading column use the index in some cases. We ran the same table and the same (org_id, created_at) index on PostgreSQL 18.6, and the range query on created_at alone used the (org_id, created_at) index there. On PostgreSQL 17, which Relyt One runs, it does not. A skip scan is only worth it when the planner expects to skip few distinct values of the leading column or the later condition is selective, as it was here, so an index designed for the query is still the more reliable choice.

Every index also costs something on every write. Before you add one, check that an existing index does not already cover the query.

A twenty-row table fits in one page. Reading that page is cheaper than reading an index page and then a table page, so the planner scans the table:

CREATE TABLE plans (id int PRIMARY KEY, name text);
INSERT INTO plans SELECT g, 'plan' || g FROM generate_series(1, 20) g;
ANALYZE plans;

EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;

 Seq Scan on plans
   Filter: (id = 7)

That is correct behavior, and it is a trap when you test on a small development database and expect the plan you will get in production, or the other way around. Test index decisions on data that looks like production, in size and in distribution.

There is also a diagnostic switch for the moments when you cannot tell whether the index is possible or just not chosen. Turn off sequential scans for one transaction and look at the plan:

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;

 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)

If the index appears, it was usable and the planner judged the table scan cheaper, so look at statistics, selectivity and table size. If it still does not appear, the index cannot serve the query as written, so go back to cases 1, 2, 3 and 6. Use this switch for diagnosis only. Do not leave it on, and do not put it in application code.

The opposite problem is indexes that nothing uses. They slow down every insert and update and take space. pg_stat_user_indexes counts index scans since the statistics were last reset:

SELECT count(*) FROM users WHERE email = 'User42@Example.com';
SELECT count(*) FROM users WHERE email = 'User43@Example.com';

SELECT pg_sleep(1.5);  -- statistics are reported with a short delay

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY idx_scan DESC, indexrelname;

After running the exact-match query on email twice on the test table, and waiting a moment for the statistics to be reported, the output was:

     indexrelname      | idx_scan
-----------------------+----------
 users_email_idx       |        2
 users_banned_idx      |        0
 users_created_org_idx |        0
 users_email_trgm_idx  |        0
 users_lower_email_idx |        0
 users_org_created_idx |        0
 users_pkey            |        0

Two cautions. EXPLAIN alone does not count as a scan, so only real executions show up. And an index with zero scans is a candidate for removal, not proof: it may exist to enforce a unique constraint, or serve a monthly report that has not run since the statistics reset.

1. Run EXPLAIN (ANALYZE, BUFFERS) and find the node that dominates.
2. In its Filter line, look for a function or a cast applied to the column (cases 1 and 2).
3. If the pattern starts with %, consider a trigram index (case 3).
4. Compare rows= with actual rows=. A big gap means ANALYZE (case 5).
5. Check how many rows the condition matches. If it is most of the table, the scan is correct (case 4).
6. For a multicolumn index, check whether the query constrains the leading column (case 6).
7. Test on realistic data, and use SET LOCAL enable_seqscan = off to tell "unusable" from "not chosen" (case 7).

On a big table, building an index is the expensive part: a plain CREATE INDEX blocks writes while it runs, and CREATE INDEX CONCURRENTLY avoids that at the cost of a slower build. On Relyt One every branch has its own compute and copy-on-write storage, so you can create a branch of your database, build the index there, run EXPLAIN (ANALYZE) against your real data, and throw the branch away if the plan does not improve. The main branch never builds the index and never sees the experiment. Compare plans and row estimates between the branch and production, not durations: the branch's compute may be sized differently and starts with a cold cache.

Start for free