I want to compare the performance of some queries so I created those tables:
*CREATE TABLE reservation1 (room int, name varchar(255), during
daterange);CREATE TABLE reservation2 (room int, name varchar(255),
start_date date, end_date date);*
then:
*INSERT INTO public.reservation1(name, room, during) select
concat('room ', a), a, '[2010-01-01, 2010-04-02)' from
generate_series(1,10000000) as a;INSERT INTO public.reservation2(name,
room, start_date, end_date) select concat('room ', a), a, '2010-01-01',
'2010-04-02' from generate_series(1,10000000) as a;*
I create an index for during column:
*CREATE INDEX reservation1_idx ON reservation1 USING GIST (during);*
I'm using the operator contains (@>) and overlaps (&&):
*select * from reservation1 where during @>
'[2010-02-15,2010-03-02)';select * from reservation1 where during &&
'[2010-02-15,2010-03-02)'; *
And I got such result time:
* 25s*
However when I use this query:
*select * from reservation2 where ('2010-02-15' between start_date and
end_date) and ('2010-03-02' between start_date and end_date);*
The result time was:
* 9s.*
I understand that the index does not have any effect when the amount of
fetched data was huge and the query planner used the seq scan method. I
want to know if it is not recommended to use rang types and operator to get
good performance or should I add something to the queries to be faster?
