Re:Ten years of improvements in PostgreSQL's optimizer
by Sesse__ on Wednesday April 17, 2024 @05:40PM
(#40070349)
Having 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.
Re:Ten years of improvements in PostgreSQL's optimizer
by darksaints on Thursday April 18, 2024 @01:41PM
(#40078729)
It'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.
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?