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