> I am trying to create an index on the year of a date field, to speed up
> some queries. Table structure is as follows
[snip]
> ------------------------------------------------------------------------
> And I have tried the following to create the actual index
> ------------------------------------------------------------------------
> create index sm_item_cdates_idx ON sm_item (extract(year from
> item_created_date_start));
> ------------------------------------------------------------------------
> The response I get from psql is
> ------------------------------------------------------------------------
> sm_live=# create index sm_item_cdates_idx ON sm_item (extract(year from
> item_created_date_start));
> ERROR: parser: parse error at or near "(" at character 52
> ------------------------------------------------------------------------
I think this is a bug. Some functions/builtins can not be used in a functional index. date_part() does not work
either...
However, if you wrap it in another function it works like it should:
CREATE FUNCTION get_year (date) RETURNS double precision AS '
SELECT extract(year from $1)' LANGUAGE SQL IMMUTABLE;
CREATE INDEX sm_item_cdates_idx ON sm_item (get_year(item_created_date_start));
But I think it would be faster to simply put an index on the date, not the year part. A direct
index should be faster than a functional index.
> Am I doing anything blatantly wrong? Can I actually use the extract
> function for an index? Would I still get a speed improvement if I were
> to just index the whole of the field, rather than just the year?
> An example query that I have been running is:
> ------------------------------------------------------------------------
> SELECT item_id, item_created_date_start FROM sm_item WHERE
> extract(year FROM item_created_date_start) = 1685;
> ------------------------------------------------------------------------
> Nick Barr
I am not sure the index code is intelligent enought to realize that the index can be used if you do the "extract()"
thing,but it will work if you do it like this:
CREATE INDEX sm_item_cdates_idx ON sm_item (item_created_date_start);
SELECT item_id, item_created_date_start FROM sm_item
WHERE (item_created_date_start >= '1685-01-01' AND item_created_date_start <= '1685-12-31');
/Mattias
