Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dDl7P-00053r-ID for pgsql-performance@arkaria.postgresql.org; Thu, 25 May 2017 05:15:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dDl7O-0003le-HN for pgsql-performance@arkaria.postgresql.org; Thu, 25 May 2017 05:15:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dDl5c-0000bX-L2 for pgsql-performance@postgresql.org; Thu, 25 May 2017 05:13:36 +0000 Received: from mxgate01.infotech-enterprises.com ([110.76.168.168] helo=mxgate01.cyient.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dDl5X-00082D-Bc for pgsql-performance@postgresql.org; Thu, 25 May 2017 05:13:35 +0000 X-IronPort-AV: E=Sophos;i="5.38,389,1491244200"; d="scan'208,217";a="73429062" Received: from unknown (HELO CYINMBX1.CORP.CYIENT.COM) ([172.17.4.161]) by mxgate01.cyient.com with ESMTP/TLS/AES256-SHA256; 25 May 2017 10:42:11 +0530 Received: from CYINMBX4.CORP.CYIENT.COM (172.17.4.164) by CYINMBX1.CORP.CYIENT.COM (172.17.4.161) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.27; Thu, 25 May 2017 10:43:26 +0530 Received: from CYINMBX4.CORP.CYIENT.COM ([fe80::596e:284a:a6f6:e6d4]) by CYINMBX4.CORP.CYIENT.COM ([fe80::596e:284a:a6f6:e6d4%19]) with mapi id 15.01.0544.027; Thu, 25 May 2017 10:43:26 +0530 From: Daulat Ram To: "pgsql-performance@postgresql.org" Subject: Query is very much slow Thread-Topic: Query is very much slow Thread-Index: AdLVFZWjh28vc6b1Qz6qYojid0Pz4Q== Date: Thu, 25 May 2017 05:13:26 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.17.4.81] Content-Type: multipart/alternative; boundary="_000_e226d5c404cf4cf69ef39354aaab2de0cyientcom_" MIME-Version: 1.0 X-Pg-Spam-Score: -3.5 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_e226d5c404cf4cf69ef39354aaab2de0cyientcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi team, We are getting very slow response of this query. SELECT date_trunc('day', insert_time),workflow.project.project_name, workflow.tool_performance.project_id,workflow.tool_performance.user_id,work= flow.tool_performance.step_id,count(*), round(sum(execution_time)/1000) as Sum_time_sec,round(((round(sum(execution= _time)/1000))/60)/count(*),2) as Efficency_Min,round (((round(sum(execution_time)/1000)))/count(*),2) as Efficency_sec FROM workflow.project,workflow.tool_performance,workflow.e= vidence_to_do WHERE workflow.project .project_id =3D workflow.tool_performance.project_id AND insert_time >'2017= -05-01' AND insert_time <'2017-05-02' AND workflow.evidence_to_do.status_id in (15100,15150,15200,15300,15400,15500) = Group BY date_trunc('day', insert_time),workflow.project.project_name, workflow.tool_performance.project_id,workflow.tool_performance.user_id,work= flow.tool_performance.step_id ORDER BY workflow.tool_performance.project_id,workflow.project.project_name, workflow.tool_performance.step_id; The following indexes are created on project & evidence_to_do table. "CREATE INDEX project_id_idx ON workflow.project USING btree (project_id)" "CREATE INDEX evidence_to_do_status_id_index ON workflow.evidence_to_do USI= NG btree (status_id)" Explain plan of the Query is: "GroupAggregate (cost=3D18675703613.60..20443753075.67 rows=3D6689718 widt= h=3D69)" " -> Sort (cost=3D18675703613.60..18823015982.33 rows=3D58924947492 widt= h=3D69)" " Sort Key: tool_performance.project_id, project.project_name, tool_= performance.step_id, (date_trunc('day'::text, tool_performance.insert_time)= ), tool_performance.user_id" " -> Nested Loop (cost=3D2.42..884042104.67 rows=3D58924947492 wid= th=3D69)" " -> Seq Scan on evidence_to_do (cost=3D0.00..118722.17 rows= =3D554922 width=3D0)" " Filter: (status_id =3D ANY ('{15100,15150,15200,15300,= 15400,15500}'::bigint[]))" " -> Materialize (cost=3D2.42..49435.58 rows=3D106186 width= =3D69)" " -> Hash Join (cost=3D2.42..48904.65 rows=3D106186 wi= dth=3D69)" " Hash Cond: (tool_performance.project_id =3D proj= ect.project_id)" " -> Seq Scan on tool_performance (cost=3D0.00..= 47442.18 rows=3D106186 width=3D39)" " Filter: ((insert_time > '2017-05-01 00:00:= 00+05:30'::timestamp with time zone) AND (insert_time < '2017-05-02 00:00:0= 0+05:30'::timestamp with time zone))" " -> Hash (cost=3D1.63..1.63 rows=3D63 width=3D3= 8)" " -> Seq Scan on project (cost=3D0.00..1.6= 3 rows=3D63 width=3D38)" We have 64 GB of RAM & CPU(s): 40 Thread(s) per core: 2 Core(s) per socket: 10 Socket(s): 2 PostgreSQL.conf parameter: shared_buffers =3D16GB work_mem =3D32MB Would you please help how we can tune this query at database & code level. Regards Daulat --_000_e226d5c404cf4cf69ef39354aaab2de0cyientcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi team,

 

We are getting very slow response of this query.

 

SELECT date_trunc('day', insert_time),workflow.proje= ct.project_name,

workflow.tool_performance.project_id,workflow.tool_p= erformance.user_id,workflow.tool_performance.step_id,count(*),

round(sum(execution_time)/1000) as Sum_time_sec,roun= d(((round(sum(execution_time)/1000))/60)/count(*),2) as Efficency_Min,round

(((round(sum(execution_time)/1000)))/count(*),2)

as Efficency_sec FROM workflow.project,workflow.tool= _performance,workflow.evidence_to_do WHERE workflow.project

.project_id =3D workflow.tool_performance.project_id= AND insert_time >'2017-05-01' AND insert_time <'2017-05-02' AND

workflow.evidence_to_do.status_id in (15100,15150,15= 200,15300,15400,15500) Group BY date_trunc('day', insert_time),workflow.pro= ject.project_name,

workflow.tool_performance.project_id,workflow.tool_p= erformance.user_id,workflow.tool_performance.step_id ORDER BY

workflow.tool_performance.project_id,workflow.projec= t.project_name,

workflow.tool_performance.step_id;

 

The following indexes are created on project &= ; evidence_to_do table.

 

"CREATE INDEX project_id_idx ON workflow.projec= t USING btree (project_id)"

"CREATE INDEX evidence_to_do_status_id_index ON= workflow.evidence_to_do USING btree (status_id)"

 

 

Explain plan of the Query is:

 

"GroupAggregate  (cost=3D18675703613.60..2= 0443753075.67 rows=3D6689718 width=3D69)"

"  ->  Sort  (cost=3D18675703= 613.60..18823015982.33 rows=3D58924947492 width=3D69)"

"        Sor= t Key: tool_performance.project_id, project.project_name, tool_performance.= step_id, (date_trunc('day'::text, tool_performance.insert_time)), tool_perf= ormance.user_id"

"        -&g= t;  Nested Loop  (cost=3D2.42..884042104.67 rows=3D58924947492 wi= dth=3D69)"

"        &nb= sp;     ->  Seq Scan on evidence_to_do&nbs= p; (cost=3D0.00..118722.17 rows=3D554922 width=3D0)"

"       &nbs= p;            Filter= : (status_id =3D ANY ('{15100,15150,15200,15300,15400,15500}'::bigint[]))&q= uot;

"       &nbs= p;      ->  Materialize  (cost=3D2.42= ..49435.58 rows=3D106186 width=3D69)"

"       &nbs= p;            ->&= nbsp; Hash Join  (cost=3D2.42..48904.65 rows=3D106186 width=3D69)"= ;

"       &nbs= p;            &= nbsp;     Hash Cond: (tool_performance.project_id =3D p= roject.project_id)"

"       &nbs= p;            &= nbsp;     ->  Seq Scan on tool_performance = ; (cost=3D0.00..47442.18 rows=3D106186 width=3D39)"

"       &nbs= p;            &= nbsp;           Filter: (= (insert_time > '2017-05-01 00:00:00+05:30'::timestamp with time zone= ) AND (insert_time < '2017-05-02 00:00:00+05:30'::timestamp with tim= e zone))"

"       &nbs= p;            &= nbsp;     ->  Hash  (cost=3D1.63..1.63 row= s=3D63 width=3D38)"

"       &nbs= p;            &= nbsp;           ->&nbs= p; Seq Scan on project  (cost=3D0.00..1.63 rows=3D63 width=3D38)"=

 

 

We have 64 GB of RAM &

 

CPU(s):       &nb= sp;        40

Thread(s) per core:    2

Core(s) per socket:    10<= /p>

Socket(s):       =       2



PostgreSQL.conf parameter:

shared_buffers =3D16GB

work_mem =3D32MB

 

Would you please help how we can tune this query at = database & code level.

 

Regards Daulat

--_000_e226d5c404cf4cf69ef39354aaab2de0cyientcom_--