select records from postgres where timestamp is in certain range

Question

I have arrival column of type timestamp in table reservations ( I'm using postgres ). How would I select all dates within this year for example?

I know I could do something like this:

select * FROM reservations WHERE extract(year from arrival) = 2012; 

But I've ran analyze and it looks like it require a sequence scan. Is there a better option?

P.S. 1 Hmm. both ways seem to require seq. scan. But the one by wildplasser produces results faster - why?

cmm=# EXPLAIN ANALYZE select * FROM reservations WHERE extract(year from arrival) = 2010;                                                   QUERY PLAN                                                    ---------------------------------------------------------------------------------------------------------------  Seq Scan on vrreservations  (cost=0.00..165.78 rows=14 width=4960) (actual time=0.213..4.509 rows=49 loops=1)    Filter: (date_part('year'::text, arrival) = 2010::double precision)  Total runtime: 5.615 ms (3 rows)  cmm=# EXPLAIN ANALYZE SELECT * from reservations WHERE arrival > '2010-01-01 00:00:00' AND arrival < '2011-01-01 00:00:00';                                                                   QUERY PLAN                                                                    -----------------------------------------------------------------------------------------------------------------------------------------------  Seq Scan on reservations  (cost=0.00..165.78 rows=51 width=4960) (actual time=0.126..2.491 rows=49 loops=1)    Filter: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))  Total runtime: 3.144 ms (3 rows) 

** P.S. 2 - After I have created index on arrival column second way got even faster - since it looks like query uses index. Mkey - I guess I'll stik with this one. **

                                                                       QUERY PLAN                                                                         ---------------------------------------------------------------------------------------------------------------------------------------------------------  Bitmap Heap Scan on reservations  (cost=4.77..101.27 rows=51 width=4960) (actual time=0.359..0.791 rows=49 loops=1)    Recheck Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))    ->  Bitmap Index Scan on arrival_idx  (cost=0.00..4.76 rows=51 width=0) (actual time=0.177..0.177 rows=49 loops=1)          Index Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))  Total runtime: 1.265 ms 

Answer 1

SELECT *  FROM reservations  WHERE arrival >= '2012-01-01' AND arrival < '2013-01-01'    ; 

BTW if the distribution of values indicates that an index scan will not be the worth (for example if all the values are in 2012), the optimiser could still choose a full table scan. YMMV. Explain is your friend.

Answer 2

Another option to make PostgreSQL use an index for your original query, is to create an index on the expression you are using:

create index arrival_year on reservations ( extract(year from arrival) ); 

That will open PostgreSQL with the possibility to use an index for

select *  FROM reservations  WHERE extract(year from arrival) = 2012; 

Note that the expression in the index must be exactly the same expression as used in the where clause to make this work.