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

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