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

Please provide any inputs on this problem..

On Mon, Sep 6, 2021, 12:21 AM Shubham Mittal <mittalshubham30(at)gmail(dot)com>
wrote:

> 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(dot)mladen(at)gmail(dot)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
>>
>>