Re: Create index on the year of a date column

What if you create sql-function without arguments and use it to create
functional index ?

Oleg
On Thu, 5 Jun 2003, Nick Barr wrote:

> 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.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83