3 ms·
> Another omission is foreign key statistics. Joins along foreign keys should never have a bad plan, but they still occasionally do. I'm curious what you mean.
by da_chicken 3y ago
> Another omission is foreign key statistics. Joins along foreign keys should never have a bad plan, but they still occasionally do.
I'm curious what you mean. Postres, like many RDBMSs, does not automatically create an index on a FK. But you have to know that already.
Is it join sequencing?
- Sesse__ 3y agoHaving a foreign key should probably be a hint to the planner that it should create a cross-table correlation estimate on the source and destination columns. This is about join cardinality estimation, not having an index.
- darksaints 3y agoIt's not about indexing, though most casual users don't know that FKs are not indexed automatically. Postgres should be smart enough to know when an index is appropriate for a foreign key, but I respect the fact that the core team wants people to make explicit well-reasoned decisions to index instead of doing it automatically. Regardless of indexing, what actually matters is cardinality estimates. Foreign keys are almost always a reasonable hint to the SQL engine that there will be a large number of joins along foreign keys, and thus should be able to provide cross-table cardinality estimates.