From: Josh Berkus <josh@agliodbs.com>
To: Matthew Engelbert <mje1975@yahoo.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: Indexing Strategy
Date: Fri, 10 Dec 2004 15:42:18 -0800
Message-ID: <200412101542.18353.josh@agliodbs.com> (raw)
In-Reply-To: <20041208192145.95226.qmail@web60308.mail.yahoo.com>
References: <20041208192145.95226.qmail@web60308.mail.yahoo.com>
Matthew,
> 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.
Please post the query you're using. How are you selecting the day? What
version of PostgreSQL are you using?
> 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(?).
Nope, just because it's a very large table and you're probably swapping. Try
increasing sort_mem dramatically, like half your RAM.
> I think my next best option would be to
> cluster on an index based on symbol, then have another index on time.
Not that either. You need a multicolumn index. When you answer the above
quesitons, I'll explain it.
> Does any one know of some resources on the web that discusses indexing
> strategies ? Any help would be appreciated? Thanks.
Not that covers your question. I'm writing a book which covers this right
now, but it won't be published until January 2006.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, mje1975@yahoo.com, pgsql-novice@postgresql.org
Subject: Re: Indexing Strategy
In-Reply-To: <200412101542.18353.josh@agliodbs.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