News for hackers. Stuff that matters.
Re:Ten years of improvements in PostgreSQL's optimizer by da_chicken on Wednesday April 17, 2024 @03:14PM (#40068952)
> 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?

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.