Two efficiency questions - clustering and ints

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Two efficiency questions - clustering and ints
Date: 2006-10-05 17:33:03
Message-ID: 5E80CBB0-2727-47CA-9212-163117DB1C2F@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a good-size DB (some tables approaching 100M rows), with
essentially static data.

Should I always cluster the tables? That is, even if no column jumps
out as being involved in most queries, should I pick a likely one and
cluster on it? (Of course, this assumes that doing so won't cause
bad correlation with any other oft-used column.)

Another question, about integer types - if no cross-type coercion is
involved, is there any reason not to choose the smallest int type
that will fit my data? In particular, I have a column of small-
integer ratings with, say, values in [1, 10]. If I'm only comparing
within such ratings, and possibly computing floating point averages,
etc., what are the good and bad points of using, say, SMALLINT? What
about NUMERIC(1) or (2)?

Thanks in advance for the usual brilliant replies!

- John D. Burger
MITRE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bricklen Anderson 2006-10-05 17:33:53 Re: trouble with setof record return
Previous Message John Sidney-Woollett 2006-10-05 17:30:43 Re: Storing images in PostgreSQL databases (again)