Re: general design question

On Fri, 19 Apr 2002, jtp wrote:

> One:  All their dynamic information can be rebuilt from other tables,
> but it will be called upon rather frequently, so the redundency so as to
> not have to rebuild on every call seems acceptable by me. (smack me if i'm
> wrong)

It's quite reasonable to keep a summary table of information for
fast reference. The only difficulty you have to deal with is how
you keep it up to date. (Update every time the summarized data
change? Update once an hour? Once a day? That kind of thing. It
depends on your application.)

> My basic question ends up being: does postgres handle
> sequntial scans across tables with fewer fields better?

Definitely. Given the same number of rows, a narrower table (fewer
columns, shorter data types, that kind of thing) will always be
scanned faster than a wider one simply because you need to read
less data from the disk. This is database-independent, in fact.

Since vacuuming also effectively involves a sequential scan, you'll
also vacuum faster on a narrower table. So it makes sense to separate
frequently updated data from less frequently updated data, and
vacuum the frequently updated table more often, I would think.

However, for tables that are already narrow, you may get little
performance gain, or in some cases performance may even get worse,
not to mention your data size blowing up bigger. Postgres has a
quite high per-tuple overhead (31 bytes or more) so splitting small
tables can actually cause growth and make things slower, if you
frequently access both tables.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC