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

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
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto: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 <mailto: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
> <https://wiki.postgresql.org/wiki/Slow_Query_Questions>
>
>                         regards, tom lane
>

The query is far too big. Also, there are several "parallel seq can"
accesses, some of which are completely needless:

Parallel Seq Scan on task_serv_nch_q4_2020 task_1  (cost=0.00..123987.65
rows=4 width=1354) (actual time=3615.528..3615.529 rows=0 loops=1)

Rows Removed by Filter: 575139 (So, the parallel query only returns 4
rows and discards over half a million? That would be much better server
by an index)

Parallel Seq Scan on task_serv_nch_q2_2021 task_3  (cost=0.00..110496.48
rows=52 width=1080) (actual time=2.235..5974.188 rows=20086 loops=2)

Parallel Seq Scan on task_serv_nch_q3_2021 task_4  (cost=0.00..87091.00
rows=198 width=717) (actual time=1.318..7362.202 rows=26068 loops=5)

Queries of this size usually mean that there is a problem with the data
model or business analyst who doesn't understand the data model
properly. Also, such queries should be sliced and diced using CTE and
temporary tables.

Last, contrary to popular belief, parallel processing speeds things up
only in a very limited number of cases, usually in a data warehouse
environment. That applies to Oracle, SQL Server and Postgres. Typical
case for parallel processing is aggregating data from one huge table.
Plan like yours will include ping pong with the messages between the
parallel processes, which will take time, especially on NUMA systems,
which means on the majority of the modern multi-processor machines. So,
please rewrite the query using CTE ("WITH" statement), some temporary
tables and optimize it piece by piece. The best strategy comes from the
Roman Empire: divide and conquer.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com