From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: Re: Indexing Strategy
Date: Tue, 14 Dec 2004 18:29:57 -0800
User-Agent: KMail/1.6.2
Cc: Matthew Engelbert <mje1975@yahoo.com>,
	pgsql-novice@postgresql.org
References: <20041211005702.75029.qmail@web60302.mail.yahoo.com>
In-Reply-To: <20041211005702.75029.qmail@web60302.mail.yahoo.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200412141829.57589.josh@agliodbs.com>

Matthew,

> select * from quote where sybmol = 'MSFT' and extract(doy from time) = 340
> order by time

Aha, that should be easy.

CREATE INDEX quote_doy_symbol ON quote ( (extract(doy from "time")), symbol );

The reason that I'm putting the DOY first is because it's liable to be 
slightly more selective; 365 values as opposed to 150.  This is just to help 
the planner realize that the index is useful.

I doubt it's worth indexing the order by within that, since the query should 
produce a fairly small amount of rows

Of course, using extract doesn't gain you anything, and in fact adds 
significant CPU overhead to both the query and the index.   So you'd be 
slightly better off doing:

SELECT * FROM quote
WHERE symbol = 'MSFT'
	AND "time" BETWEEN '2004-10-11' AND '2004-10-12';

This will also allow you to create a single index on:

CREATE INDEX quote_time_symbol ON quote("time", symbol);

... which will be useful for any time-based query, not just one on days.  And 
it would be potentially useful for time-based queries which don't include a 
symbol.

Further, if your queries are *always* structured like the above (time + 
symbol) I'd suggest CLUSTERing on the index.

Also, I hope that you didn't really name a column "time".

-- 
Josh Berkus
Aglio Database Solutions
San Francisco