From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Glaesmann <grzm(at)myrealbox(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
Date: | 2003-10-22 17:44:50 |
Message-ID: | 17556.1066844690@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-sql |
Michael Glaesmann <grzm(at)myrealbox(dot)com> writes:
> Searching for ways to improve performance, I tried to create a index on
> the extract function, but for some reason I couldn't get it to work.
> Following the documentation for CREATE INDEX and EXTRACT, I tried
> CREATE INDEX dborders_extract_month_idx ON dborders (EXTRACT(MONTH from
> date));
> ERROR: parser: parse error at or near "(" at character 61
You can't do that in pre-7.4 releases; the syntax of a functional index
can only be "ON table (func(col1,col2,...))" --- that is, a simple,
standard-notation function applied to one or more columns of the table.
So to do this, you'd need to create an intermediate function along
the lines of "month_trunc(date)"; and you'd have to use it in your
queries as well as in the index definition.
7.4 is more flexible though --- it will take the above as long as you
put an extra set of parentheses in there...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-10-22 17:53:56 | Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
Previous Message | Michael Glaesmann | 2003-10-22 17:07:53 | Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-10-22 17:53:56 | Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
Previous Message | Michael Glaesmann | 2003-10-22 17:07:53 | Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |