Re: Create index on the year of a date column

"Nick Barr" <nick(dot)barr(at)webbased(dot)co(dot)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