Question :
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 ?
Answer :
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.