[gopalv]

> commercial engines will use the presence of fk constraints

What, postgres doesn't do FK join removals?

Like I tried it right now and it didn't remove the hash-join

http://sqlfiddle.com/#!17/073747/2

Should've been just a count off the PK Employees table.

Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run something like a "select count(*) + where" or just project 2-3 columns out of a 60+ join view from a couple of tables.

And those just ran forever without the PK-FK removals.

[michael1999]

How would that work? Your schema allows an employee to be assigned to multiple departments, and the query must count them.

[atwebb]

It is also called Table Elimination if you want a google-able term.

[jmalicki]

Because of the foreign key, the query should reduce to "explain select count(*) from Department d;"

[doctor_eval]

The schema only allows one employee per department, so there is no need to look up the employee table.

[taspeotis]

I would try running that fiddle on something more recent than Postgres 9.6

[mdavidn]

I ran into this recently on a newer version. Postgres will not use the presence of a foreign key (referencing a unique index) to skip unnecessary inner joins.

[taspeotis]

I have done my own (simple) testing on Postgres 13 and was unable to make Postgres elide the join.