On Fri, 17 Oct 2003 15:47:10 +0100, Nick Burrett <[EMAIL PROTECTED]>
wrote:
>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);
Making this
CREATE TABLE server (
id int NOT NULL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE fiveminute (
serverid int NOT NULL REFERENCES server,
stamp timestamp NOT NULL,
bytesin bigint CHECK (bytesin >= 0),
bytesout bigint CHECK (bytesout >= 0),
PRIMARY KEY (serverid, stamp)
);
should give you a much smaller index on fiveminute.
If you have to load lots of data initially, better create the tables
without primary and foreign keys, import data and then
ALTER TABLE server ADD PRIMARY KEY (id);
ALTER TABLE fiveminute ADD PRIMARY KEY (serverid, stamp);
ALTER TABLE fiveminute
ADD FOREIGN KEY (serverid) REFERENCES server;
Servus
Manfred
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
