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

Re: Under the hood of views

From: Greg Stark <gsstark(at)mit(dot)edu>
To: David Kerr <dmk(at)mr-paradox(dot)net>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Under the hood of views
Date: 2009-08-13 23:14:00
Message-ID: 407d949e0908131614g53e882d0v7f40f498ec2e290@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Aug 14, 2009 at 12:04 AM, David Kerr<dmk(at)mr-paradox(dot)net> wrote:
> On Thu, Aug 13, 2009 at 05:28:01PM +0100, Richard Huxton wrote:
> - David Kerr wrote:
> - >
> - >create view test as
> - >select a,b,c,d,e,f,g from testtable;
> - >
> - >select a from test;
> - >
> - >(does the engine retrieve b-g?)
> -
> - Shouldn't - the query just gets rewritten macro-style. I don't think it
> - eliminates joins if you don't need any columns, but that's not possible
> - without a bit of analysis.

In the case above everything is simple enough that the planner will
certainly collapse everything and it'll be exactly as if you juts
wrote the first query. In more complex cases involving LIMIT or GROUP
BY etc that may not be true.

However there's an underlying question here, what do you mean by
"retrieve"? The database always reads the entire row from disk
anyways. In fact it reads the whole block that the row is on.

If there are large values which have been toasted they're never
retrieved unless you actually need their values either for some
operation such as a function or operator or because they're in the
final output to send to the client.

If you mean what is sent over the wire to the client then only the
columns listed in the final select list get sent to the client.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2009-08-13 23:16:37
Subject: Re: freezing tuples ( was: Why is vacuum_freeze_min_age 100m? )
Previous:From: Josh BerkusDate: 2009-08-13 23:07:39
Subject: Re: freezing tuples ( was: Why is vacuum_freeze_min_age 100m? )

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