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