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. Commented Dec 17, 2015 at 3:15
  • 2
    Check your execution plan, it will show you
    – user1822
    Commented Dec 17, 2015 at 6:53
  • @a_horse_with_no_name So there is no generic rule ? Just case by case ?
    – ITChap
    Commented Dec 18, 2015 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. Commented Jan 6, 2016 at 10:05

1 Answer 1

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 and acknowledge you have read our privacy policy.

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