Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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



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


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


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


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


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


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.


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




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: