Re: How number of columns affects performance

On Fri, 2003-08-01 at 11:08, Francisco Reyes wrote:
> If a table which will be heavily used has numerous fields, yet only a
> handfull of them will be used heavily, would it make sense performance wise to split it?
>
> Example
> Table 1
> Field 1
> ....
> Field 100
>
> Table 2
> References Field 1 of table1
> .....
>
> Table n
> References Field 1 of table 1
>
> So table 1 basically will be referenced by many tables and most of the
> time only a handfull of fields  of table 1 are needed. Don't have exact
> numbers, but let's say that more than 60% of queries to table 1 queries
> only use 20 fields or less.
>
> If I split Table 1 then the second table will basically be a 1 to 1 to
> Table 1.

Do all 100 fields *really* all refer to the same *one* entity,
with no repeating values, etc?
If not, then good design says to split the table.

Also, if it's a high-activity table, but you only rarely need fields
60-90, then splitting them out to their own table might be useful
(especially if some of those fields are large *CHAR or TEXT).

> I have this simmilar scenario for two tables. One is close to 1 Million
> records and the other is about 300,000 records.
>
> Programming wise it is much easier to only have one table, but I am just
> concerned about performance.
>
> Most access to these tables will be indexed with some occassional
> sequential scans. Number of concurrent users now is probably 10 or less.
> Expect to grow to 20+ concurrent connections. Will this be more of an
> issue if I had hundreds/thousands of users?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
+-----------------------------------------------------------------+
| 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                                                      |
+-----------------------------------------------------------------+