Home > mailing lists

Re: Query takes around 15 to 20 min over 20Lakh rows - Mailing list pgsql-general

From	Shubham Mittal
Subject	Re: Query takes around 15 to 20 min over 20Lakh rows
Date	September 5, 2021 21:51:00
Msg-id	CA+ERcR8qspcq_mxTqBkmV8nXGw4EXwixhN8Yh2s=3VE8a895yQ@mail.gmail.com Whole thread Raw
In response to	Re: Query takes around 15 to 20 min over 20Lakh rows (Mladen Gogala <gogala.mladen@gmail.com>)
Responses	Re: Query takes around 15 to 20 min over 20Lakh rows
List	pgsql-general

Tree view

Hi Mladen,

Thanks for the input. But could you please help in revising the query as every subquery used in join is different As per my understanding we use CTE if same query output is required to be reused in the outer query multiple times.

If you can give more clarity on some part of the query, how to convert, it would be much helpful.

Thanks & Regards,

Shubham

On Sun, Sep 5, 2021 at 11:21 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 9/5/21 1:06 PM, Shubham Mittal wrote:
Hi EveryOne,

Please find the complete query and also explain plan. This is run on PostgreSQL 11.6 on x86_64-pc-linux-gnu. This query is being run on a logically replicated db instance for generating dynamic reports multiple times in a day. Here Task and task_history are two tables on which join is currently there based on some conditions. common_details is a json column in the task table. All indexes can be seen in the explain plan.
The task table is partitioned on organisation_process_path and created_date two columns. But I could not include created_date anywhere in the query due to business requirements which the query is trying to fulfill.

Please help in highlighting any optimisations that can be done.

SELECT TASK.order_id,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srType' :: text AS product,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'mobileNumber' :: text AS msisdn,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'alternateNumber' :: text AS
alternate_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'circle' :: text AS
parent_circle,
TASK.circle,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srNumber' :: text AS
complaint_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'caseType' :: text AS
complaint_type,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'status' :: text ) AS status,
TASK.status AS
task_status,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'subType' :: text AS SUBTYPE,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'subSubType' :: text AS
subsubtype,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'source' :: text AS source,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custType' :: text AS
customer_type,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custClass' :: text AS
customer_class,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custValue' :: text AS
customer_value,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'accountNumber' :: text AS
account_number,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'slaDt' :: text ) AS sladt,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srDt' :: text AS sr_date,
CASE Lower(TASK.status)
WHEN 'reopen' THEN NULL
ELSE ( totalTimeJoin.modified_date )
END AS
resolutiondatetime,
reopenJoin.modified_date :: DATE AS
reopen_date,
TASK.dynamic_data ->> 'resolution_code' :: text AS rc,
TASK.dynamic_data ->> 'fault_found_code' :: text AS ffc,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'servingCellId' :: text AS
serving_cell_id,
Coalesce(( ( ( TASK.common_details -> 'commonDetails' :: text ) ->
'nchBundle'
:: text )
->> 'servingSiteId' :: text ),
(
( (
TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' :: text )
->> 'producthandsetType' ::
text )
)
AS
servingsiteid,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'customerLat' :: text AS
customer_lat,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'customerLng' :: text AS
customer_long,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'thanksCustomer' :: text AS
thanks_flag,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custValue' :: text AS
black_flag,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'caseType' :: text AS sr_ftr,
TASK.dynamic_data ->> 'dsl_connection' :: text AS dsl,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'appInstalled' :: text AS
app_installed,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'voiceMOU' :: text AS voice_mou
,
( ( TASK.common_details -> 'commonDetails' :: text ) ->
'nchBundle' :: text )
->> 'dataConsumed' :: text AS data_mou,
( TASK.common_details -> 'commonDetails' :: text ) ->> 'sourceChannel' ::
text
AS lob,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'issue' :: text AS category,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'handsetType' :: text AS
handset_type,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'coverageType' :: text AS
technology,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'USIMStatus' :: text AS usim,
TASK.dynamic_data ->> 'solution_suggested' :: text AS
solution_suggested,
TASK.dynamic_data ->> 'solution_to_be_implemented' :: text AS
solution_to_be_implemented,
TASK.dynamic_data ->> 'solution_implemented' :: text AS
solution_implemented,
npiActionJoin.modified_date :: DATE AS
npi_action_date,
TASK.created_date AS
order_created_date,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'partyName' :: text AS
customer_name,
TASK.pincode,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'address' :: text AS address,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'problemLocation' :: text AS
problematic_location,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'specialCust' :: text AS
customer_type1,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'gridId' :: text AS grid_id,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'dffIndoorOutdoor' :: text AS
dff_indoor_outdoor,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'problemSince' :: text AS
problem_duration,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'signalsNumber' :: text AS
number_of_signals,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationFlag' :: text ) AS
escalationflag,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationCount' :: text ) AS
escalationCount,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationDate' :: text ) AS
escalationDate,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationSource' :: text ) AS
escalationsource,
TASK.pending_with,
TASK.pending_with_details,
TASK.pending_with_role,
TASK.agency_name AS agency,
TASK.dynamic_data ->> 'appoinment_date' :: text AS
survey_visit_date,
surveyJoin.pending_with AS
survey_engineer,
surveyJoin.pending_with_details AS
survey_engineer_msisdn,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'serviceImpactingAlarmsName' :: text AS
service_impacting_alarm,
tsgJoin.pending_with AS
tsg_advisor,
tsgJoin.pending_with_details AS
tsg_advisor_msisdn,
TASK.dynamic_data ->> 'planned_site_id' :: text AS
planned_site_id,
TASK.dynamic_data ->> 'planned_site_id_rfs_timeline' :: text AS
planned_site_timeline,
TASK.dynamic_data ->> 'status_of_planned_site' :: text AS
planned_site_status,
TASK.dynamic_data ->> 'upgrade_site_id' :: text AS
upgrade_site,
TASK.dynamic_data ->> 'upgrade_site_id_rfs_timeline' :: text AS
upgrade_site_timeline,
TASK.dynamic_data ->> 'status_of_ugrade_planned' :: text AS
upgrade_site_status,
TASK.dynamic_data ->> 'sector_addition_status' :: text AS
sector_addition_twinbeam_status,
installationJoin.modified_date :: DATE AS
installation_date,
repairJoin.modified_date :: DATE AS
repair_date,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'haltedSiteId' :: text AS
halted_site,
engineerDetailsJoin.pending_with AS
npi_engineer_name,
engineerDetailsJoin.pending_with_details AS
npi_engineer_msisdn,
npiBucketJoin.modified_date :: DATE AS
npi_bucket_date,
TASK.dynamic_data ->> 'wo_number' :: text AS
operations_internal_work_order,
TASK.dynamic_data ->> 'final_2g_serving_cell_id' :: text AS
final_2g_serving_cell_id,
TASK.dynamic_data ->> 'final_2g_serving_site_id' :: text AS
final_2g_serving_site_id_mo,
TASK.dynamic_data ->> 'final_3g_serving_cell_id' :: text AS
final_3g_serving_cell_id,
TASK.dynamic_data ->> 'final_3g_serving_site_id' :: text AS
final_3g_serving_site_id_mo,
TASK.dynamic_data ->> 'final_4g_serving_cell_id' :: text AS
final_4g_serving_cell_id,
TASK.dynamic_data ->> 'final_4g_serving_site_id' :: text AS
final_4g_serving_site_id_mo,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'rm' :: text AS
rm_mobile_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'tl' :: text AS
tl_mobile_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'coordinator' :: text AS
coordinator_mobile_number,
TASK.dynamic_data ->> 'dpr_key' :: text AS dpr_key,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srSummary' :: text ) AS srsummary
,
TASK.dynamic_data ->> 'survey_remarks' :: text AS
survey_summary,
TASK.dynamic_data ->> 'npi_remarks' :: text AS
npi_remarks
FROM (((((((((TASK TASK
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc'
)
AND ( ( ts.status ) = ANY (
( array ['Survey Planned',
'Femto SR to Survey Engineer',
'Repeater SR to Survey Engineer'
,
'Circle OPS Survey Planned - Femto repair'
,
'SR sent for initial survey' ] )) ) ))
surveyJoin
ON (( ( TASK.txn_id = surveyJoin.txn_id )
AND ( surveyJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc'
)
AND ( ( ts.status ) = 'SR with TSG hub' ) ))
tsgJoin
ON (( ( TASK.txn_id = tsgJoin.txn_id )
AND ( tsgJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc'
)
AND ( ( ts.status ) = ANY
(( array ['Femto Installed'
,
'Repeater Installed' ]
)) ) )) installationJoin
ON (( ( TASK.txn_id = installationJoin.txn_id )
AND ( installationJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc'
)
AND ( ( ts.status ) = ANY
(( array ['FEMTO REPAIRED',
'REPEATER REPAIRED' ] ))
) )) repairJoin
ON (( ( TASK.txn_id = repairJoin.txn_id )
AND ( repairJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc' )
AND ( ( ts.status ) = ANY
(( array ['SR Assigned to NPI'
,
'SR Assigned to NPI for Review' ] )) )
)) engineerDetailsJoin
ON (( ( TASK.txn_id = engineerDetailsJoin.txn_id )
AND ( engineerDetailsJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc' )
AND ( ( ts.status ) = 'SR Resolved' ) ))
totalTimeJoin
ON (( ( TASK.txn_id = totalTimeJoin.txn_id )
AND ( totalTimeJoin.rn = 1 )
AND Lower(TASK.status) IN( 'sr resolved', 'closed',
'close',
'closelooped',
'resolved' )
AND Lower(( ( TASK.common_details -> 'commonDetails' )
->
'nchBundle' ) ->>
'status'
) NOT IN ( 'reopen', 're-opened' ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc' )
AND ( ( ts.status ) = 'REOPEN' ) )) reopenJoin
ON (( ( TASK.txn_id = reopenJoin.txn_id )
AND ( reopenJoin.rn = 1 )
AND Lower(( ( TASK.common_details -> 'commonDetails' ) ->
'nchBundle' ) ->>
'status'
) IN( 'reopen', 're-opened', 'sr resolved', 'closed',
'close', 'closelooped', 'resolved' ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc' )
AND ( ( ts.status ) ~~* 'SR Assigned to NPI' ) ))
npiBucketJoin
ON (( ( TASK.txn_id = npiBucketJoin.txn_id )
AND ( npiBucketJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'org_abc' )
AND ( ( ts.action ) = ANY (
( array ['other_solutions_available',
'Planning_Solution', 'Hard_Optimization'
,
'Repair_Required',
'Specific_Connectivity_Issue_Identified'
,
'Soft_Optimization',
'repeater_team_available',
'Deployment_solution',
'sr_initial_survey_required',
'Operations_issue'
] )) ) )) npiActionJoin
ON (( ( TASK.txn_id = npiActionJoin.txn_id )
AND ( npiActionJoin.rn = 1 ) )))
WHERE ( TASK.operating_boundary_path <@ 'INDIA' )
AND
( TASK.common_details -> 'commonDetails' -> 'nchBundle' ->> 'srDt' ) :: timestamp > '2021-03-28T12:01:00.000Z'
AND
( TASK.common_details -> 'commonDetails' -> 'nchBundle' ->> 'srDt' ) :: timestamp < '2021-09-02T11:50:00.000Z'
AND TASK.status NOT IN ( 'SR Resolved', 'CLOSED', 'Closed', 'Resolved' )
AND TASK.organisation_process_path = 'org_abc';
explain ANALYSE SELECT TASK.order_id,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srType' :: text AS product,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'mobileNumber' :: text AS msisdn,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'alternateNumber' :: text AS
alternate_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'circle' :: text AS
parent_circle,
TASK.circle,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srNumber' :: text AS
complaint_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'caseType' :: text AS
complaint_type,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'status' :: text ) AS status,
TASK.status AS
task_status,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'subType' :: text AS SUBTYPE,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'subSubType' :: text AS
subsubtype,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'source' :: text AS source,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custType' :: text AS
customer_type,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custClass' :: text AS
customer_class,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custValue' :: text AS
customer_value,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'accountNumber' :: text AS
account_number,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'slaDt' :: text ) AS sladt,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srDt' :: text AS sr_date,
CASE Lower(TASK.status)
WHEN 'reopen' THEN NULL
ELSE ( totalTimeJoin.modified_date )
END AS
resolutiondatetime,
reopenJoin.modified_date :: DATE AS
reopen_date,
TASK.dynamic_data ->> 'resolution_code' :: text AS rc,
TASK.dynamic_data ->> 'fault_found_code' :: text AS ffc,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'servingCellId' :: text AS
serving_cell_id,
Coalesce(( ( ( TASK.common_details -> 'commonDetails' :: text ) ->
'nchBundle'
:: text )
->> 'servingSiteId' :: text ),
(
( (
TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' :: text )
->> 'producthandsetType' ::
text )
)
AS
servingsiteid,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'customerLat' :: text AS
customer_lat,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'customerLng' :: text AS
customer_long,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'thanksCustomer' :: text AS
thanks_flag,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'custValue' :: text AS
black_flag,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'caseType' :: text AS sr_ftr,
TASK.dynamic_data ->> 'dsl_connection' :: text AS dsl,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'appInstalled' :: text AS
app_installed,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'voiceMOU' :: text AS voice_mou
,
( ( TASK.common_details -> 'commonDetails' :: text ) ->
'nchBundle' :: text )
->> 'dataConsumed' :: text AS data_mou,
( TASK.common_details -> 'commonDetails' :: text ) ->> 'sourceChannel' ::
text
AS lob,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'issue' :: text AS category,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'handsetType' :: text AS
handset_type,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'coverageType' :: text AS
technology,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'USIMStatus' :: text AS usim,
TASK.dynamic_data ->> 'solution_suggested' :: text AS
solution_suggested,
TASK.dynamic_data ->> 'solution_to_be_implemented' :: text AS
solution_to_be_implemented,
TASK.dynamic_data ->> 'solution_implemented' :: text AS
solution_implemented,
npiActionJoin.modified_date :: DATE AS
npi_action_date,
TASK.created_date AS
order_created_date,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'partyName' :: text AS
customer_name,
TASK.pincode,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'address' :: text AS address,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'problemLocation' :: text AS
problematic_location,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'specialCust' :: text AS
customer_type1,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'gridId' :: text AS grid_id,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'dffIndoorOutdoor' :: text AS
dff_indoor_outdoor,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'problemSince' :: text AS
problem_duration,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'signalsNumber' :: text AS
number_of_signals,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationFlag' :: text ) AS
escalationflag,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationCount' :: text ) AS
escalationCount,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationDate' :: text ) AS
escalationDate,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'escalationSource' :: text ) AS
escalationsource,
TASK.pending_with,
TASK.pending_with_details,
TASK.pending_with_role,
TASK.agency_name AS agency,
TASK.dynamic_data ->> 'appoinment_date' :: text AS
survey_visit_date,
surveyJoin.pending_with AS
survey_engineer,
surveyJoin.pending_with_details AS
survey_engineer_msisdn,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'serviceImpactingAlarmsName' :: text AS
service_impacting_alarm,
tsgJoin.pending_with AS
tsg_advisor,
tsgJoin.pending_with_details AS
tsg_advisor_msisdn,
TASK.dynamic_data ->> 'planned_site_id' :: text AS
planned_site_id,
TASK.dynamic_data ->> 'planned_site_id_rfs_timeline' :: text AS
planned_site_timeline,
TASK.dynamic_data ->> 'status_of_planned_site' :: text AS
planned_site_status,
TASK.dynamic_data ->> 'upgrade_site_id' :: text AS
upgrade_site,
TASK.dynamic_data ->> 'upgrade_site_id_rfs_timeline' :: text AS
upgrade_site_timeline,
TASK.dynamic_data ->> 'status_of_ugrade_planned' :: text AS
upgrade_site_status,
TASK.dynamic_data ->> 'sector_addition_status' :: text AS
sector_addition_twinbeam_status,
installationJoin.modified_date :: DATE AS
installation_date,
repairJoin.modified_date :: DATE AS
repair_date,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'haltedSiteId' :: text AS
halted_site,
engineerDetailsJoin.pending_with AS
npi_engineer_name,
engineerDetailsJoin.pending_with_details AS
npi_engineer_msisdn,
npiBucketJoin.modified_date :: DATE AS
npi_bucket_date,
TASK.dynamic_data ->> 'wo_number' :: text AS
operations_internal_work_order,
TASK.dynamic_data ->> 'final_2g_serving_cell_id' :: text AS
final_2g_serving_cell_id,
TASK.dynamic_data ->> 'final_2g_serving_site_id' :: text AS
final_2g_serving_site_id_mo,
TASK.dynamic_data ->> 'final_3g_serving_cell_id' :: text AS
final_3g_serving_cell_id,
TASK.dynamic_data ->> 'final_3g_serving_site_id' :: text AS
final_3g_serving_site_id_mo,
TASK.dynamic_data ->> 'final_4g_serving_cell_id' :: text AS
final_4g_serving_cell_id,
TASK.dynamic_data ->> 'final_4g_serving_site_id' :: text AS
final_4g_serving_site_id_mo,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'rm' :: text AS
rm_mobile_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'tl' :: text AS
tl_mobile_number,
( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'coordinator' :: text AS
coordinator_mobile_number,
TASK.dynamic_data ->> 'dpr_key' :: text AS dpr_key,
( ( ( TASK.common_details -> 'commonDetails' :: text ) -> 'nchBundle' ::
text )
->> 'srSummary' :: text ) AS srsummary
,
TASK.dynamic_data ->> 'survey_remarks' :: text AS
survey_summary,
TASK.dynamic_data ->> 'npi_remarks' :: text AS
npi_remarks
FROM (((((((((TASK TASK
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH'
)
AND ( ( ts.status ) = ANY (
( array ['Survey Planned',
'Femto SR to Survey Engineer',
'Repeater SR to Survey Engineer'
,
'Circle OPS Survey Planned - Femto repair'
,
'SR sent for initial survey' ] )) ) ))
surveyJoin
ON (( ( TASK.txn_id = surveyJoin.txn_id )
AND ( surveyJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH'
)
AND ( ( ts.status ) = 'SR with TSG hub' ) ))
tsgJoin
ON (( ( TASK.txn_id = tsgJoin.txn_id )
AND ( tsgJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH'
)
AND ( ( ts.status ) = ANY
(( array ['Femto Installed'
,
'Repeater Installed' ]
)) ) )) installationJoin
ON (( ( TASK.txn_id = installationJoin.txn_id )
AND ( installationJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH'
)
AND ( ( ts.status ) = ANY
(( array ['FEMTO REPAIRED',
'REPEATER REPAIRED' ] ))
) )) repairJoin
ON (( ( TASK.txn_id = repairJoin.txn_id )
AND ( repairJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.pending_with,
ts.pending_with_details,
ts.pending_with_role,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH' )
AND ( ( ts.status ) = ANY
(( array ['SR Assigned to NPI'
,
'SR Assigned to NPI for Review' ] )) )
)) engineerDetailsJoin
ON (( ( TASK.txn_id = engineerDetailsJoin.txn_id )
AND ( engineerDetailsJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date DESC) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH' )
AND ( ( ts.status ) = 'SR Resolved' ) ))
totalTimeJoin
ON (( ( TASK.txn_id = totalTimeJoin.txn_id )
AND ( totalTimeJoin.rn = 1 )
AND Lower(TASK.status) IN( 'sr resolved', 'closed',
'close',
'closelooped',
'resolved' )
AND Lower(( ( TASK.common_details -> 'commonDetails' )
->
'nchBundle' ) ->>
'status'
) NOT IN ( 'reopen', 're-opened' ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH' )
AND ( ( ts.status ) = 'REOPEN' ) )) reopenJoin
ON (( ( TASK.txn_id = reopenJoin.txn_id )
AND ( reopenJoin.rn = 1 )
AND Lower(( ( TASK.common_details -> 'commonDetails' ) ->
'nchBundle' ) ->>
'status'
) IN( 'reopen', 're-opened', 'sr resolved', 'closed',
'close', 'closelooped', 'resolved' ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH' )
AND ( ( ts.status ) ~~* 'SR Assigned to NPI' ) ))
npiBucketJoin
ON (( ( TASK.txn_id = npiBucketJoin.txn_id )
AND ( npiBucketJoin.rn = 1 ) )))
left join (SELECT ts.txn_id,
ts.modified_date,
Row_number()
over (
PARTITION BY ts.txn_id
ORDER BY ts.modified_date) AS rn
FROM task_history ts
WHERE ( ( ts.organisation_process_path =
'AIRTEL.SERVICE.NCH' )
AND ( ( ts.action ) = ANY (
( array ['other_solutions_available',
'Planning_Solution', 'Hard_Optimization'
,
'Repair_Required',
'Specific_Connectivity_Issue_Identified'
,
'Soft_Optimization',
'repeater_team_available',
'Deployment_solution',
'sr_initial_survey_required',
'Operations_issue'
] )) ) )) npiActionJoin
ON (( ( TASK.txn_id = npiActionJoin.txn_id )
AND ( npiActionJoin.rn = 1 ) )))
WHERE ( TASK.operating_boundary_path <@ 'INDIA' )
AND
( TASK.common_details -> 'commonDetails' -> 'nchBundle' ->> 'srDt' ) :: timestamp > '2021-03-28T12:01:00.000Z'
AND
( TASK.common_details -> 'commonDetails' -> 'nchBundle' ->> 'srDt' ) :: timestamp < '2021-09-02T11:50:00.000Z'
AND TASK.status NOT IN ( 'SR Resolved', 'CLOSED', 'Closed', 'Resolved' )
AND TASK.organisation_process_path = 'AIRTEL.SERVICE.NCH';
Hash Left Join (cost=22414321.14..22834343.75 rows=1095 width=2526) (actual time=405603.492..674990.059 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = npiactionjoin.txn_id)
-> Hash Left Join (cost=12247125.46..12666403.46 rows=1095 width=942) (actual time=286683.056..297424.682 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = npibucketjoin.txn_id)
-> Hash Left Join (cost=2417258.79..2836015.29 rows=1095 width=934) (actual time=195833.183..205884.455 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = reopenjoin.txn_id)
" Join Filter: (lower((((task_1.common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'status'::text)) = ANY ('{reopen,re-opened,""sr resolved"",closed,close,closelooped,resolved}'::text[]))"
-> Hash Left Join (cost=2367432.69..2786168.62 rows=1095 width=926) (actual time=159276.784..167931.300 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = totaltimejoin.txn_id)
" Join Filter: ((lower((task_1.status)::text) = ANY ('{""sr resolved"",closed,close,closelooped,resolved}'::text[])) AND (lower((((task_1.common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'status'::text)) <> ALL ('{reopen,re-opened}'::text[])))"
Rows Removed by Join Filter: 22221
-> Hash Left Join (cost=1306475.23..1724652.68 rows=1095 width=918) (actual time=115767.357..123441.968 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = engineerdetailsjoin.txn_id)
-> Hash Left Join (cost=498424.72..916062.87 rows=1095 width=895) (actual time=37923.564..44786.634 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = repairjoin.txn_id)
-> Hash Left Join (cost=398827.31..816425.76 rows=1095 width=887) (actual time=37923.380..44734.888 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = installationjoin.txn_id)
-> Hash Left Join (cost=299229.90..716788.65 rows=1095 width=879) (actual time=37923.290..44684.077 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = tsgjoin.txn_id)
-> Hash Left Join (cost=249403.80..666942.01 rows=1095 width=856) (actual time=1752.546..8081.056 rows=171240 loops=1)
Hash Cond: (task_1.txn_id = surveyjoin.txn_id)
-> Gather (cost=1000.00..418445.12 rows=1095 width=832) (actual time=1553.054..7790.976 rows=171240 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Append (cost=0.00..417335.62 rows=274 width=832) (actual time=1038.392..11470.655 rows=34248 loops=5)
-> Parallel Seq Scan on task_serv_nch_q4_2020 task_1 (cost=0.00..123987.65 rows=4 width=1354) (actual time=3615.528..3615.529 rows=0 loops=1)
" Filter: ((operating_boundary_path <@ 'INDIA'::ltree) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree) AND ((status)::text <> ALL ('{""SR Resolved"",CLOSED,Closed,Resolved}'::text[])) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone > '2021-03-28 12:01:00'::timestamp without time zone) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone < '2021-09-02 11:50:00'::timestamp without time zone))"
Rows Removed by Filter: 575139
-> Parallel Seq Scan on task_serv_nch_q2_2021 task_3 (cost=0.00..110496.48 rows=52 width=1080) (actual time=2.235..5974.188 rows=20086 loops=2)
" Filter: ((operating_boundary_path <@ 'INDIA'::ltree) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree) AND ((status)::text <> ALL ('{""SR Resolved"",CLOSED,Closed,Resolved}'::text[])) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone > '2021-03-28 12:01:00'::timestamp without time zone) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone < '2021-09-02 11:50:00'::timestamp without time zone))"
Rows Removed by Filter: 265520
-> Parallel Seq Scan on task_serv_nch_q3_2021 task_4 (cost=0.00..87091.00 rows=198 width=717) (actual time=1.318..7362.202 rows=26068 loops=5)
" Filter: ((operating_boundary_path <@ 'INDIA'::ltree) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree) AND ((status)::text <> ALL ('{""SR Resolved"",CLOSED,Closed,Resolved}'::text[])) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone > '2021-03-28 12:01:00'::timestamp without time zone) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone < '2021-09-02 11:50:00'::timestamp without time zone))"
Rows Removed by Filter: 87988
-> Parallel Seq Scan on task_serv_nch_q1_2021 task_2 (cost=0.00..51261.35 rows=12 width=1201) (actual time=13.306..1704.056 rows=364 loops=2)
" Filter: ((operating_boundary_path <@ 'INDIA'::ltree) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree) AND ((status)::text <> ALL ('{""SR Resolved"",CLOSED,Closed,Resolved}'::text[])) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone > '2021-03-28 12:01:00'::timestamp without time zone) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone < '2021-09-02 11:50:00'::timestamp without time zone))"
Rows Removed by Filter: 126132
-> Parallel Seq Scan on task_serv_nch_qold_2020 task (cost=0.00..44497.78 rows=8 width=1246) (actual time=1540.795..1540.795 rows=0 loops=1)
" Filter: ((operating_boundary_path <@ 'INDIA'::ltree) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree) AND ((status)::text <> ALL ('{""SR Resolved"",CLOSED,Closed,Resolved}'::text[])) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone > '2021-03-28 12:01:00'::timestamp without time zone) AND (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'srDt'::text))::timestamp without time zone < '2021-09-02 11:50:00'::timestamp without time zone))"
Rows Removed by Filter: 221142
-> Hash (cost=248402.46..248402.46 rows=107 width=31) (actual time=199.475..199.476 rows=252 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 25kB
-> Subquery Scan on surveyjoin (cost=247709.66..248402.46 rows=107 width=31) (actual time=198.305..198.512 rows=252 loops=1)
Filter: (surveyjoin.rn = 1)
Rows Removed by Filter: 1
-> WindowAgg (cost=247709.66..248136.00 rows=21317 width=192) (actual time=198.302..198.483 rows=253 loops=1)
-> Sort (cost=247709.66..247762.95 rows=21317 width=39) (actual time=198.268..198.290 rows=253 loops=1)
Sort Key: ts.txn_id, ts.modified_date DESC
Sort Method: quicksort Memory: 44kB
-> Index Scan using task_history_status_idx on task_history ts (cost=0.57..246177.00 rows=21317 width=39) (actual time=0.903..198.001 rows=253 loops=1)
" Index Cond: ((status)::text = ANY ('{""Survey Planned"",""Femto SR to Survey Engineer"",""Repeater SR to Survey Engineer"",""Circle OPS Survey Planned - Femto repair"",""SR sent for initial survey""}'::text[]))"
" Filter: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=49825.84..49825.84 rows=21 width=31) (actual time=36170.658..36170.658 rows=81659 loops=1)
Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
-> Subquery Scan on tsgjoin (cost=49687.29..49825.84 rows=21 width=31) (actual time=36046.958..36145.352 rows=81659 loops=1)
Filter: (tsgjoin.rn = 1)
Rows Removed by Filter: 36117
-> WindowAgg (cost=49687.29..49772.55 rows=4263 width=192) (actual time=36046.955..36135.210 rows=117776 loops=1)
-> Sort (cost=49687.29..49697.95 rows=4263 width=39) (actual time=36046.933..36066.147 rows=117776 loops=1)
Sort Key: ts_1.txn_id, ts_1.modified_date DESC
Sort Method: external merge Disk: 5912kB
-> Index Scan using task_history_status_idx on task_history ts_1 (cost=0.57..49430.29 rows=4263 width=39) (actual time=0.071..35909.254 rows=117776 loops=1)
" Index Cond: ((status)::text = 'SR with TSG hub'::text)"
" Filter: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=99596.87..99596.87 rows=43 width=16) (actual time=0.070..0.070 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Subquery Scan on installationjoin (cost=99319.74..99596.87 rows=43 width=16) (actual time=0.069..0.070 rows=0 loops=1)
Filter: (installationjoin.rn = 1)
-> WindowAgg (cost=99319.74..99490.28 rows=8527 width=24) (actual time=0.068..0.068 rows=0 loops=1)
-> Sort (cost=99319.74..99341.06 rows=8527 width=16) (actual time=0.066..0.067 rows=0 loops=1)
Sort Key: ts_2.txn_id, ts_2.modified_date DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using task_history_status_idx on task_history ts_2 (cost=0.57..98763.02 rows=8527 width=16) (actual time=0.058..0.058 rows=0 loops=1)
" Index Cond: ((status)::text = ANY ('{""Femto Installed"",""Repeater Installed""}'::text[]))"
" Filter: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=99596.87..99596.87 rows=43 width=16) (actual time=0.173..0.174 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on repairjoin (cost=99319.74..99596.87 rows=43 width=16) (actual time=0.170..0.171 rows=1 loops=1)
Filter: (repairjoin.rn = 1)
-> WindowAgg (cost=99319.74..99490.28 rows=8527 width=24) (actual time=0.167..0.167 rows=1 loops=1)
-> Sort (cost=99319.74..99341.06 rows=8527 width=16) (actual time=0.163..0.163 rows=1 loops=1)
Sort Key: ts_3.txn_id, ts_3.modified_date DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using task_history_status_idx on task_history ts_3 (cost=0.57..98763.02 rows=8527 width=16) (actual time=0.114..0.158 rows=1 loops=1)
" Index Cond: ((status)::text = ANY ('{""FEMTO REPAIRED"",""REPEATER REPAIRED""}'::text[]))"
" Filter: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=808042.60..808042.60 rows=633 width=31) (actual time=77843.364..77843.364 rows=731997 loops=1)
Buckets: 65536 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3585kB
-> Subquery Scan on engineerdetailsjoin (cost=803931.19..808042.60 rows=633 width=31) (actual time=76467.471..77608.295 rows=731997 loops=1)
Filter: (engineerdetailsjoin.rn = 1)
Rows Removed by Filter: 510756
-> WindowAgg (cost=803931.19..806461.29 rows=126505 width=192) (actual time=76467.468..77504.738 rows=1242753 loops=1)
-> Sort (cost=803931.19..804247.45 rows=126505 width=39) (actual time=76467.442..76753.955 rows=1242753 loops=1)
Sort Key: ts_4.txn_id, ts_4.modified_date DESC
Sort Method: external merge Disk: 62088kB
-> Gather (cost=326483.91..791183.18 rows=126505 width=39) (actual time=55428.599..75277.365 rows=1242753 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Bitmap Heap Scan on task_history ts_4 (cost=325483.91..777532.68 rows=31626 width=39) (actual time=55408.157..75813.036 rows=248551 loops=5)
" Recheck Cond: (((status)::text = ANY ('{""SR Assigned to NPI"",""SR Assigned to NPI for Review""}'::text[])) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree))"
Rows Removed by Index Recheck: 1040655
Heap Blocks: exact=10110 lossy=175029
-> BitmapAnd (cost=325483.91..325483.91 rows=126505 width=0) (actual time=55372.811..55372.811 rows=0 loops=1)
-> Bitmap Index Scan on task_history_status_idx (cost=0.00..17418.09 rows=1307768 width=0) (actual time=3246.985..3246.985 rows=1360072 loops=1)
" Index Cond: ((status)::text = ANY ('{""SR Assigned to NPI"",""SR Assigned to NPI for Review""}'::text[]))"
-> Bitmap Index Scan on idx_th_organisation_process_path (cost=0.00..308002.31 rows=10946995 width=0) (actual time=52084.639..52084.639 rows=12120619 loops=1)
" Index Cond: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=1060941.08..1060941.08 rows=1310 width=16) (actual time=43508.793..43508.793 rows=1990328 loops=1)
Buckets: 131072 (originally 2048) Batches: 32 (originally 1) Memory Usage: 3936kB
-> Subquery Scan on totaltimejoin (cost=1052426.15..1060941.08 rows=1310 width=16) (actual time=40868.371..43071.904 rows=1990328 loops=1)
Filter: (totaltimejoin.rn = 1)
Rows Removed by Filter: 902254
-> WindowAgg (cost=1052426.15..1057666.11 rows=261998 width=24) (actual time=40868.368..42831.800 rows=2892582 loops=1)
-> Sort (cost=1052426.15..1053081.14 rows=261998 width=16) (actual time=40868.350..41375.386 rows=2892582 loops=1)
Sort Key: ts_5.txn_id, ts_5.modified_date DESC
Sort Method: external merge Disk: 73656kB
-> Gather (cost=345290.85..1026223.38 rows=261998 width=16) (actual time=12324.560..38662.630 rows=2892582 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Bitmap Heap Scan on task_history ts_5 (cost=344290.85..999023.58 rows=65500 width=16) (actual time=12301.142..39536.776 rows=578516 loops=5)
" Recheck Cond: (((status)::text = 'SR Resolved'::text) AND (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree))"
Rows Removed by Index Recheck: 1101044
Heap Blocks: exact=4798 lossy=249930
-> BitmapAnd (cost=344290.85..344290.85 rows=261998 width=0) (actual time=12274.856..12274.856 rows=0 loops=1)
-> Bitmap Index Scan on task_history_status_idx (cost=0.00..36157.29 rows=2708457 width=0) (actual time=5752.355..5752.355 rows=3045195 loops=1)
" Index Cond: ((status)::text = 'SR Resolved'::text)"
-> Bitmap Index Scan on idx_th_organisation_process_path (cost=0.00..308002.31 rows=10946995 width=0) (actual time=6485.334..6485.334 rows=12120619 loops=1)
" Index Cond: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=49825.84..49825.84 rows=21 width=16) (actual time=36556.373..36556.373 rows=88757 loops=1)
Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3103kB
-> Subquery Scan on reopenjoin (cost=49687.29..49825.84 rows=21 width=16) (actual time=36459.911..36536.010 rows=88757 loops=1)
Filter: (reopenjoin.rn = 1)
Rows Removed by Filter: 1202
-> WindowAgg (cost=49687.29..49772.55 rows=4263 width=24) (actual time=36459.907..36526.051 rows=89959 loops=1)
-> Sort (cost=49687.29..49697.95 rows=4263 width=16) (actual time=36459.889..36471.319 rows=89959 loops=1)
Sort Key: ts_6.txn_id, ts_6.modified_date
Sort Method: external merge Disk: 2296kB
-> Index Scan using task_history_status_idx on task_history ts_6 (cost=0.57..49430.29 rows=4263 width=16) (actual time=0.129..36367.410 rows=89959 loops=1)
" Index Cond: ((status)::text = 'REOPEN'::text)"
" Filter: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=9829859.02..9829859.02 rows=612 width=16) (actual time=90849.844..90849.844 rows=731997 loops=1)
Buckets: 131072 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3196kB
-> Subquery Scan on npibucketjoin (cost=9825882.90..9829859.02 rows=612 width=16) (actual time=89708.678..90678.472 rows=731997 loops=1)
Filter: (npibucketjoin.rn = 1)
Rows Removed by Filter: 509616
-> WindowAgg (cost=9825882.90..9828329.74 rows=122342 width=24) (actual time=89708.674..90579.401 rows=1241613 loops=1)
-> Sort (cost=9825882.90..9826188.76 rows=122342 width=16) (actual time=89708.653..89948.972 rows=1241613 loops=1)
Sort Key: ts_7.txn_id, ts_7.modified_date
Sort Method: external merge Disk: 31656kB
-> Gather (cost=309032.90..9814318.76 rows=122342 width=16) (actual time=3927.605..88750.176 rows=1241613 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Bitmap Heap Scan on task_history ts_7 (cost=308032.90..9801084.56 rows=30586 width=16) (actual time=3834.361..89083.911 rows=248323 loops=5)
" Recheck Cond: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
Rows Removed by Index Recheck: 4898034
" Filter: ((status)::text ~~* 'SR Assigned to NPI'::text)"
Rows Removed by Filter: 2025563
Heap Blocks: exact=16002 lossy=1191572
-> Bitmap Index Scan on idx_th_organisation_process_path (cost=0.00..308002.31 rows=10946995 width=0) (actual time=3861.245..3861.245 rows=12120619 loops=1)
" Index Cond: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
-> Hash (cost=10167192.01..10167192.01 rows=293 width=16) (actual time=118910.580..118910.580 rows=446782 loops=1)
Buckets: 131072 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3667kB
-> Subquery Scan on npiactionjoin (cost=10165289.40..10167192.01 rows=293 width=16) (actual time=118413.432..118806.684 rows=446782 loops=1)
Filter: (npiactionjoin.rn = 1)
Rows Removed by Filter: 47875
-> WindowAgg (cost=10165289.40..10166460.24 rows=58542 width=24) (actual time=118413.429..118760.513 rows=494657 loops=1)
-> Sort (cost=10165289.40..10165435.75 rows=58542 width=16) (actual time=118413.395..118503.036 rows=494657 loops=1)
Sort Key: ts_8.txn_id, ts_8.modified_date
Sort Method: external merge Disk: 12616kB
-> Index Scan using idx_th_organisation_process_path on task_history ts_8 (cost=0.55..10160653.70 rows=58542 width=16) (actual time=0.837..117999.520 rows=494657 loops=1)
" Index Cond: (organisation_process_path = 'AIRTEL.SERVICE.NCH'::ltree)"
" Filter: ((action)::text = ANY ('{other_solutions_available,Planning_Solution,Hard_Optimization,Repair_Required,Specific_Connectivity_Issue_Identified,Soft_Optimization,repeater_team_available,Deployment_solution,sr_initial_survey_required,Operations_issue}'::text[]))"
Rows Removed by Filter: 10874773

Planning Time: 111.506 ms
Execution Time: 675129.656 ms

On Fri, Sep 3, 2021 at 4:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal <mittalshubham30@gmail.com>
> wrote:
>> *Please help in optimizing this query. I need to actually generate reports
>> daily using this query.. It takes almost 15 to 20 min to execute this query
>> due to joins.. *

> Use jsonb_populate_recordset (or one of its siblings) to get rid of as many
> of these key-based value extraction operations as possible and build a
> table from the contents of the jsonb.

While those duplicative extractions sure look inefficient, it's not
clear from the (lack of) given facts whether that's the main cost,
or whether the complicated FROM clause is producing a bad plan.
I'd suggest first looking at EXPLAIN ANALYZE output to verify which
plan step(s) are slow. If it's the final output step that's expensive,
then yes the next step is to optimize the extractions.

Otherwise, see

https://wiki.postgresql.org/wiki/Slow_Query_Questions

regards, tom lane

The query is far too big. Also, there are several "parallel seq can" accesses, some of which are completely needless:
Parallel Seq Scan on task_serv_nch_q4_2020 task_1 (cost=0.00..123987.65 rows=4 width=1354) (actual time=3615.528..3615.529 rows=0 loops=1)
Rows Removed by Filter: 575139 (So, the parallel query only returns 4 rows and discards over half a million? That would be much better server by an index)
Parallel Seq Scan on task_serv_nch_q2_2021 task_3 (cost=0.00..110496.48 rows=52 width=1080) (actual time=2.235..5974.188 rows=20086 loops=2)
Parallel Seq Scan on task_serv_nch_q3_2021 task_4 (cost=0.00..87091.00 rows=198 width=717) (actual time=1.318..7362.202 rows=26068 loops=5)
Queries of this size usually mean that there is a problem with the data model or business analyst who doesn't understand the data model properly. Also, such queries should be sliced and diced using CTE and temporary tables.
Last, contrary to popular belief, parallel processing speeds things up only in a very limited number of cases, usually in a data warehouse environment. That applies to Oracle, SQL Server and Postgres. Typical case for parallel processing is aggregating data from one huge table. Plan like yours will include ping pong with the messages between the parallel processes, which will take time, especially on NUMA systems, which means on the majority of the modern multi-processor machines. So, please rewrite the query using CTE ("WITH" statement), some temporary tables and optimize it piece by piece. The best strategy comes from the Roman Empire: divide and conquer.
-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

pgsql-general by date:

From: "David G. Johnston"
Date: 05 September 2021, 21:32:41
Subject: Re: How to observe plan_cache_mode transition from custom to generic plan?

From: Ian Dauncey
Date: 06 September 2021, 09:05:13
Subject: RE: vacuum full

Re: Query takes around 15 to 20 min over 20Lakh rows - Mailing list pgsql-general

Previous

Next