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(at)postgresql(dot)org)
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+