Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) - Mailing list pgsql-novice

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:


<excerpt>I'd suggest instead that you use the "aggregate grid" method:

</excerpt><<snip/>

<excerpt>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).

</excerpt>

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:

<fontfamily><param>Courier</param>CREATE [ UNIQUE ] INDEX
<italic>index_name </italic>ON <italic>table </italic>[ USING
<italic>acc_method </italic>] ( <italic>func_name
</italic>(<italic>column </italic>[, ... ]) [ <italic>ops_name
</italic>] )

    [ WHERE <italic>predicate </italic>]</fontfamily>


and the example given in 8.5 Functional Indexes of the User's Guide:

<fontfamily><param>Courier</param>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


</fontfamily>
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