Query performance with cluster

From: hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query performance with cluster
Date: 2018-03-28 16:09:03
Message-ID: CAMsqVxuk2Q5AvL-W_Rm2YDVX48YWi1eU_C8ib+v13+AhuEiHkA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Browse pgsql-general by date

  From Date Subject
Next Message Moreno Andreo 2018-03-28 16:24:02 Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
Previous Message Adrian Klaver 2018-03-28 13:16:28 Re: Problem with postgreSQL