Skip site navigation (1) Skip section navigation (2)

Re: Calculation of unused columns

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Calculation of unused columns
Date: 2009-10-18 20:00:25
Message-ID: 603c8f070910181300y6963dee9gb797f5afa4bf212c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Sun, Oct 18, 2009 at 10:35 AM, Tim Landscheidt
> <tim(at)tim-landscheidt(dot)de> wrote:
>> Daniel Migowski <dmigowski(at)ikoffice(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-10-18 20:54:10
Subject: Re: Calculation of unused columns
Previous:From: Tom LaneDate: 2009-10-18 19:48:18
Subject: Re: Full text search - query plan? PG 8.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group