Re: ShmemAlloc errors

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