Re: How number of columns affects performance - Mailing list pgsql-performance

From Ron Johnson
Subject Re: How number of columns affects performance
Date
Msg-id 1059765560.22384.67.camel@haggis
Whole thread Raw
In response to Re: How number of columns affects performance  (Josh Berkus <josh@agliodbs.com>)
Responses Re: How number of columns affects performance
List pgsql-performance
On Fri, 2003-08-01 at 12:44, Josh Berkus wrote:
> Francisco,
>
> > Yes all fields belong to the same entity. I used 100 as an example it may
> > be something like 60 to 80 fields (there are two tables in question). I
> > don't formally do 3rd normal form, but for the most part I do most of
> > the general concepts of normalization.
> >
> > > If not, then good design says to split the table.
>
> Actually, no, it doesn't.   If all 60-80 fields are unitary and required
> characteristics of the row-entity, normalization says keep them in one table.

You snipped out too much, because that's exactly what I said...
Another way of writing it: only split the table if some of the fields
are not unitary to the entity.

> The only time NF would recommend splitting the table is for fields which are
> frequenly NULL for reasons other than missing data entry.  For those, you'd
> create a child table.  Although while this is good 4NF, it's impractical in
> PostgreSQL, where queries with several LEFT OUTER JOINs tend to be very slow
> indeed.

Good to know.

> My attitude toward these normalization vs. performance issues is consistenly
> the same:  First, verify that you have a problem.   That is, build the
> database with everything in one table (or with child tables for Nullable
> fields, as above) and try to run your application.  If performance is
> appalling, *then* take denormalization steps to improve it.

The OP was not talking about denormalizing ...

It was: will vertically partitioning a table increase performance.
And the answer is "sometimes",

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: OSDL Database Test Suite 3 is available on PostgreSQL
Next
From: Josh Berkus
Date:
Subject: Re: How number of columns affects performance