Indexing Strategy

From: Matthew Engelbert <mje1975(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Indexing Strategy
Date: 2004-12-08 19:21:45
Message-ID: 20041208192145.95226.qmail@web60308.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-sql

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Neil 2004-12-09 01:44:54 message type 0x44 - Again
Previous Message Neil 2004-12-08 06:07:14 Re: message type 0x44

Browse pgsql-sql by date

  From Date Subject
Next Message Kevin B. 2004-12-09 15:25:25 filtering
Previous Message Christoph Haller 2004-12-08 12:50:59 Unresolved external: tgetent