pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedview columns and performance
2+ messages / 2 participants
[nested] [flat]
* view columns and performance
@ 2010-07-30 14:55 Eric Schwarzenbach <subscriber@blackbrook.org>
0 siblings, 1 reply; 2+ messages in thread
From: Eric Schwarzenbach @ 2010-07-30 14:55 UTC (permalink / raw)
To: pgsql-performance
I'm wondering whether columns, in the select list of a view that is used
in a join, which are not used either as join criteria or in the select
list of the overall query, effect the performance of the query.
In other words supposed I define a view something like
CREATE view MyView AS SELECT a,b, c, d, e, f, g FROM (several tables
joined together)
Assume for the sake of simplicity there are no aggregates or such, we're
just joining tables, and getting a bunch of columns back each of the
various tables.
I then then perform a query something like
SELECT v.a, x.h, y.i FROM MyView as v JOIN otherTable as x on (x.m =
v.a) JOIN yetAnotherTable as y on (y.n=v.a)
Does all the extra clutter of b,c,d,e,f in MyView affect the performance
of the query by taking up extra space in memory during the joins or is
the optimizer smart enough to realize that they aren't needed and evoke
the query as if MyView were really defined as
CREATE view MyView AS SELECT a FROM (several tables joined together)?
Thanks,
Eric
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: view columns and performance
@ 2010-07-30 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eric Schwarzenbach <subscriber@blackbrook.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-07-30 15:11 UTC (permalink / raw)
To: Eric Schwarzenbach <subscriber@blackbrook.org>; +Cc: pgsql-performance
Eric Schwarzenbach <subscriber@blackbrook.org> writes:
> I'm wondering whether columns, in the select list of a view that is used
> in a join, which are not used either as join criteria or in the select
> list of the overall query, effect the performance of the query.
If the view gets "flattened" into the calling query then unreferenced
columns will be optimized away, otherwise probably not. You haven't
given enough details about your intended view definition to be sure
whether it can be optimized, but if it's just a join it's fine.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-07-30 15:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-30 14:55 view columns and performance Eric Schwarzenbach <subscriber@blackbrook.org>
2010-07-30 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox