Query takes around 15 to 20 min over 20Lakh rows
Hi ,
*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.. *
*Here common_details is a jsonB column.*
SELECT T.order_id,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'srType' :: text
AS
product,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'mobileNumber' :: text
AS
msisdn,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'alternateNumber' :: text
AS
alternate_number,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'circle' :: text
AS
parent_circle,
T.circle,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'srNumber' :: text
AS
complaint_number,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'caseType' :: text
AS
complaint_type,
( ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'status' :: text )
AS
status,
T.status
AS
task_status,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'subType' :: text
AS
SUBTYPE,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'subSubType' :: text
AS
subsubtype,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'source' :: text
AS
source,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'custType' :: text
AS
customer_type,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'custClass' :: text
AS
customer_class,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'custValue' :: text
AS
customer_value,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'accountNumber' :: text
AS
account_number,
To_char(( ( ( T.common_details -> 'commonDetails' :: text ) ->
'bundle' ::
text )
->> 'slaDt' :: text ) :: timestamp, 'DD/MM/YYYY
HH24:MI:SS') AS
sladt,
To_char(( ( ( T.common_details -> 'commonDetails' :: text ) ->
'bundle' ::
text )
->> 'srDt' :: text ) :: timestamp, 'DD/MM/YYYY
HH24:MI:SS') AS
sr_date,
CASE Lower(T.status)
WHEN 'reopen' THEN NULL
ELSE To_char(( totalTimeJoin.modified_date ), 'DD/MM/YYYY
HH24:MI:SS')
END
AS
resolutiondatetime,
To_char(reopenJoin.modified_date, 'DD/MM/YYYY HH24:MI:SS')
AS
reopen_date,
T.dynamic_data ->> 'resolution_code' :: text
AS
rc,
T.dynamic_data ->> 'fault_found_code' :: text
AS
ffc,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'servingCellId' :: text
AS
serving_cell_id,
Coalesce(( ( ( T.common_details -> 'commonDetails' :: text ) ->
'bundle'
:: text )
->> 'servingSiteId' :: text ),
(
( (
T.common_details -> 'commonDetails' :: text ) -> 'bundle' :: text )
->> 'producthandsetType'
::
text )
)
AS
servingsiteid,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'customerLat' :: text
AS
customer_lat,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'customerLng' :: text
AS
customer_long,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'thanksCustomer' :: text
AS
thanks_flag,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'custValue' :: text
AS
black_flag,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'caseType' :: text
AS
sr_ftr,
T.dynamic_data ->> 'dsl_connection' :: text
AS
dsl,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'appInstalled' :: text
AS
app_installed,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'voiceMOU' :: text
AS
voice_mou,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'dataConsumed' :: text
AS
data_mou,
( T.common_details -> 'commonDetails' :: text ) ->> 'sourceChannel'
::
text
AS
lob,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'issue' :: text
AS
category,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'handsetType' :: text
AS
handset_type,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'coverageType' :: text
AS
technology,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'USIMStatus' :: text
AS
usim,
T.dynamic_data ->> 'solution_suggested' :: text
AS
solution_suggested,
T.dynamic_data ->> 'solution_to_be_implemented' :: text
AS
solution_to_be_implemented,
T.dynamic_data ->> 'solution_implemented' :: text
AS
solution_implemented,
To_char(npiActionJoin.modified_date, 'DD/MM/YYYY HH24:MI:SS')
AS
npi_action_date,
To_char(T.created_date, 'DD/MM/YYYY HH24:MI:SS')
AS
order_created_date,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'partyName' :: text
AS
customer_name,
T.pincode,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'address' :: text
AS
address,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'problemLocation' :: text
AS
problematic_location,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'specialCust' :: text
AS
customer_type1,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'gridId' :: text
AS
grid_id,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'dffIndoorOutdoor' :: text
AS
dff_indoor_outdoor,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'problemSince' :: text
AS
problem_duration,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'signalsNumber' :: text
AS
number_of_signals,
( ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'escalationFlag' :: text )
AS
escalationflag,
( ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'escalationCount' :: text )
AS
escalationCount,
To_char(( ( ( T.common_details -> 'commonDetails' :: text ) ->
'bundle' ::
text )
->> 'escalationDate' :: text ) :: timestamp,
'DD/MM/YYYY HH24:MI:SS')
AS escalationDate,
( ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'escalationSource' :: text )
AS
escalationsource,
T.pending_with,
T.pending_with_details,
T.pending_with_role,
T.agency_name
AS
agency,
To_char(( T.dynamic_data ->> 'appoinment_date' :: text ) ::
timestamp,
'DD/MM/YYYY HH24:MI:SS')
AS
survey_visit_date,
surveyJoin.pending_with
AS
survey_engineer,
surveyJoin.pending_with_details
AS
survey_engineer_msisdn,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'serviceImpactingAlarmsName' :: text
AS
service_impacting_alarm,
tsgJoin.pending_with
AS
tsg_advisor,
tsgJoin.pending_with_details
AS
tsg_advisor_msisdn,
T.dynamic_data ->> 'planned_site_id' :: text
AS
planned_site_id,
T.dynamic_data ->> 'planned_site_id_rfs_timeline' :: text
AS
planned_site_timeline,
T.dynamic_data ->> 'status_of_planned_site' :: text
AS
planned_site_status,
T.dynamic_data ->> 'upgrade_site_id' :: text
AS
upgrade_site,
T.dynamic_data ->> 'upgrade_site_id_rfs_timeline' :: text
AS
upgrade_site_timeline,
T.dynamic_data ->> 'status_of_ugrade_planned' :: text
AS
upgrade_site_status,
T.dynamic_data ->> 'sector_addition_status' :: text
AS
sector_addition_twinbeam_status,
To_char(installationJoin.modified_date, 'DD/MM/YYYY HH24:MI:SS')
AS
installation_date,
To_char(repairJoin.modified_date, 'DD/MM/YYYY HH24:MI:SS')
AS
repair_date,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'haltedSiteId' :: text
AS
halted_site,
engineerDetailsJoin.pending_with
AS
npi_engineer_name,
engineerDetailsJoin.pending_with_details
AS
npi_engineer_msisdn,
To_char(npiBucketJoin.modified_date, 'DD/MM/YYYY HH24:MI:SS')
AS
npi_bucket_date,
T.dynamic_data ->> 'wo_number' :: text
AS
operations_internal_work_order,
T.dynamic_data ->> 'final_2g_serving_cell_id' :: text
AS
final_2g_serving_cell_id,
T.dynamic_data ->> 'final_2g_serving_site_id' :: text
AS
final_2g_serving_site_id_mo,
T.dynamic_data ->> 'final_3g_serving_cell_id' :: text
AS
final_3g_serving_cell_id,
T.dynamic_data ->> 'final_3g_serving_site_id' :: text
AS
final_3g_serving_site_id_mo,
T.dynamic_data ->> 'final_4g_serving_cell_id' :: text
AS
final_4g_serving_cell_id,
T.dynamic_data ->> 'final_4g_serving_site_id' :: text
AS
final_4g_serving_site_id_mo,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'rm' :: text
AS
rm_mobile_number,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'tl' :: text
AS
tl_mobile_number,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'coordinator' :: text
AS
coordinator_mobile_number,
T.dynamic_data ->> 'dpr_key' :: text
AS
dpr_key,
( ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'srSummary' :: text )
AS
srsummary,
T.dynamic_data ->> 'survey_remarks' :: text
AS
survey_summary,
T.dynamic_data ->> 'npi_remarks' :: text
AS
npi_remarks
FROM (((((((((T T
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 =
'B'
)
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 (( ( T.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 =
'B'
)
AND ( ( ts.status ) = 'SR with TSG hub'
) ))
tsgJoin
ON (( ( T.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 =
'B'
)
AND ( ( ts.status ) = ANY
(( array ['Femto Installed'
,
'Repeater Installed' ]
)) ) )) installationJoin
ON (( ( T.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 =
'B'
)
AND ( ( ts.status ) = ANY
(( array ['FEMTO REPAIRED',
'REPEATER REPAIRED' ] ))
) )) repairJoin
ON (( ( T.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 =
'B' )
AND ( ( ts.status ) = ANY
(( array ['SR Assigned to NPI'
,
'SR Assigned to NPI for Review' ] )) )
)) engineerDetailsJoin
ON (( ( T.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 =
'B' )
AND ( ( ts.status ) = 'SR Resolved' )
AND ts.action_performed_by NOT IN ( 'SYSTEM'
) ))
totalTimeJoin
ON (( ( T.txn_id = totalTimeJoin.txn_id )
AND ( totalTimeJoin.rn = 1 )
AND ( T.status ) IN ( 'SR Resolved', 'CLOSED',
'closed',
'Closed',
'resolved'
)
AND Lower(( ( T.common_details -> 'commonDetails' )
->
'bundle' ) ->>
'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 =
'B' )
AND ( ( ts.status ) = 'REOPEN' ) )) reopenJoin
ON (( ( T.txn_id = reopenJoin.txn_id )
AND ( reopenJoin.rn = 1 )
AND Lower(( ( T.common_details -> 'commonDetails' )
->
'bundle' ) ->>
'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 =
'B' )
AND ( ( ts.status ) ~~* 'SR Assigned to NPI' )
))
npiBucketJoin
ON (( ( T.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 =
'B' )
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 (( ( T.txn_id = npiActionJoin.txn_id )
AND ( npiActionJoin.rn = 1 ) )))
Thanks ,
Shubham
What is T and how many rows are in there? How many rows in task_history?
What indexes exist? Are you confident you want 2 million rows in that
result set? What version is this on? What pg_settings have been changed
from defaults?
On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal <mittalshubham30@gmail.com>
wrote:
Hi ,
*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.. *
*Here common_details is a jsonB column.*SELECT T.order_id,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'srType' :: text
AS
product,
( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' ::
text )
->> 'mobileNumber' :: text
AS
msisdn,
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.
Possibly into a temporary table to which you add indexes.
David J.
"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
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@sss.pgh.pa.us> wrote:
Show quoted text
"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
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
<mailto:tgl@sss.pgh.pa.us>> wrote:"David G. Johnston" <david.g.johnston@gmail.com
<mailto:david.g.johnston@gmail.com>> writes:On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal
<mittalshubham30@gmail.com <mailto: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
<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
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:
Show quoted text
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-1217https://dbwhisperer.wordpress.com
Please provide any inputs on this problem..
On Mon, Sep 6, 2021, 12:21 AM Shubham Mittal <mittalshubham30@gmail.com>
wrote:
Show quoted text
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,
ShubhamOn 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.SE
What does the term 'over 20Lakh rows' mean? Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan, Afrika...
20 Lakh is the current no of rows in the task table.. on which the query is
executed..
On Mon, Sep 6, 2021, 11:44 PM Matthias Apitz <guru@unixarea.de> wrote:
Show quoted text
What does the term 'over 20Lakh rows' mean? Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/
+49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still
existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan,
Afrika...
po 6. 9. 2021 v 20:14 odesílatel Matthias Apitz <guru@unixarea.de> napsal:
What does the term 'over 20Lakh rows' mean? Thanks
AFAIK in India (and surrounding areas) 20 Lakh = 20 * 100 000 = 2 000 000
Show quoted text
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan, Afrika...
El día lunes, septiembre 06, 2021 a las 11:45:34p. m. +0530, Shubham Mittal escribió:
20 Lakh is the current no of rows in the task table.. on which the query is
executed..
Ahh, I never came accross this (Indian) unit 'lakh' and now understand
that we're are talking about https://en.wikipedia.org/wiki/Lakh
and 20 Lakh are only 2.000.000 rows, which isn't a very big number.
Can't help with your query, though.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan, Afrika...
Have you ever used this site to visualize the explain plan and spot bad
estimates and slow nodes? https://explain.depesz.com/s/WE1R
This stands out to me-
*Subquery Scan on npiactionjoin (cost=10,165,289.40..10,167,192.01 rows=293
width=16) (actual time=118,413.432..118,806.684 rows=446,782
loops=1)Filter: (npiactionjoin.rn = 1)*
It seems that estimate is pretty far off and this node and the final node
above this are the biggest slowdowns. If you filtered down to the record
you want from task_history BEFORE the join, then maybe you would have
quicker results. I might try a materialized CTE or even an analyzed temp
table if that option is available to you, so the planner makes informed
decisions.
By the way, the order by on that row_number seems like you are getting the
OLDEST activity related to the task which could maybe be cached rather than
re-calculated daily as this query runs.
*Michael Lewis | Database Engineer*
*Entrata*
Hi Tom/David
Could you please help me getting started to optimise this query??
Thanks & Regards
Shubham mittal
On Tue, Sep 7, 2021, 8:57 PM Michael Lewis <mlewis@entrata.com> wrote:
Show quoted text
Have you ever used this site to visualize the explain plan and spot bad
estimates and slow nodes? https://explain.depesz.com/s/WE1RThis stands out to me-
*Subquery Scan on npiactionjoin (cost=10,165,289.40..10,167,192.01
rows=293 width=16) (actual time=118,413.432..118,806.684 rows=446,782
loops=1)Filter: (npiactionjoin.rn = 1)*It seems that estimate is pretty far off and this node and the final node
above this are the biggest slowdowns. If you filtered down to the record
you want from task_history BEFORE the join, then maybe you would have
quicker results. I might try a materialized CTE or even an analyzed temp
table if that option is available to you, so the planner makes informed
decisions.By the way, the order by on that row_number seems like you are getting the
OLDEST activity related to the task which could maybe be cached rather than
re-calculated daily as this query runs.*Michael Lewis | Database Engineer*
*Entrata*