I added the index the slow query obviously needs and nothing changed - how do you find out why it is not being used?

The reasons are a short list and you can usually identify yours in a couple of minutes.

A function or a cast on the indexed column. If the query says something like lower of a column, or the column cast to a different type, the index on the plain column cannot be used: the planner would have to evaluate the function on every row anyway. This is the most common cause by a distance, and the cast version is sneaky because it is often implicit, inserted because the parameter type does not match the column type.

A leading wildcard in a pattern match. An index can find things that start with something; it cannot find things that end with something.

The wrong column order in a composite index. An index on two columns can serve a query filtering on the first, or on both, but generally not on the second alone. If your filter uses them in the other order than the index declares, it is not usable.

The planner thinks a scan is cheaper. If the filter matches a large fraction of the table, reading it all sequentially genuinely is faster, and the planner is right. This is the case where nothing is wrong.

Stale statistics, covered below.

A type mismatch between the column and the parameter, particularly with an ORM binding a parameter as text against a non-text column.

For reading the plan without learning the whole subject, there are three things to look at and you can ignore the rest at first.

Run it with the option that actually executes the query rather than only estimating, and with buffers. That gives you real row counts alongside the estimates, which is where the answer usually is.

Then:

Compare estimated rows against actual rows at each node. If the planner estimated a hundred and got half a million, it made its decision on bad information and that is your problem: statistics, or a correlation between columns it cannot see.

Find where the time actually goes. The node with the largest actual time is the one to care about. People often optimise a node that accounts for two percent of the query.

Look at the filter line under the scan. It shows what the condition became after parsing, and this is where you catch the implicit cast or the function wrapping - you will literally see it in the plan text even though your query looked clean.

A useful trick when you suspect the planner is choosing wrongly: temporarily discourage sequential scans in your session and re-run. If it then uses the index and is faster, the index is fine and the planner made a bad cost decision. If it uses the index and is slower, the planner was right. Do not leave that setting on; it is a diagnostic, not a fix.

A function or a cast on the indexed column silently disqualifies the index, and it is easy to add one without noticing.

The two boring causes worth ruling out before anything clever, because they are quick and they are common on tables that have grown fast.

Statistics are out of date. The planner decides using a sample of the table's contents, gathered periodically. On a table that has recently grown or changed shape a lot, those numbers can describe a table that no longer exists. Run an analyse on the table and re-check the plan. This takes seconds and it fixes a surprising share of these.

The index was never actually built, or was built on something slightly different from what you think. Check what exists on the table rather than what is in your migration file, particularly if you have been running migrations against a database that has diverged.

And one thing specific to using an ORM, since that is this room: look at the SQL that was actually sent. Log it. The query you wrote in the ORM and the query the database received are not always the same shape, and an ORM that wraps a column, adds a cast, or reorders conditions will produce exactly your symptom while your ORM code looks correct.

That is also the general lesson here: this is a database question wearing an ORM costume, and the fastest route to the answer is always to get the real SQL and the real plan in front of you rather than reasoning about the abstraction.