4 ms·
> 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.
by gfody 4y ago
> 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))
- djk447 4y agoNB: 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.
- 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]