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:
*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 msExecution time:
6408.456 ms SELECT product_id, start_date, end_date, during FROM
product_availabilities WHERE during @> daterange('2018-02-01',
'2018-04-01')*
returns query runtime
*: 2min*
