Re: Create index on the year of a date column

Hi,

Thanks guys for all the responses, they are really helpful. To
summarise:

1. Use an index on the entire date column and use ranges to make use of
the index.
2. Create a dummy function that wraps the extract function call, and use
this as the index.
3. Create another column, for the year, filled in automatically using a
trigger and index that.
4. Wait until 7.4 which will allow me to do this.

Brilliant. I suspect I will probably choose the first one for now, and
see how that fares. I suspect, as per normal with Postgres, it will
absolutely fly ;-). However, if performance becomes more of an issue,
which it probably wont, then 7.4 will be out by then so I will use that.

Thanks again for your time and responses. Much appreciated

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.



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 06 June 2003 06:29
> To: Nick Barr
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Create index on the year of a date column
>
> "Nick Barr" <nick.barr@webbased.co.uk> writes:
> > SELECT item_id, item_created_date_start FROM sm_item WHERE
> > extract(year FROM item_created_date_start) = 1685;
>
> As of 7.4 you will actually be able to build an index on an expression
> like that:
>
> regression=# CREATE TABLE "sm_item" ("item_created_date_start" date);
> CREATE TABLE
> regression=# create index sm_item_cdates_idx ON sm_item ((extract(year
> from item_created_date_start)));
> CREATE INDEX
> regression=# explain SELECT * FROM sm_item WHERE
> regression-# extract(year FROM item_created_date_start) = 1685;
>                                                         QUERY PLAN
>
------------------------------------------------------------------------
--
> ------------------------------------------------
>  Index Scan using sm_item_cdates_idx on sm_item  (cost=0.00..17.09
rows=5
> width=4)
>    Index Cond: (date_part('year'::text,
> (item_created_date_start)::timestamp without time zone) = 1685::double
> precision)
> (2 rows)
>
> But in current releases the best bet is a range inquiry using a plain
> index:
>
> regression=# create index fooi on sm_item (item_created_date_start);
> CREATE INDEX
> regression=# explain SELECT * FROM sm_item WHERE
> regression-# item_created_date_start BETWEEN '1685-01-01' AND
'1685-12-
> 31';
>                                                      QUERY PLAN
>
------------------------------------------------------------------------
--
> -------------------------------------------
>  Index Scan using fooi on sm_item  (cost=0.00..17.08 rows=5 width=4)
>    Index Cond: ((item_created_date_start >= '1685-01-01'::date) AND
> (item_created_date_start <= '1685-12-31'::date))
> (2 rows)
>
>             regards, tom lane