Re: [NOVICE] Indexing Strategy

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