[gfody]

> If I’d written out the query, I might have seen that I didn’t need the JOIN (or never written it in the first place). Whereas the view hides that complexity. So they can make things easier, but that can lead to performance pitfalls if we’re not careful.

you can avoid this particular pitfall by using left joins for views like this (that join stuff in for convenience that you might not select) - postgres will eliminate a left join but not an inner join since the inner join could filter rows (eg symbols in stocks_real_time that don't exist in company (commercial engines will use the presence of fk constraints to know that's impossible and go ahead and eliminate inner joins as well))

[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.

[djk447]

NB: post author here!

Thanks yes! Totally true, was thinking about including some of that but it felt like it opened a can of worms about join types and why certain things would be included and others not (ie inner join needs to see that it's there on both sides whereas the left join doesn't) etc. and the post was already kinda long in the tooth.