On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Sun, Oct 18, 2009 at 10:35 AM, Tim Landscheidt
> <tim@tim-landscheidt.de> wrote:
>> Daniel Migowski <dmigowski@ikoffice.de> wrote:
>>
>>> 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). [...]
>>
>> How could that be done otherwise? PostgreSQL *must* look at
>> country to determine how many rows the left join produces.
>
> Even if country.id is a primary or unique key?
Well, we currently don't have any logic for making inferences based on
unique constraints. I have dreams of fixing that at some point (or
maybe I'll get lucky and someone else will beat me to it) but it's
currently in the category of "things for which I won't get paid but
would like to spend some of my spare time in the evenings on", so it
may be a while (unless of course it moves into the category of "things
people are paying me a lot of money to get done", in which case it
will likely happen quite a bit sooner...).
...Robert
