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

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.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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> > On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal <
>> mittalshubham30(at)gmail(dot)com>
>> > wrote:
>> >> *Please help in optimizing this query. I need to actually generate
>> reports
>> >> daily using this query.. It takes almost 15 to 20 min to execute this
>> query
>> >> due to joins.. *
>>
>> > Use jsonb_populate_recordset (or one of its siblings) to get rid of as
>> many
>> > of these key-based value extraction operations as possible and build a
>> > table from the contents of the jsonb.
>>
>> While those duplicative extractions sure look inefficient, it's not
>> clear from the (lack of) given facts whether that's the main cost,
>> or whether the complicated FROM clause is producing a bad plan.
>> I'd suggest first looking at EXPLAIN ANALYZE output to verify which
>> plan step(s) are slow. If it's the final output step that's expensive,
>> then yes the next step is to optimize the extractions.
>>
>> Otherwise, see
>>
>> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>>
>> regards, tom lane
>>
>
> 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
>
>