From: Matthew Engelbert <mje1975@yahoo.com>
To: pgsql-novice@postgresql.org
Subject: Indexing Strategy
Date: Wed, 8 Dec 2004 11:21:45 -0800 (PST)
Message-ID: <20041208192145.95226.qmail@web60308.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mje1975@yahoo.com, pgsql-novice@postgresql.org
Subject: Re: Indexing Strategy
In-Reply-To: <20041208192145.95226.qmail@web60308.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox