What is the Salesforce query optimiser doing, and why does adding an index sometimes not help?

The optimiser decides, per query, whether to use an index or scan the table, and it decides on estimated selectivity:

1. Selectivity threshold: It uses an index only when the filter is expected to return below a threshold share of rows — commonly cited as roughly 30% of the first million records and about 15% beyond that, lower for standard indexes. Those figures move, so I treat them as an order of magnitude, not a contract.
2. Why an index can be ignored: A checkbox or an evenly distributed four-value picklist over 30 million rows returns far too many matches. The optimiser is behaving correctly by scanning; the field is simply not selective.
3. Index-defeating constructs: Leading wildcards in LIKE, negative operators such as != and NOT IN, comparisons on null, and formula fields that are not deterministic will all prevent index use even on an indexed field.
4. What actually fixes it: Add a genuinely selective filter alongside the weak one — an indexed date range, an owner, an External Id. Or change the shape of the problem: pre-aggregate into a summary object, use a skinny table, or move the volume to a Big Object.
5. How I diagnose it: The Query Plan tool in the Developer Console shows the plan the optimiser chose and its cost, which turns this from an argument into a measurement.