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 <http://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 <http://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 <http://r.id> = p.id <http://p.id> and r.i = n.i;*
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?
