I update the example:
create table product_availabilities(product_id integer, start_date date, end_date date);
insert into product_availabilities(product_id, start_date, end_date)
select a, '2018-01-01', '2018-05-03' from generate_series(1,15000000) as a
alter table product_availabilities add column during daterange;
update product_availabilities set during = daterange(start_date, end_date);
CREATE INDEX idx_time ON product_availabilities USING gist(during);
explain analyze SELECT product_id, start_date, end_date, during
FROM product_availabilities
WHERE during @> daterange('2018-02-01', '2018-04-01')
Query plan:create table product_availabilities(product_id integer, start_date date, end_date date);
insert into product_availabilities(product_id, start_date, end_date)
select a, '2018-01-01', '2018-05-03' from generate_series(1,15000000) as a
alter table product_availabilities add column during daterange;
update product_availabilities set during = daterange(start_date, end_date);
CREATE INDEX idx_time ON product_availabilities USING gist(during);
explain analyze SELECT product_id, start_date, end_date, during
FROM product_availabilities
WHERE during @> daterange('2018-02-01', '2018-04-01')
Bitmap Heap Scan on product_availabilities (cost=33728.79..236086.04 rows=878500 width=26) (actual time=2775.058..5792.842 rows=15000000 loops=1)
Recheck Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
Heap Blocks: exact=31040 lossy=79255
-> Bitmap Index Scan on idx_time (cost=0.00..33509.17 rows=878500 width=0) (actual time=2767.262..2767.262 rows=15000000 loops=1)
Index Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
Planning time: 0.063 ms
Execution time: 6408.456 ms
SELECT product_id, start_date, end_date, during
FROM product_availabilities
WHERE during @> daterange('2018-02-01', '2018-04-01')
