Opptimizing projections containing unused columns

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Opptimizing projections containing unused columns
Date: 2008-10-15 11:59:49
Message-ID: 1B595E8C7B5E4DFC97C08BF681C4B257@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have lot of autogenerated from projection queries in form

SELECT source.c1, source.c2, t1.col1, t1.col2, ...
FROM (SELECT c1, c2, c3, ..... , c20 FROM ... WHERE ... ) source
LEFT JOIN t2 USING (somecolumn)

Main SELECT uses only few columns (source.c1 and source.c2 in this sample)
from source projection.
Inner table may have up to 500000 rows.

I read 8.4 WITH documentation and this describes somewhat that PostgreSQL
can discard unused columns automatically but this is not clear. I havent
found exact doc about this. I havent way how to determine this using EXPLAIN
or similar command.

Questions:

1. Does selecting of unused columns ( c3 ... c20) affect to query prefomance
?
2. Will PostgreSQL 8.0+ automatically exclude columns c3 .. c20 while
executing
query ?
3. Or should I make sql builder much more sophisticated so that it will not
generate columns c3 .. c20 when it creates sql ?

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Voras 2008-10-15 13:21:12 Re: Backup strategies
Previous Message Guillaume Lelarge 2008-10-15 11:40:30 Re: Backup strategies