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

On Tue, May 15, 2007 at 07:51:44AM +0200, Dawid Kuroczko wrote:
> On 5/15/07, Bill Moseley <moseley@hank.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.

Sorry, I don't mean to drag this thread out much longer.  But, I have
one more question regarding joins.

Say I have a customer table and an order table.  I want a list of all
order id's for a given customer.

    SELECT o.id
    FROM order o
    JOIN customer c on o.customer = c.id

Does that bring into memory all columns from both order and customer?
Maybe that's not a good example due to indexes.

See, I've seen this splitting of one-to-one tables a number of time
(such as the user and user_preferences example) and I'm not sure if
that's just poor schema design, premature optimization, or someone
making smart use of their knowledge of the internal workings of
Postgresql....



--
Bill Moseley
moseley@hank.org