Fixing PostgreSQL Partial Index Ignored When WHERE Clause Uses a Cast

You created a partial index to speed up a common filtered query. The index is there — \d tablename proves it — but EXPLAIN ANALYZE still shows Seq Scan. The table has millions of rows and the query is slow. The most common reason PostgreSQL ignores a perfectly valid partial index is a silent type mismatch introduced by an implicit or explicit cast in the WHERE clause.

What You'll Learn

• Why PostgreSQL's planner rejects a partial index when the predicate types don't align
• How to read EXPLAIN ANALYZE output to confirm a cast is the culprit
• Four concrete fixes, from simplest to most involved
• Common gotchas that bring the problem back after you think it's solved

Prerequisites

• PostgreSQL 13 or later (the behavior exists in all versions, but output formatting varies slightly)
• Permission to run EXPLAIN ANALYZE and CREATE INDEX on the affected table
• Basic familiarity with PostgreSQL data types — integer, text, varchar, numeric, bigint

How PostgreSQL Decides Whether a Partial Index Applies

A partial index is defined with a WHERE predicate that limits which rows are indexed. Before the planner can use that index for a query, it must prove that the query's own filter implies the index predicate — meaning every row the query could touch is guaranteed to be inside the index.

PostgreSQL does this implication check through its constraint exclusion and predicate proof system. The check is purely syntactic and type-aware: the planner does not evaluate expressions at plan time. If your query filter and the index predicate look semantically identical but involve different types, the planner cannot prove the implication and silently falls back to a sequential scan.

This is not a bug. It is a deliberate safety choice: the planner will never use an index it cannot formally prove is correct for the query.

The Cast Problem in Practice

Here is a minimal reproduction. Suppose you have an orders table where status is stored as integer, and you create a partial index for pending orders:

CREATE TABLE orders (
    id         bigserial PRIMARY KEY,
    status     integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Partial index: only rows where status = 1
CREATE INDEX idx_orders_pending
    ON orders (created_at)
    WHERE status = 1;

Now you run a query, but an ORM or application layer passes the status value as a string literal — or you write it as one by habit:

SELECT id, created_at
FROM orders
WHERE status = '1'
  AND created_at > now() - interval '7 days';

The string literal '1' has type unknown (or is cast to text depending on context). PostgreSQL must cast it to integer to evaluate the comparison. That cast changes the expression from status = 1 (integer literal) to status = '1'::integer, which is not syntactically identical to the index predicate status = 1. The planner cannot prove the implication. Index ignored.

The same failure mode appears with:

• varchar column indexed with a text literal in the query (or vice versa)
• numeric column where the query passes a float parameter
• boolean column where the query compares against 'true' instead of TRUE
• Any ORM that binds parameters as text regardless of the underlying column type

Diagnosing the Issue with EXPLAIN ANALYZE

Before you change anything, confirm the index is being skipped and understand why. Run the full explain with buffers:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, created_at
FROM orders
WHERE status = '1'
  AND created_at > now() - interval '7 days';

A sequential scan output looks like this:

Seq Scan on orders  (cost=0.00..45231.00 rows=1843 width=16)
                    (actual time=0.042..312.881 rows=1821 loops=1)
  Filter: ((status = 1) AND (created_at > ...))
  Rows Removed by Filter: 2498179
Buffers: shared hit=12043

Notice that the Filter line shows status = 1 (the cast has already been resolved), yet the planner still chose a sequential scan. That tells you the index predicate proof failed at planning time, not at execution time. The index is structurally sound — the query just can't use it as written.

You can also check what predicate is stored on your index:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
  AND indexname = 'idx_orders_pending';

Compare the predicate in indexdef character by character against what your query sends. Any difference in type annotation is your culprit.

Fix 1: Match the Literal Type to the Column Type

The simplest fix is to stop using string literals for non-text columns. Change your query to use a properly typed literal:

-- Before (string literal, causes implicit cast)
SELECT id, created_at
FROM orders
WHERE status = '1'
  AND created_at > now() - interval '7 days';

-- After (integer literal, matches index predicate)
SELECT id, created_at
FROM orders
WHERE status = 1
  AND created_at > now() - interval '7 days';

Run EXPLAIN ANALYZE again and you should see Index Scan using idx_orders_pending instead of Seq Scan. This fix costs nothing and is the right answer when you control the query text.

If your application passes values through a prepared statement or ORM, make sure the bind parameter type matches the column type. In Python with psycopg2 or psycopg3, pass 1 (an int), not '1' (a str). In Java with JDBC, use setInt not setString. The wire type matters because PostgreSQL uses it during plan caching for generic plans.

Fix 2: Change the Index Predicate to Match Your Query Pattern

Sometimes you cannot change the query — it comes from a third-party tool, a legacy application, or a reporting layer you don't own. In that case, rebuild the index so its predicate matches the exact expression the query uses:

-- Drop the old index
DROP INDEX idx_orders_pending;

-- Rebuild with a predicate that matches the cast expression your query produces
CREATE INDEX idx_orders_pending
    ON orders (created_at)
    WHERE status = 1::integer;

Or, if the query genuinely compares against a text value (e.g., status is a text column):

CREATE INDEX idx_orders_pending
    ON orders (created_at)
    WHERE status = 'pending';

The key is that the predicate in the index definition must be syntactically and semantically identical to the filter in your query, including the resolved type. Check pg_indexes.indexdef after creation to verify PostgreSQL stored the predicate the way you intended.

Fix 3: Use an Expression Index When a Cast Is Unavoidable

In some situations a cast in the query is genuinely unavoidable — for example, a polymorphic column stored as text that you must compare against an integer. You can build an expression index that pre-applies the cast on the stored values and then write your query to match:

-- status is stored as text, but queries always cast it to integer
CREATE INDEX idx_orders_status_int
    ON orders ((status::integer))
    WHERE status::integer = 1;

-- Query must reference the same expression
SELECT id, created_at
FROM orders
WHERE status::integer = 1
  AND created_at > now() - interval '7 days';

Expression indexes are powerful but come with a cost: the cast expression is evaluated on every write, and the index is slightly larger than a plain column index. Use this only when you can't fix the column type. If you're in a position to change the column type with ALTER TABLE ... ALTER COLUMN ... TYPE integer USING status::integer, that is almost always the better long-term choice. A related class of write-side problems — including how row changes can silently affect triggers — is covered in detail in the article on fixing PostgreSQL upsert behavior that breaks updated_at timestamps.

Fix 4: Set enable_seqscan = off to Confirm the Index Works

Before you drop and recreate an index in production, use this diagnostic trick to confirm the index would work if forced:

SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE status = 1
  AND created_at > now() - interval '7 days';

RESET enable_seqscan;

With sequential scans disabled, PostgreSQL will use the index if it possibly can. If EXPLAIN still shows a sequential scan even with enable_seqscan = off, the index is fundamentally unusable for this query — wrong columns indexed, wrong predicate, or the table statistics make it impossible. If the index does appear now and the timing is much faster, you've confirmed the fix direction and just need to update your query or rebuild the index.

Always reset enable_seqscan immediately. Never leave it off in a long-lived session. A similar diagnostic approach applies when investigating wrong rank output in ordered queries — the guide on fixing PostgreSQL window function rank errors with ties shows how to use planner output to pinpoint ordering problems.

Common Pitfalls to Watch For

ORM parameter binding

ORMs like SQLAlchemy, ActiveRecord, and Hibernate often bind all scalar parameters as text and let PostgreSQL cast them. Even if your Python code passes an integer, check the actual wire protocol using pg_stat_activity or a query log with log_min_duration_statement = 0. The logged query will show you the literal type PostgreSQL received.

Cached generic plans

PostgreSQL caches prepared statement plans after five executions. If the first five executions used string literals, the cached plan may have chosen a sequential scan and will keep using it until the plan is invalidated. Run DEALLOCATE ALL in a test session to force re-planning, or use pg_stat_statements to check whether multiple plan types are being cached for the same query.

Collation mismatches on text columns

For text or varchar partial indexes, collation differences can also block predicate proof. If your column uses a non-default collation and your query literal doesn't specify one, the planner may treat them as incompatible. Check pg_attribute.attcollation against pg_collation for the column and make sure the index predicate uses the same collation.

Dropping the wrong index

If you have multiple partial indexes on the same table, use the full index name in DROP INDEX and verify with \d tablename before and after. Accidentally dropping an index used by a different query pattern can introduce a new slow query somewhere else. Also note that if you need to rebuild an index on a large live table, prefer CREATE INDEX CONCURRENTLY to avoid locking writes.

Statistics not refreshed after a bulk load

If you recently bulk-inserted rows that match the partial index predicate, the planner's row estimates may still reflect the old state. Run ANALYZE orders; to refresh statistics. A stale row count estimate can make the planner think a sequential scan is cheaper even when the index would win. This is a separate issue from the cast problem but often co-occurs after migrations or data loads.

Wrapping Up

PostgreSQL's partial index predicate proof is strict by design. A single character difference in type between the index definition and the query filter is enough to make the planner skip your index entirely — no warning, no error, just a slower query.

Here are the concrete actions to take right now:

1. Run EXPLAIN (ANALYZE, BUFFERS) on your slow query and confirm it shows Seq Scan despite a matching partial index existing.
2. Compare your query's WHERE filter character-by-character against the predicate in pg_indexes.indexdef.
3. If the query uses a string literal for a non-text column, change it to a typed literal (Fix 1) — this is the most common cause and the cheapest fix.
4. If you can't change the query, rebuild the index with a predicate that matches the exact cast expression the query produces (Fix 2).
5. If the cast is genuinely unavoidable, create an expression index on the cast expression and align your query to match (Fix 3).

Once the planner picks the index, re-run EXPLAIN ANALYZE to verify the actual execution time dropped and that Rows Removed by Filter is gone from the index scan node. That's your confirmation the fix held.