4 ms·
> 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 ha
by gopalv 4y ago
> 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 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 4y agoHow would that work? Your schema allows an employee to be assigned to multiple departments, and the query must count them.
- doctor_eval 4y agoThe schema only allows one employee per department, so there is no need to look up the employee table.
- jmalicki 4y agoBecause of the foreign key, the query should reduce to "explain select count(*) from Department d;"
- deleted 4y ago[deleted]
- atwebb 4y agoIt is also called Table Elimination if you want a google-able term.
- taspeotis 4y agoI would try running that fiddle on something more recent than Postgres 9.6
- deleted 4y ago[deleted]