It's not a "stupid reason". Indexes are not free, you either pay to maintain indexes or you pay for a full table scan at query time. I for one want that control. First of all, tables < millions of rows can fit comfortably into memory and a full table scan can be perfectly fine. The "Index every possible thing" strategy can backfire horribly and cause massive inefficiencies - only to discover half the indexes aren't even getting used. Benchmark first.
Comments
Why aren’t indexes for FK relationships the default?
If you really don’t want one there should be a hint/pragma to turn it off.
It’s just such a stupid reason for a full table scan.
It's not a "stupid reason". Indexes are not free, you either pay to maintain indexes or you pay for a full table scan at query time. I for one want that control. First of all, tables < millions of rows can fit comfortably into memory and a full table scan can be perfectly fine. The "Index every possible thing" strategy can backfire horribly and cause massive inefficiencies - only to discover half the indexes aren't even getting used. Benchmark first.
Foreign key indexes are pretty cheap. If you have a spot where that’s breaking your architecture, that might be your architecture and not the index.
And as I already said, you should be able to opt out.
On Postgres, they are. You'll get an informational message saying the index was automatically generated.
But this is not normal behavior. I think Postgres is the only one that does this.
This is not true. Even OP says so