I have a very common example which would illustrate the above problem a
bit more. Guess the following view on a company table, which references
the country of that company in another table. The view itself just
returns the company-id and the country-name,
create view companys_and_countries as
select company.id, country.name from company left join country on
(company.country_id = country.id);
Pleaso note we have a left join here, so the contents of country do by
no means affect the contents of the "id" row in that view. Lets see what
happens when we just query for the ids:
explain select id from companys_and_countries;
The join is done anyway, even if its removed (At least on Postgres 8.3).
The more common usecase would be having Display-Tables, where are
foreign keys are dereferenced to their values. One could store this in a
view, and then query only the columns one needs (This is especially
useful if the user is able to configure its client for which columns he
needs).
I would like if unnessecary joins would be cut off here, as well as
unnessecary columns. I know this would be a performance hit, so maybe a
session option would be the right way here?
Regards,
Daniel Migowski