pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Glaesmann <grzm@myrealbox.com>
To: pgsql-novice@postgresql.org
Subject: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
Date: Thu, 23 Oct 2003 02:07:53 +0900
Message-ID: <461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.com> (raw)
In-Reply-To: <200310210948.19592.josh@agliodbs.com>

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

view thread (9+ messages)  latest in thread

Message-ID: <461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.com>
Permalink:  ../461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.com/
Also on:    postgresql.org/message-id/461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: grzm@myrealbox.com, pgsql-novice@postgresql.org
  Subject: Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
  In-Reply-To: <461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.com>
  References: <200310210948.19592.josh@agliodbs.com> <461424BA-04B2-11D8-81AE-0005029FC1A7@myrealbox.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