Re: Proposition for better performance

(Including the list....)

On 03/27/2018 10:49 AM, hmidi slim wrote:
> Sorry I didn't copy it very well:
> create index idx on availability using gist(during);
>
> and during = daterange(start_date,end_date)
>
> And the query plan used was seq scan not index scan.

It sounds like there must be some important information missing. Here is
my attempt to mock up some fake data:

insert into availability
select p.id, concat('prod ', p.id::text), daterange((now() +
concat(r.v, ' days')::interval)::date, (now() + concat((r.v + 1 +
random() * 21)::int, ' days')::interval)::date)
from (select * from generate_series(1, 1000000)) p(id)
cross join (select * from generate_series(1, 15)) n(i)
join lateral (
select p2.id, n2.i, (random() * 600 - 300)::int AS v
from generate_series(1, 1000000) p2(id),
generate_series(1, 15) n2(i)
) r
on r.id = p.id and r.i = n.i
;

Then

explain select * from availability where during @>
daterange('2018-03-27', '2018-03-31');
QUERY PLAN

-----------------------------------------------------------------------------
Index Scan using idx on availability (cost=0.39..1644.41 rows=1
width=552)
Index Cond: (during @> '[2018-03-27,2018-03-31)'::daterange)
(2 rows)

Running that query for real I get:

...
(215044 rows)

Time: 1450.099 ms (00:01.450)

So if the index isn't being used you'll have to do some digging to find
out why.

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