From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | Greg Stark <gsstark(at)MIT(dot)EDU> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ShmemAlloc errors |
Date: | 2003-10-17 22:11:17 |
Message-ID: | 87y8vjzf96.fsf@stark.dyndns.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greg Stark <gsstark(at)MIT(dot)EDU> writes:
> > CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);
> >
> > It probably would have made sense to just have an index on the server column,
> > but I can't remember why (decision was made Nov-2000). I suspect that is the
> > cause of the index bloat.
There's also another option.
Create a single normalized table, but create separate partial indexes for each
server.
Ie:
create index idx_server_1 (fiveminute) on tab where serverid = 1;
create index idx_server_2 (fiveminute) on tab where serverid = 2;
create index idx_server_3 (fiveminute) on tab where serverid = 3;
...
This is a management headache, creating a new index every time you add a new
server. And the performance of the optimizer when there are thousands of
indexes to choose from would be, well, it would be an interesting experiment
:)
But it should be faster than having a single two-column index, largely because
it has half the data to store and read through.
This assumes all queries on the table have a clause like "where serverid = n".
It won't work for "where serverid between n and m" or for things like "order
by serverid, fiveminute"
Also, incidentally, do you have a lot of people downloading more than 4Gb in
five minutes? Maybe just regular integers would be big enough? They would be
faster.
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2003-10-17 22:38:59 | using cygwin for postgres |
Previous Message | elein | 2003-10-17 21:24:44 | Re: [GENERAL] Writers Wanted |