From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Matthew Engelbert <mje1975@yahoo.com>
Subject: Re: Indexing Strategy
Date: Fri, 10 Dec 2004 15:42:18 -0800
User-Agent: KMail/1.6.2
Cc: pgsql-novice@postgresql.org
References: <20041208192145.95226.qmail@web60308.mail.yahoo.com>
In-Reply-To: <20041208192145.95226.qmail@web60308.mail.yahoo.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200412101542.18353.josh@agliodbs.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