"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
