Re: Calculation of unused columns

From: Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Volker Grabsch <vog(at)notjusthosting(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Calculation of unused columns
Date: 2009-10-18 14:38:18
Message-ID: 4ADB285A.6030808@ikoffice.de
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Anj Adu 2009-10-18 15:24:19 Re: sequential scan on child partition tables
Previous Message Joe Uhl 2009-10-18 12:24:39 Re: Partitioned Tables and ORDER BY