Re: Performance issues of one vs. two split tables.

On 5/15/07, Bill Moseley <moseley(at)hank(dot)org> wrote:
> On Tue, May 15, 2007 at 06:33:26AM +0200, Dawid Kuroczko wrote:
> > Well, views are not going to help with memory consumption here.
> > It is the table contents that gets cached in buffer cache, not the
> > views contents. So if you have a view which returns only one
> > column from 15-column table, you will be caching that 15-column
> > data nonetheless. View, as the name states, is converted into
> > a select on a real table.
>
> Are you saying that in Postgresql:
>
> select first_name, last_name from user_table;
>
> uses the same memory as this?
>
> select first_name, last_name,
> passowrd, email,
> [10 other columns]
> from user_table;

Yes. You read whole page (8KB) into buffer_cache,
then extract these columns from these buffer. From the
buffer cache point of view, whole tuple is contained in the
cache.

Say, if you first SELECT fname, lname FROM user_table;
and then you issue SELECT * FROM user_table; -- the
second select will be returned from buffer cache -- since
all rows are already in the cache.

Having seperate caches for possible SELECT [column list]
would be well, not quite efficient.

Now, select fname,lname will take less private memory,
but this memory will be freed as soon as the query finishes,
but this won't help our cache much.

Regards,
Dawid