Nick Burrett <nick@dsvr.net> writes:
> The original table definition was exactly this:
> CREATE TABLE fiveminute ( server CHAR(32),
> stamp TIMESTAMP,
> bytesin BIGINT CHECK (bytesin >= 0),
> bytesout BIGINT CHECK (bytesout >= 0));
> CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);
> The database never survived operational use. The original import of
> data took so long (2+ days) that the re-design was done almost immediately.
It'd probably be better to load the initial data first and then build
the index; boosting sort_mem while building the index should be a help
too. But there's still something very fishy here. You said you had
1500 * 3000 = 4.5million rows in the tables. Even with the dumbest
approach to loading the data, I can't conceive of it taking 2 days.
Even on my oldest and slowest development machine, I can load a table
that size in about ten minutes, and create an index on it in another ten.
One thing that comes to mind is that what you eliminated appears to have
been an indexed char(32) column. We have seen reports of very slow
behavior on certain platforms if a non-C locale is used --- apparently
strcoll() is just horrendously slow in some implementations. It'd be
worth ensuring that your database is initdb'd in C locale. Also, is
there a good reason to pad every server name to 32 characters? Is the
32-character limit even real, or did you pluck that number from the air?
Usually I recommend type text (or equivalently, varchar with no specific
length limit) unless you have a clear application-driven need for a
specific length limit --- and even then it should be varchar(n) not
char(n).
regards, tom lane
