Hacker News new | ask | show | jobs
by hinkley 748 days ago
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.
2 comments
perrygeo 748 days ago
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.
link
hinkley 747 days ago
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.

link
marcosdumay 748 days ago
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.

link
gurjeet 748 days ago
This is not true. Even OP says so

> ... foreign keys are not indexed by default.

link