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?
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 |