I'm having trouble correctly indexing a large table. The table will be
receiving about 30 million rows/day, and has the following definition:
CREATE TABLE quote (
symbol varchar (5),
price numeric (7,4),
time timestamp
);
I need to configure the indices so that I can minimize the time to request all
the records for a particular symbol, for a particular day, ordered by time.
I've tried clustering a single index based on symbol, then time, but this
operation takes much too long. I think this is because this operation would
have O(N^2) complexity(?). I think my next best option would be to cluster on
an index based on symbol, then have another index on time.
Does any one know of some resources on the web that discusses indexing
strategies ? Any help would be appreciated? Thanks.
- Matt