# 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.

```text
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:
   ```sql
   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:
   ```sql
   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:
   ```sql
   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:
   ```sql
   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:
   ```sql
   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