pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctional index problems. (Was: Many joins: monthly summaries S-L--O--W)
9+ messages / 3 participants
[nested] [flat]
* Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 17:07 Michael Glaesmann <grzm@myrealbox.com>
0 siblings, 2 replies; 9+ messages in thread
From: Michael Glaesmann @ 2003-10-22 17:07 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
I've implemented the aggregate grid method, building a "xtab" table as
outlined. The orders relation has about 300K records, which isn't that
big from the numbers thrown around on these lists. The basic query
(just the monthly columns, none of the inventory mess) took about 2
minutes. That's going through the 300K records and creating monthly
totals. Something tells me there's room for improvement, so I'll keep
trying. (Adding the inventory joins slows it back down to about 10
minutes, so there's a lot of room for improvement there, but one thing
at a time, right?)
On Wednesday, Oct 22, 2003, at 01:48 Asia/Tokyo, Josh Berkus wrote:
> I'd suggest instead that you use the "aggregate grid" method:
<snip/>
> This is much, much faster than the outer join method for large numbers
> of
> columns. For better performance, make an index on extract(month from
> sales_date).
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));
which gave me
ERROR: parser: parse error at or near "(" at character 61
I also tried
CREATE INDEX dborders_extract_month_idx ON dborders EXTRACT(MONTH from
date);
ERROR: parser: parse error at or near "EXTRACT" at character 53
and just for good measure, not that I thought it work
CREATE INDEX dborders_extract_month_idx on dborders EXTRACT(MONTH from
(date));
ERROR: parser: parse error at or near "EXTRACT" at character 53
What am I missing from the CREATE statement? It seems in line with the
documentation for CREATE INDEX:
CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_method ] (
func_name (column [, ... ]) [ ops_name ] )
[ WHERE predicate ]
and the example given in 8.5 Functional Indexes of the User's Guide:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
I know this isn't the solution to all my problems, but I am a bit
curious why my CREATE INDEX isn't working.
Further debugging attempts:
Try a different fuction.
CREATE INDEX dborders_date_trunc_idx on dborders
(date_trunc('month',date));
This failed with
ERROR: parser: parse error at or near "'month'" at character 62
I even renamed the 'date' column (type timestamp with timezone) to
'datetime' on the off chance that using and SQL key word (though not a
PostgreSQL key word) was causing some problem. Same errors.
What is that simple thing I'm overlooking? Any ideas what else I should
check? (I did a search on 'functional index' in the list archives but
kept getting timed out :(
Michael
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 17:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-10-22 17:44 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; +Cc: pgsql-novice@postgresql.org
Michael Glaesmann <grzm@myrealbox.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 17:53 Josh Berkus <josh@agliodbs.com>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-10-22 17:53 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; pgsql-novice@postgresql.org
Michael,
> What is that simple thing I'm overlooking? Any ideas what else I should
> check? (I did a search on 'functional index' in the list archives but
> kept getting timed out :(
Oh, sorry. There's an implementation issue with funcional indexes, where they
can't take parameters other than column names. So you need to do:
CREATE FUNCTION get_month (
TIMESTAMPTZ ) RETURNS INTEGER AS
' SELECT EXTRACT(MONTH from $1); '
LANGUAGE sql IMMUTABLE STRICT;
Then do
CREATE INDEX dborders_date_trunc_idx on dborders
(get_month(date));
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 18:34 Michael Glaesmann <grzm@myrealbox.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Michael Glaesmann @ 2003-10-22 18:34 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
On Thursday, Oct 23, 2003, at 02:44 Asia/Tokyo, Tom Lane wrote:
> Michael Glaesmann <grzm@myrealbox.com> writes:
>> 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.
On Thursday, Oct 23, 2003, at 02:53 Asia/Tokyo, Josh Berkus wrote:
> Oh, sorry. There's an implementation issue with funcional indexes,
> where they
> can't take parameters other than column names. So you need to do:
>
> CREATE FUNCTION get_month (
> TIMESTAMPTZ ) RETURNS INTEGER AS
> ' SELECT EXTRACT(MONTH from $1); '
> LANGUAGE sql IMMUTABLE STRICT;
Thanks, Tom and Josh! Added a type cast of the extract (which returns a
double precision) and it's all good.
Tom commented:
> 7.4 is more flexible though --- it will take the above as long as you
> put an extra set of parentheses in there...
I took a gander at the documentation for 7.4beta. I can tell it's been
reorganized. There's no longer a specific section on functional indexes
that I can see, though there is mention of it in the SQL CREATE INDEX
entry
<http://developer.postgresql.org/docs/postgres/sql-createindex.html;
The 7.3.2 documents I have say that there cannot be multicolumn
functional indexes, though there's no mention of this in the new
documentation. Does this mean this proscription has been lifted?
Thanks again for your help!
Michael
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 19:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-10-22 19:17 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; +Cc: pgsql-novice@postgresql.org
Michael Glaesmann <grzm@myrealbox.com> writes:
> On Thursday, Oct 23, 2003, at 02:44 Asia/Tokyo, Tom Lane wrote:
>> 7.4 is more flexible though --- it will take the above as long as you
>> put an extra set of parentheses in there...
> I took a gander at the documentation for 7.4beta. I can tell it's been
> reorganized. There's no longer a specific section on functional indexes
> that I can see, though there is mention of it in the SQL CREATE INDEX
> entry
> <http://developer.postgresql.org/docs/postgres/sql-createindex.html;
> The 7.3.2 documents I have say that there cannot be multicolumn
> functional indexes, though there's no mention of this in the new
> documentation. Does this mean this proscription has been lifted?
Yes. Any column of an index can now be an expression; the former
functional-index capability is now just a special case of "expressional
indexes". For syntactic reasons we had to require an extra pair of
parens around expressions in the CREATE INDEX statement --- although
it proved possible to not require these when the expression looks like
a standard-syntax function call, thus providing backwards compatibility
with the old functional-index syntax. See
http://developer.postgresql.org/docs/postgres/indexes-expressional.html
as well as the CREATE INDEX reference page.
I'm not particularly happy with the phrase "expressional index", and
would like to think of something else to use before the 7.4 docs go
final. Any ideas?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-22 19:20 Josh Berkus <josh@agliodbs.com>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-10-22 19:20 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; pgsql-novice@postgresql.org
Michael,
> <http://developer.postgresql.org/docs/postgres/sql-createindex.html;
> The 7.3.2 documents I have say that there cannot be multicolumn
> functional indexes, though there's no mention of this in the new
> documentation. Does this mean this proscription has been lifted?
Yes.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-23 00:32 Michael Glaesmann <grzm@myrealbox.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Michael Glaesmann @ 2003-10-23 00:32 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
On Thursday, Oct 23, 2003, at 04:17 Asia/Tokyo, Tom Lane wrote:
> I'm not particularly happy with the phrase "expressional index", and
> would like to think of something else to use before the 7.4 docs go
> final. Any ideas?
Though it might be nice to be creative and name it something like
"Bill", for a defined language like SQL I think it's best to work
within the framework already used to describe the language. Looking in
the PostgreSQL docs (which I'm sure you've done), I find this
definition of "value expression"
A value expression is one of the following:
- A constant or literal value; see Section 1.1.2 .
- A column reference.
- A positional parameter reference, in the body of a function
declaration.
- An operator invocation.
- A function call.
- An aggregate expression.
- A type cast.
- A scalar subquery.
- Another value expression in parentheses, useful to group
subexpressions and override precedence.
Preceding this is a paragraph mentioning that value expressions are
also called "scalar expressions" or just "expressions". I was surprised
to find column references are included in this list, but thinking about
it, a column has a scalar value, which is what any function or operator
returns as well. According to the above description of "value
expression", a normal column index is just a special case of the more
general "expression" index.
(Actually, I'm a bit confused with the definition of value expression.
Does column reference here mean value in the same row (and same table)?
That's the only way I can see getting a scalar value. If it means
reference in the constraint meaning, it's a set of allowed values,
rather than a specific one, which definitely wouldn't work for an
index. Perhaps the use of the word "reference" here is inappropriate,
though I haven't looked through the rest of the documentation to see
how the term "column reference" is used. My bad, I know. I'll
definitely accept criticism for not doing my homework thoroughly.)
To me, an operator is just a special class of function that doesn't use
the more general func(arg [, arg]) construction. 2 + 2 could just as
easily be add(2,2) — and maybe it is already defined this way as well.
I haven't checked.
However, I can see that it might be useful to distinguish between
functions and column references. One term I thought about was "derived
index". Another was "evaluated index". But both of these terms seem to
introduce unnecessary language.
My final thought would be to continue using "functional index", noting
that operators are a special class of function. Out of the above
description of "value expression", I believe only functions and
operators are allowed as expressions in the CREATE INDEX syntax,
correct? (Besides referring to a column.)
What do you think?
Michael
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [NOVICE] Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-23 00:55 Josh Berkus <josh@agliodbs.com>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-10-23 00:55 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; pgsql-sql
Michael,
> My final thought would be to continue using "functional index", noting
> that operators are a special class of function. Out of the above
> description of "value expression", I believe only functions and
> operators are allowed as expressions in the CREATE INDEX syntax,
> correct? (Besides referring to a column.)
Well argued. The problem is Peter's point, which you weren't cc'd on:
>At least it's better than "functional index", because I had always
>wondered where the dysfunctional indexes went. :)
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
@ 2003-10-23 02:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2003-10-23 02:53 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; +Cc: pgsql-novice@postgresql.org
Michael Glaesmann <grzm@myrealbox.com> writes:
> My final thought would be to continue using "functional index", noting
> that operators are a special class of function. Out of the above
> description of "value expression", I believe only functions and
> operators are allowed as expressions in the CREATE INDEX syntax,
> correct? (Besides referring to a column.)
Actually, you can use any scalar-valued expression that does not contain
a sub-SELECT or aggregate function. (Aggregates would be meaningless
--- what are you aggregating over? The prohibition of sub-SELECTs is
partly because we don't do planning for index expressions, which could
be fixed if anyone were sufficiently motivated, and partly because it
seems quite unlikely that the result of a SELECT could reasonably be
considered immutable. We do *not* want to get into updating indexes for
reasons other than updates of the indexed row.)
I see your point that "functional index" is still a valid description,
but I'm inclined to bow to Peter's position that it has another meaning
that could distract people. Presently I'm going to go with "expression
index" unless someone can provide a better choice.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-10-23 02:53 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-22 17:07 Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) Michael Glaesmann <grzm@myrealbox.com>
2003-10-22 17:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-22 18:34 ` Michael Glaesmann <grzm@myrealbox.com>
2003-10-22 19:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-23 00:32 ` Michael Glaesmann <grzm@myrealbox.com>
2003-10-23 00:55 ` Josh Berkus <josh@agliodbs.com>
2003-10-23 02:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-22 19:20 ` Josh Berkus <josh@agliodbs.com>
2003-10-22 17:53 ` Josh Berkus <josh@agliodbs.com>
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