6

I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL.

If a view is doing joins that don't:

  • add any columns
  • eliminate rows
  • duplicate rows
  • introduce nulls

will the joins be ignored for the queries that don't need them. Or is it a SQL Server thing only ?

4
  • 2
    It's generally called join removal in PostgreSQL. Unsure what the exact rules are; varies somewhat with version as it's been enhanced over time. Dec 17 '15 at 3:15
  • 2
    Check your execution plan, it will show you Dec 17 '15 at 6:53
  • @a_horse_with_no_name So there is no generic rule ? Just case by case ?
    – ITChap
    Dec 18 '15 at 3:04
  • I haven't seen the video but I'm pretty sure than SQL Server's optimizer does not catch all possible cases of join removal. Jan 6 '16 at 10:05
4

There is an example about this feature in the PostgreSQL "Whats new in 9.0". If the optimizier detects, that a join can not add any additional rows, it will be dropped. The PostgreSQL Feature does not remove joins from views if they are not needed because the requested columns are not selected.

Also, the feature is limited on LEFT JOINs right now ( version 9.4 ). This may change in future versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.