Thread: Which is better (more columns or rows) ?

Hi,
  I have recently tried some simple test on the postgresql 7.0 running
on a PIII 600MHz, 128 Mbytes RAM . I created a simple Visual Basic app
that query two tables.
The first one consist of 10 columns  and the other 30 columns. I
inserted about 250,000 records into each tables and then do a "vacuum"
on the database.
Next I query to select about 100,000 records. I repeated this query for
5 times and each time I will do a "vacuum".
Below is the average timing I get:-
For 10 columns table ~109s
For 30 columns table ~ 112s

Is it true that I can design database tables to have more columns
without performance degradation during query ?

Regards,
Thomas Wong

At 9:16 AM +0800 4/10/01, thomas wong wrote:
>Hi,
> I have recently tried some simple test on the postgresql 7.0
>running on a PIII 600MHz, 128 Mbytes RAM . I created a simple Visual
>Basic app that query two tables.
>The first one consist of 10 columns  and the other 30 columns. I
>inserted about 250,000 records into each tables and then do a
>"vacuum" on the database.
>Next I query to select about 100,000 records. I repeated this query
>for 5 times and each time I will do a "vacuum".
>Below is the average timing I get:-
>For 10 columns table ~109s
>For 30 columns table ~ 112s
>
>Is it true that I can design database tables to have more columns
>without performance degradation during query ?


If your data is such that you can just put everything in one table
with lots of columns, you're better off with a flatfile database.

The whole point of relational databases is the flexibility you get
from having normalized data, which in general means more tables with
fewer columns in each.