Query performance

From: hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query performance
Date: 2018-03-26 16:46:15
Message-ID: CAMsqVxtfpQceMBL99LP4Uit+0fguTH5rzCJycXOSnQP8hn2UEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2018-03-26 17:38:25 Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13
Previous Message Alvaro Herrera 2018-03-26 15:46:38 Re: [GENERAL] missing public on schema public