Re: [SQL] [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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] parse error at or near "(" -- Huh???
On Sun, 12 Dec 2004 19:18:00 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Thu, Dec 09, 2004 at 03:19:56PM -0500, Alex Beamish wrote:
>
> > I'm getting a frustrating error
> >
> > ERROR: parser: parse error at or near "(" at character 201
> >
> > in a CREATE TABLE statement from an SQL script that I'm running from
> > within a Perl script. When I run the same script from the command
> > line, either as a regular user or as root, it works fine.
>
> Your SQL statements ran fine for me, so please show us a small but
> complete Perl script that duplicates the problem.
Michael, Tom,
Thank you both for your responses .. I discovered that, while I was
editting what I thought was the running script, I was actually running
an earlier (wrong) version extracted a moment earlier from the version
control system, in a paralell directory. This became obvious when I
deleted the offending line and the error remained exactly the same.
However, I am intrigued to find out about the character offset that
appears in the error. How is that calculated? This would be useful
diagnostic information to have in the future.
Thanks!
Alex
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
