Re: Proposition for better performance

On 03/27/2018 10:04 AM, hmidi slim wrote:
> the query that I used to fetch products was:
> select * from availability
> where ('27-03-2018' between start_date and end_date)
> and ('31-03-2018' between start_date and end_date);
>
> I added another column named during of type daterange and I created a
> gist index :
> create index idx on availability(during);

That creates a b-tree index.

Also, what did you set `during` to?

> select * from availability
> where during @> daterange(''27-03-2018', '31-03-2018');
>
> With a table of 15M rows I got an execution time of 1 minute.

What was the query plan? Did you confirm that it used the index?

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com