Hi,
I'm creating this table :
create table availability(product_id integer, product_name varchar(255), during daterange);
create index time_idx on availability using gist(during);
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
When I execute this query :
select * from availability where during @> daterange('2018-03-27', '2018-03-31');
I got 2s as query runtime. I used a cluster:
cluster availability using time_idx
The time became between 200ms and 300ms. Based on the doc, Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered.SO if a new row was add I have to run the cluster manually. Is there any proposition when to run the cluster when an update was made, because this operation took a time to accomplish.Thus the database was locked and it's not possible for other operation such as read or write to be executed.Any propositions to use cluster or something else for better performance?