Lists: | pgsql-performance |
---|
From: | David Kerr <dmk(at)mr-paradox(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Under the hood of views |
Date: | 2009-08-13 16:07:48 |
Message-ID: | 20090813160748.GB88642@mr-paradox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
developer came by and asked me an interesting question.
If he has a view with 20 columns in it, and he selects a specific column from the view
in his query. Does the engine when accessing the view return all columns? or is it
smart enough to know to just retrive the one?
example:
create view test as
select a,b,c,d,e,f,g from testtable;
select a from test;
(does the engine retrieve b-g?)
Thanks
Dave
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | David Kerr <dmk(at)mr-paradox(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Under the hood of views |
Date: | 2009-08-13 16:28:01 |
Message-ID: | 4A843F11.5020805@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
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.
--
Richard Huxton
Archonet Ltd
From: | David Kerr <dmk(at)mr-paradox(dot)net> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Under the hood of views |
Date: | 2009-08-13 23:04:00 |
Message-ID: | 20090813230400.GA5860@mr-paradox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
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.
Perfect, thanks!
Dave
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.