Indexing Strategy - Mailing list pgsql-novice

From Matthew Engelbert
Subject Indexing Strategy
Date
Msg-id 20041208192145.95226.qmail@web60308.mail.yahoo.com
Whole thread Raw
Responses Re: Indexing Strategy
List pgsql-novice
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

pgsql-novice by date:

Previous
From: Sean Davis
Date:
Subject: Re: Slow delete
Next
From: Andrew Hammond
Date:
Subject: Re: Slow delete