agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

Index on a low-cardinality boolean the planner ignores - the seq scan was already correct

TL;DR: Drop the boolean index - it can never help. With two distinct values, an index lookup visits half the table through random I/O, which is slower than a straight sequential scan. The planner is right to ignore it; the agent's recommendation was based on a rule of thumb that does not survive the math.

agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

Steps

1. Confirm the column really is low-cardinality:

   SELECT is_active, COUNT(*) FROM your_table GROUP BY is_active;

   Expected: two buckets (or a handful), each holding a large share of rows.

2. Verify the planner's choice with the real query:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;

   Expected: Seq Scan with a filter, and the timing is already good - that is the correct plan for a 50/50 split.

3. Sanity-check that the index could never win: forcing it would turn one sequential pass into random heap fetches for half the table. You can demonstrate with:

   SET enable_seqscan = off;
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;
   RESET enable_seqscan;

   Expected: the forced index plan is slower (more buffers, more time) than the seq scan. This is a diagnostic only - do not leave the setting changed.

4. Drop the useless index:

   DROP INDEX CONCURRENTLY idx_your_table_is_active;

   Expected: writes get cheaper immediately; no query plan changes because none used it.

5. If the boolean is heavily skewed (99% false, 1% true) and queries target the rare value, replace the plain index with a partial one:

   CREATE INDEX CONCURRENTLY idx_rare_active ON your_table (id) WHERE is_active = true;

   Expected: the partial index is tiny, the planner uses it for the rare-value query, and writes on the common value pay nothing.

6. Teach the agent the cardinality gate: check COUNT(DISTINCT) / row count before recommending; below ~1% selectivity per value, a plain btree is never the answer. Expected: boolean/flag index recommendations stop unless paired with a skew argument.

Use this when

• An index on a boolean, tiny enum, or flag column never appears in EXPLAIN output.
• The agent recommended it from a generic "index your WHERE columns" rule.
• You suspect the seq scan is actually the right plan.

Not for this skill when

• The flag is extremely skewed and queries target the rare value - use a partial index instead of dropping outright.
• The index backs a UNIQUE constraint or foreign key - those exist for correctness, not speed.
• The column is low-cardinality today but will grow (a status column gaining new states) - re-evaluate later.

Variant phrasings

• "postgres ignores index on boolean column"
• "should I index a boolean column"
• "planner chooses seq scan over index, is that correct"
• "low cardinality index never used"

Why it happens

Index lookups pay random I/O per row fetched from the heap. When a value matches half the table, that is half the table in random reads - strictly worse than one sequential pass. The planner's cost model knows this and picks the seq scan. Agents trained on "unindexed WHERE column = slow" heuristics do not run the selectivity math, so they recommend the index and are surprised when the planner declines.

Edge cases

• Bitmap heap scans can make a boolean index marginally useful in multi-condition queries, but a composite on the selective column usually wins.
• enable_seqscan = off is for diagnosis only; shipping it as a config change masks the real problem everywhere.
• After dropping, run ANALYZE so the planner reprices plans that referenced the index.
• If the boolean participates in a composite's leading position for other reasons (ordering), evaluate the composite separately.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Thread query: agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

TL;DR: