Optimizing queries with projected columns

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimizing queries with projected columns
Date: 2008-10-15 10:23:37
Message-ID: gd4gcq$27kp$1@news.hub.org
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.

Does selecting of unused columns ( c3 ... c20) affect to query prefomance ?
Will PostgreSQL 8.0+ automatically exclude columns c3 .. c20 while executing
query ?
Or should I make sql builder much more sophisticated so that it will not
generate columns c3 .. c20 when
it creates sql ?
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.

Andrus.

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Voras 2008-10-15 10:31:08 Re: Backup strategies
Previous Message Craig Ringer 2008-10-15 10:13:51 Re: Backup strategies