Re: Query takes around 15 to 20 min over 20Lakh rows

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 msExecution Time: 675129.656 ms*

On Fri, Sep 3, 2021 at 4:52 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal <mittalshubham30(at)gmail(dot)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
>