Why is Postgres not using my index?

There are only two answers and they need separating before anything else. Either the index is not usable for this query, in which case no amount of tuning will change the plan, or it is usable and the planner costed the table scan lower. The first is a mismatch you can fix in the query or the index. The second is an estimate, and the estimate is sometimes correct.

When the index cannot be used at all

A condition has to match the index the way the index was built. Wrapping the column in a function, comparing it to a value of a different type that forces the column to be cast, or using a pattern with a leading wildcard all prevent a plain index from applying, because the stored keys are not what the query is asking about.

Collation is the subtle member of this family. A text index is ordered by a specific collation, and a comparison under a different one cannot use it. This is also why an operating system collation change quietly invalidates text indexes across an entire cluster.

Partial indexes only apply when the planner can prove the query falls inside their predicate, and that proof is literal rather than clever. Multi-column indexes can be used from the leading column inward and not from the middle.

When it could and chose not to

Now the estimate matters. An index scan reads the index and then fetches rows from the table, and past a certain fraction of the table that is more work than reading the table in order, not less. Returning most of the rows is exactly the case where a sequential scan is the right plan, and the query that looks wrong is fine.

Three things distort the choice. Stale statistics, so the planner expects a different number of rows than exists, which is what makes analyzing after a bulk load so load-bearing. Correlated columns, because independence is assumed unless you create the statistics that say otherwise. And storage cost assumptions inherited from spinning disks, which overstate the price of random reads on anything modern and push the planner toward sequential scans on hardware where they are no longer cheaper.

Disabling sequential scans is a diagnostic, never a fix: if the forced plan is faster, you have learned the estimate is wrong, and the estimate is what to correct.

Unused and missing indexes covers proving an index earns its write cost before adding another. If the plan does use the index but still reads the table for every row, the thing to understand is visibility, not the index.