Обсуждение: [PERFORM] Query is running very slow......
Dear Expert,
While executing the blow query, its taking too long time to fetch output.
Could you please help to fine tune the same?
SELECT
date_trunc('day', insert_time),
workflow.project.project_name,
workflow.tool_performance.project_id,
workflow.tool_performance.user_id,
workflow.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.evidence_to_do
WHERE
workflow.evidence_to_do.project_id = workflow.tool_performance.project_id AND
workflow.evidence_to_do.project_id = workflow.project.project_id AND
workflow.tool_performance.insert_time >'2017-05-19' AND
workflow.tool_performance.insert_time <'2017-05-20' 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,
workflow.tool_performance.step_id
ORDER BY
workflow.tool_performance.project_id,
workflow.project.project_name,
workflow.tool_performance.step_id
I am using PostgreSQL 9.1 with Linux Platform.
Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
------------------------------------------------------------------
Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.
Hi, On 5/24/17 7:04 PM, Dinesh Chandra 12108 wrote: > Dear Expert, > > While executing the blow query, its taking too long time to fetch output. > > Could you please help to fine tune the same? > You'll have to provide far more details - the query alone is certainly not enough for anyone to guess why it's slow. Perhaps look at this: https://wiki.postgresql.org/wiki/Slow_Query_Questions In particular, you'll have to tell us (a) something about the hardware it's running on (b) amounts of data in the tables / databases (c) EXPLAIN or even better EXPLAIN ANALYZE of the query (d) configuration of the database (work_mem, shared_buffers etc.) regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi Tomas, Please find the below input for slow query. (a) something about the hardware it's running on RAM-->64 GB, CPU->40core (b) amounts of data in the tables / databases Database size :32GB ----------------- Tables size ----------------- Workflow.project : 8194 byte workflow.tool_performance :175 MB workflow.evidence_to_do :580 MB (c) EXPLAIN or even better EXPLAIN ANALYZE of the query "GroupAggregate (cost=16583736169.63..18157894828.18 rows=5920110 width=69)" " -> Sort (cost=16583736169.63..16714893857.43 rows=52463075120 width=69)" " 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=2.42..787115179.07 rows=52463075120 width=69)" " -> Seq Scan on evidence_to_do (cost=0.00..119443.95 rows=558296 width=0)" " Filter: (status_id = ANY ('{15100,15150,15200,15300,15400,15500}'::bigint[]))" " -> Materialize (cost=2.42..49843.24 rows=93970 width=69)" " -> Hash Join (cost=2.42..49373.39 rows=93970 width=69)" " Hash Cond: (tool_performance.project_id = project.project_id)" " -> Seq Scan on tool_performance (cost=0.00..48078.88 rows=93970 width=39)" " 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 time zone))" " -> Hash (cost=1.63..1.63 rows=63 width=38)" " -> Seq Scan on project (cost=0.00..1.63 rows=63 width=38)" (d) configuration of the database (work_mem, shared_buffers etc.) work_mem = 32MB shared_buffers = 16GB maintenance_work_mem = 8GB temp_buffers = 64MB max_connections=2000 Regards, Dinesh Chandra |Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida. ------------------------------------------------------------------ Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tomas Vondra Sent: 24 May, 2017 10:56 PM To: pgsql-performance@postgresql.org Subject: [EXTERNAL] Re: [PERFORM] Query is running very slow...... Hi, On 5/24/17 7:04 PM, Dinesh Chandra 12108 wrote: > Dear Expert, > > While executing the blow query, its taking too long time to fetch output. > > Could you please help to fine tune the same? > You'll have to provide far more details - the query alone is certainly not enough for anyone to guess why it's slow. Perhapslook at this: https://wiki.postgresql.org/wiki/Slow_Query_Questions In particular, you'll have to tell us (a) something about the hardware it's running on (b) amounts of data in the tables / databases (c) EXPLAIN or even better EXPLAIN ANALYZE of the query (d) configuration of the database (work_mem, shared_buffers etc.) regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On 5/25/17 2:26 PM, Dinesh Chandra 12108 wrote: > Hi Tomas, > > Please find the below input for slow query. > > (a) something about the hardware it's running on > RAM-->64 GB, CPU->40core > > (b) amounts of data in the tables / databases > Database size :32GB > ----------------- > Tables size > ----------------- > Workflow.project : 8194 byte > workflow.tool_performance :175 MB > workflow.evidence_to_do :580 MB > > (c) EXPLAIN or even better EXPLAIN ANALYZE of the query > > "GroupAggregate (cost=16583736169.63..18157894828.18 rows=5920110 width=69)" > " -> Sort (cost=16583736169.63..16714893857.43 rows=52463075120 width=69)" > " 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=2.42..787115179.07 rows=52463075120 width=69)" > " -> Seq Scan on evidence_to_do (cost=0.00..119443.95 rows=558296 width=0)" > " Filter: (status_id = ANY ('{15100,15150,15200,15300,15400,15500}'::bigint[]))" > " -> Materialize (cost=2.42..49843.24 rows=93970 width=69)" > " -> Hash Join (cost=2.42..49373.39 rows=93970 width=69)" > " Hash Cond: (tool_performance.project_id = project.project_id)" > " -> Seq Scan on tool_performance (cost=0.00..48078.88 rows=93970 width=39)" > " 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 time zone))" > " -> Hash (cost=1.63..1.63 rows=63 width=38)" > " -> Seq Scan on project (cost=0.00..1.63 rows=63 width=38)" > Are you sure this is the same query? The query you posted includes there two join conditions: evidence_to_do.project_id = tool_performance.project_id evidence_to_do.project_id = project.project_id But the plan only seems to enforce the equality between 'project' and 'tool_performance'. So when joining the evidence_to_do, it performs a cartesian product, producing ~52B rows (estimated). That can't be fast. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi Thomas, Thanks for your reply. Yes, the query is absolutely same which I posted. Please suggest if something need to change in query. As Per your comment... The query you posted includes there two join conditions: evidence_to_do.project_id = tool_performance.project_id evidence_to_do.project_id = project.project_id But the plan only seems to enforce the equality between 'project' and 'tool_performance'. So when joining the evidence_to_do,it performs a cartesian product, producing ~52B rows (estimated). That can't be fast. Regards, Dinesh Chandra |Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida. ------------------------------------------------------------------ Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India. -----Original Message----- From: Tomas Vondra [mailto:tomas.vondra@2ndquadrant.com] Sent: 25 May, 2017 9:08 PM To: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com> Cc: pgsql-performance@postgresql.org Subject: [EXTERNAL] Re: FW: Re: [PERFORM] Query is running very slow...... On 5/25/17 2:26 PM, Dinesh Chandra 12108 wrote: > Hi Tomas, > > Please find the below input for slow query. > > (a) something about the hardware it's running on > RAM-->64 GB, CPU->40core > > (b) amounts of data in the tables / databases > Database size :32GB > ----------------- > Tables size > ----------------- > Workflow.project : 8194 byte > workflow.tool_performance :175 MB > workflow.evidence_to_do :580 MB > > (c) EXPLAIN or even better EXPLAIN ANALYZE of the query > > "GroupAggregate (cost=16583736169.63..18157894828.18 rows=5920110 width=69)" > " -> Sort (cost=16583736169.63..16714893857.43 rows=52463075120 width=69)" > " 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=2.42..787115179.07 rows=52463075120 width=69)" > " -> Seq Scan on evidence_to_do (cost=0.00..119443.95 rows=558296 width=0)" > " Filter: (status_id = ANY ('{15100,15150,15200,15300,15400,15500}'::bigint[]))" > " -> Materialize (cost=2.42..49843.24 rows=93970 width=69)" > " -> Hash Join (cost=2.42..49373.39 rows=93970 width=69)" > " Hash Cond: (tool_performance.project_id = project.project_id)" > " -> Seq Scan on tool_performance (cost=0.00..48078.88 rows=93970 width=39)" > " 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 time zone))" > " -> Hash (cost=1.63..1.63 rows=63 width=38)" > " -> Seq Scan on project (cost=0.00..1.63 rows=63 width=38)" > Are you sure this is the same query? The query you posted includes there two join conditions: evidence_to_do.project_id = tool_performance.project_id evidence_to_do.project_id = project.project_id But the plan only seems to enforce the equality between 'project' and 'tool_performance'. So when joining the evidence_to_do,it performs a cartesian product, producing ~52B rows (estimated). That can't be fast. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi Thomas, Thanks for your reply. Yes, the query is absolutely same which I posted. Please suggest if something need to change in query. As Per your comment... The query you posted includes there two join conditions: evidence_to_do.project_id = tool_performance.project_id evidence_to_do.project_id = project.project_id But the plan only seems to enforce the equality between 'project' and 'tool_performance'. So when joining the evidence_to_do, it performs a cartesian product, producing ~52B rows (estimated). That can't be fast.
Dinesh, please check that again. Your colleague Daulat Ram posted a similar question with this WHERE-Condition:
===
WHERE workflow.project
.project_id = 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) ===
This condition would explain the query-plan. I have answered that question yesterday.
Regards, Andreas
-- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com