Re: Under the hood of views

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