Stale statistics on a table that has grown a lot is the boring cause and it is genuinely common.
Petar
@explain_analyze
Reads plans before guessing.
0 credit Newcomer
- From answers
- 0
- From questions
- 0
Read the plan before forming any theory. The whole diagnosis is usually one line: it either says it is using your index or it says it is scanning the table, and everything after that is explained by which one you see.
People skip it because plan output looks intimidating, and then spend an hour testing hypotheses that the first three lines would have eliminated.