Create index on the year of a date column - Mailing list pgsql-general

Hi,

I am trying to create an index on the year of a date field, to speed up
some queries. Table structure is as follows

------------------------------------------------------------------------
----
CREATE SEQUENCE "sm_item_id_seq" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;
CREATE TABLE "sm_item" (
    "item_id" int4 DEFAULT nextval('"sm_item_id_seq"'::text) NOT
NULL,
    "item_created_date_start" date,
    CONSTRAINT "sm_item_pkey" PRIMARY KEY ("item_id")
) WITHOUT OIDS;
------------------------------------------------------------------------
----

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

This relates to the brackets surrounding the "year from
item_created_date_start" bit.

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;
------------------------------------------------------------------------
----

Which of course has been using a seq scan, as there is absolutely no
index on this column as yet.

Kind Regards,

Nick Barr
WebBased Ltd.

This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.