From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | PostgreSQL performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: How number of columns affects performance |
Date: | 2003-08-01 16:34:48 |
Message-ID: | 1059755687.7508.638.camel@haggis |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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 |
+-----------------------------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-08-01 16:51:33 | Re: Views With Unions |
Previous Message | Francisco Reyes | 2003-08-01 16:08:18 | How number of columns affects performance |