From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Cc: Matthew Engelbert <mje1975@yahoo.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: Indexing Strategy
Date: Tue, 14 Dec 2004 18:29:57 -0800
Message-ID: <200412141829.57589.josh@agliodbs.com> (raw)
In-Reply-To: <20041211005702.75029.qmail@web60302.mail.yahoo.com>
References: <20041211005702.75029.qmail@web60302.mail.yahoo.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
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: <200412141829.57589.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