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

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 (view raw or flat)
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

pgsql-performance by date

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

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