explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cjcl

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 4,500.702 ↑ 3,397.0 1 1

Subquery Scan on v (cost=3,091.47..225,155.16 rows=3,397 width=3,885) (actual time=4,500.657..4,500.702 rows=1 loops=1)

  • Output: v.new_ihs_id, v.tenant_site_id, v.noc_entity, v.project, v.region, v.state, v.proc_priority, v.tenant_priority, v.proc_inst_id_, v.task_id, v.mttr, v.date_time_start, v.date_time_assigned_by_noc_engineer, v.ihs_sbc, v.source_of_escalation, v.impact, v.alarm_cause, v.alarm_issue_type, v.noc_eng, v.noc_manager, v.proc_comment, v.fault_status, v.cascaded_site, v.tenant_rpt_group, v.comment_created, CASE WHEN (array_length(v.cascaded_site, 1) > 0) THEN 'Hub'::text ELSE 'Terminal'::text END, v.proc_id, v.tsk_id, v.power_solution_category, v.root_cause, v.tenants, v.tenant_reference_no, v.create_time, v.date_time_resolved, v.network_element, v.site_name_adress, v.resolution, v.fse, v.fse_phone, v.sfom, v.sfom_phone, v.rto, v.rto_phone, v.srm, v.srm_phone, v.fse_informed, v.fse_date_time_informed, v.sfom_informed, v.sfom_date_time_informed, v.rto_informed, v.rto_date_time_informed, v.srm_informed, v.srm_date_time_informed, v.sbc_od, v.sbc_od_phone_no, v.sbc_cto, v.sbc_cto_phone_no, v.sbc_md, v.sbc_md_phone_no, v.sbc_od_informed, v.sbc_od_date_time_informed, v.sbc_cto_informed, v.sbc_cto_date_time_informed, v.sbc_md_informed, v.sbc_md_date_time_informed, v.date_reported, v.power_type, v.ppm_date, v.rms_product, v.rms_status, v.rms_install_date, v.fuel_level, v.cascaded_rca, v.change_impact_time, v.acc_change_impact_time, v.actually_incident_date, v.actually_resolution_date, v.end_date, v.owner_, v.all_tenants, v.last_update, v.power_date_cutover, v.power_product, v.power_solution, v.tenant_type, v.external_alarm, v.alarm_cause_category, v.gen_run_hour, v.time_of_arrival, v.site_priority, v.siteaccess_24, v.restriction_reason, v.avail_access_hours, v.hvac, v.shelter, v.roof, v.floor, v.door, v.gen_run_hour2, v.no_of_generators_nsfm, v.gateway, v.alarm_cause_warning, v.alarm_issue_type_warning, v.root_cause_warning, v.alarm_cause_category_warning, v.escalation_reference, v.escalation_reference_warning, v.source_of_escalation_warning, v.gateway_warning, v.date_time_start_warning, v.date_time_resolved_warning, v.resolution_warning, v.fault_responsibility, v.fault_responsibility_warning, v.rms_onsite, v.rms_pic, v.site_name, v.tenant_site_name, v.tenant_ticket_exist, v.tenant_ticket_type, v.tenant_ticket_number, v.technology_failed, v.technology_installed, v.watch_dog_category, v.ext_alarm_cond, v.proc_comment_all, v.string_capactiy_value, v.dc_load_value, v.threshold_value, v.attachment, v.ticket_type, v.srm_noc_manager, v.warning_to_sitedown, v.warning_mttr, v.responsible_party_new, v.proc_status, CASE WHEN (v.date_time_resolved IS NOT NULL) THEN ((((('['::text || (v.date_time_start)::text) || ','::text) || (v.date_time_resolved)::text) || ']'::text))::tstzrange ELSE ((('['::text || (v.date_time_start)::text) || ', infinity)'::text))::tstzrange END, v.id, v.mdg_deployed, v.false_escalated_alarm, v.mdg_timestamp
  • Buffers: shared hit=1436
  • Functions: 119
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 464.367 ms, Inlining 0.000 ms, Optimization 176.908 ms, Emission 3789.830 ms, Total 4431.104 ms
2. 163.533 4,500.682 ↑ 3,397.0 1 1

Merge Left Join (cost=3,091.47..224,968.32 rows=3,397 width=3,853) (actual time=4,500.638..4,500.682 rows=1 loops=1)

  • Output: (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_site_id'::jsonpath, '{}'::jsonb, false)), (d.doc #>> '{maintenancesite,noc_classification}'::text[]), (d.doc #>> '{maintenancesite,project}'::text[]), (d.doc #>> '{maintenancesite,region}'::text[]), (d.doc #>> '{maintenancesite,state}'::text[]), (replace(upper((d.doc ->> 'priority'::text)), 'P'::text, ''::text))::integer, jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_priority_mapped'::jsonpath, '{}'::jsonb, false)), ROW((d.proc_inst_id)::text, ('/#/abox/processes/'::text || (d.proc_inst_id)::text), NULL::text, NULL::text)::avatar, ROW((d.task_id)::text, ('/#/abox/tasks/'::text || (d.task_id)::text), NULL::text, NULL::text)::avatar, CASE WHEN ((d.doc ->> 'incident_time'::text) IS NOT NULL) THEN bi.formathrs(date_trunc('second'::text, (COALESCE(((d.doc ->> 'resolution_time'::text))::timestamp with time zone, now()) - ((d.doc ->> 'incident_time'::text))::timestamp with time zone))) ELSE NULL::interval END, ((d.doc ->> 'incident_time'::text))::timestamp with time zone, (COALESCE((jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == SBC NOC)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (d.doc ->> 'time_vendor_notified'::text)))::timestamp with time zone, (d.doc #>> '{maintenancesite,vendor_current,display_name}'::text[]), regexp_replace(((d.doc -> 'escalation_source'::text) ->> 'name'::text), '[\n\r]+'::text, ''::text, 'g'::text), ((d.doc -> 'impact'::text) ->> 'name'::text), btrim(((d.doc -> 'alarm_cause'::text) ->> 'label'::text)), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'type_issue'::text)), d.ownerid, r.region_manager, (regexp_replace((d.last_comment).message, '[\n\r]+'::text, ''::text, 'g'::text))::character varying, CASE WHEN ((d.doc ->> 'resolution_time'::text) IS NULL) THEN 'Open Fault'::text ELSE 'Closed Fault'::text END, pentaho.map_cascaded((d.doc #> '{maintenancesite,cascaded_sites_list}'::text[])), bi.tenant_rpt_group(jsonb_array_castext_arr(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_name'::jsonpath, '{}'::jsonb, false))), (d.last_comment).created, NULL::text, d.proc_inst_id, d.task_id, (d.doc #>> '{maintenancesite,cl_attributes,ums/powersolution/pwrsolution_cat}'::text[]), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'root_cause'::text)), jsonb_array_castext_arr(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_name'::jsonpath, '{}'::jsonb, false)), NULL::text, (t.start_time_)::timestamp with time zone, ((d.doc #>> '{resolution_time}'::text[]))::timestamp with time zone, NULL::text, (d.doc #>> '{maintenancesite,site_address}'::text[]), regexp_replace((d.doc ->> 'resolution'::text), '[\n\r]+'::text, ''::text, 'g'::text), (d.doc #>> '{maintenancesite,fse_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,fse_phone}'::text[]), (d.doc #>> '{maintenancesite,sfom_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,sfom_phone}'::text[]), (d.doc #>> '{maintenancesite,rto_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,rto_phone}'::text[]), (d.doc #>> '{maintenancesite,srm_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,srm_phone}'::text[]), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == FSE)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == FSE)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == SFOM)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == SFOM)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == RTO)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == RTO)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == SRM)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.ihs_estiming[*]?(@.ro_role == SRM)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (d.doc #>> '{maintenancesite,sbc_manager_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,sbc_manager_phone}'::text[]), (d.doc #>> '{maintenancesite,sbc_cto_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,sbc_cto_phone}'::text[]), (d.doc #>> '{maintenancesite,sbc_ceo_obj,display_name}'::text[]), (d.doc #>> '{maintenancesite,sbc_ceo_phone}'::text[]), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == OD)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == OD)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == CTO)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == CTO)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == CEO)'::jsonpath, '{}'::jsonb, false) ->> 'informed'::text), (jsonb_path_query_first(d.doc, '$.maintenancesite.vendor_estiming[*]?(@.ro_role == CEO)'::jsonpath, '{}'::jsonb, false) ->> 'time'::text), NULL::timestamp with time zone, d.proc_status, (d.doc #>> '{maintenancesite,cl_attributes,ums/powersolution/pwrsolution_type}'::text[]), NULL::timestamp with time zone, (d.doc #>> '{maintenancesite,cl_attributes,ums/rmsdata/rms_type}'::text[]), NULL::text, NULL::timestamp with time zone, ((d.doc ->> 'fuel_level'::text))::numeric, CASE WHEN (array_length(pentaho.map_cascaded((d.doc #> '{maintenancesite,cascaded_sites_list}'::text[])), 1) > 0) THEN bi.cascaded_type(((d.doc -> 'maintenancesite'::text) ->> 'new_ihs_id'::text), d.task_id) ELSE NULL::text END, ((d.doc ->> 'warning_resolution_time'::text))::timestamp with time zone, (SubPlan 1), (SubPlan 2), (SubPlan 3), (t.end_time_)::timestamp with time zone, u.user_login_id, jsonb_array_castext_arr(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]'::jsonpath, '{}'::jsonb, false)), d.last_update, ((d.doc #>> '{maintenancesite,cl_attributes,ums/powersolution/pwrsolution_prod/power_date_cutover}'::text[]))::timestamp with time zone, (d.doc #>> '{maintenancesite,cl_attributes,ums/powersolution/pwrsolution_prod}'::text[]), (d.doc #>> '{maintenancesite,cl_attributes,ums/powersolution/pwrsolution_deploy}'::text[]), NULL::text, (d.doc #>> '{maintenancesite,cl_attributes,ums/add-on/externalalarmmoduleinstalled}'::text[]), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'category'::text)), ((d.doc ->> 'gen_run_hour'::text))::numeric, (d.doc #>> '{time_of_arrival,value}'::text[]), (d.doc #>> '{maintenancesite,priority}'::text[]), (d.doc #>> '{maintenancesite,siteaccess_24}'::text[]), (d.doc #>> '{maintenancesite,restriction_reason}'::text[]), (d.doc #>> '{maintenancesite,avail_access_hours}'::text[]), (d.doc ->> 'hvac'::text), (d.doc ->> 'shelter'::text), (d.doc ->> 'roof'::text), (d.doc ->> 'floor'::text), (d.doc ->> 'door'::text), ((d.doc ->> 'gen_run_hour2'::text))::numeric, (d.doc ->> 'no_of_generators_nsfm'::text), (d.doc ->> 'gateway'::text), COALESCE(btrim(((d.doc -> 'warning_alarm_cause'::text) ->> 'label'::text)), btrim(((d.doc -> 'alarm_cause'::text) ->> 'label'::text))), COALESCE(btrim((((d.doc -> 'warning_alarm_cause'::text) -> 'doc'::text) ->> 'type_issue'::text)), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'type_issue'::text))), COALESCE(btrim((((d.doc -> 'warning_alarm_cause'::text) -> 'doc'::text) ->> 'root_cause'::text)), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'root_cause'::text))), COALESCE(btrim((((d.doc -> 'warning_alarm_cause'::text) -> 'doc'::text) ->> 'category'::text)), btrim((((d.doc -> 'alarm_cause'::text) -> 'doc'::text) ->> 'category'::text))), regexp_replace((d.doc ->> 'escalation_reference'::text), '[\n\r]+'::text, ''::text, 'g'::text), regexp_replace((d.doc ->> 'warning_escalation_reference'::text), '[\n\r]+'::text, ''::text, 'g'::text), regexp_replace(((d.doc -> 'warning_escalation_source'::text) ->> 'name'::text), '[\n\r]+'::text, ''::text, 'g'::text), (d.doc ->> 'warning_gateway'::text), ((d.doc ->> 'warning_incident_time'::text))::timestamp with time zone, ((d.doc ->> 'warning_resolution_time'::text))::timestamp with time zone, regexp_replace((d.doc ->> 'warning_resolution'::text), '[\n\r]+'::text, ''::text, 'g'::text), (d.doc ->> 'fault_responsibility'::text), (d.doc ->> 'warning_fault_responsibility'::text), (d.doc ->> 'btx_rms'::text), (d.doc ->> 'btx_rms_pic'::text), (d.doc #>> '{maintenancesite,old_ihs_id}'::text[]), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_site_name'::jsonpath, '{}'::jsonb, false)), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_ticket_exist'::jsonpath, '{}'::jsonb, false)), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_ticket_type'::jsonpath, '{}'::jsonb, false)), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).tenant_ticket_number'::jsonpath, '{}'::jsonb, false)), jsonb_array_castext(jsonb_path_query_array(d.doc, '$.maintenancesite.tenants[*]?(@.selected == true).technology_failed'::jsonpath, '{}'::jsonb, false)), (SubPlan 4), (SubPlan 5), ''::text, (SubPlan 6), (d.doc ->> 'stringCapactiyValue'::text), (d.doc ->> 'dcLoadValue'::text), (d.doc ->> 'thresholdValue'::text), (SubPlan 7), CASE WHEN (((d.doc ->> 'eventType'::text) = 'OWS-TKT'::text) AND ((d.doc ->> 'operation'::text) = 'create'::text)) THEN 'Auto Create (OWS)'::text WHEN (((d.doc ->> 'eventType'::text) = 'OWS-TKT'::text) AND ((d.doc ->> 'operation'::text) = 'update'::text)) THEN 'Auto Update (OWS)'::text WHEN (((d.doc ->> 'eventType'::text) = 'RMSAlarm'::text) AND ((d.doc ->> 'isauto'::text))::boolean) THEN 'Auto Create (RMS)'::text WHEN (((d.doc ->> 'eventType'::text) = 'RMSAlarm'::text) AND (NOT (COALESCE((d.doc ->> 'isauto'::text), 'f'::text))::boolean)) THEN 'Grid Create (RMS)'::text ELSE 'Manual Ticket'::text END, r.srm_noc_manager, (d.doc ->> 'warning_to_sitedown'::text), CASE WHEN (((d.doc ->> 'warning_incident_time'::text) IS NOT NULL) AND ((d.doc ->> 'incident_time'::text) IS NOT NULL)) THEN bi.formathrs(date_trunc('second'::text, (((d.doc ->> 'incident_time'::text))::timestamp with time zone - ((d.doc ->> 'warning_incident_time'::text))::timestamp with time zone))) ELSE NULL::interval END, (d.doc ->> 'responsible_party_new'::text), gen_random_uuid(), (d.doc ->> 'mdg_deployed'::text), (d.doc ->> 'false_escalated_alarm'::text), ((d.doc ->> 'mdg_timestamp'::text))::timestamp with time zone
  • Merge Cond: ((t.owner_)::text = ((u.activiti_id)::text))
  • Buffers: shared hit=1436
3. 0.011 4,048.240 ↑ 973.0 1 1

Sort (cost=2,044.69..2,047.12 rows=973 width=701) (actual time=4,048.240..4,048.240 rows=1 loops=1)

  • Output: d.doc, d.proc_inst_id, d.task_id, d.ownerid, d.last_comment, d.proc_status, d.task_events, d.last_update, d.proc_comment, t.start_time_, t.end_time_, t.owner_, r.region_manager, r.srm_noc_manager
  • Sort Key: t.owner_
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=106
4. 0.105 4,048.229 ↑ 973.0 1 1

Hash Left Join (cost=151.67..1,996.40 rows=973 width=701) (actual time=4,048.219..4,048.229 rows=1 loops=1)

  • Output: d.doc, d.proc_inst_id, d.task_id, d.ownerid, d.last_comment, d.proc_status, d.task_events, d.last_update, d.proc_comment, t.start_time_, t.end_time_, t.owner_, r.region_manager, r.srm_noc_manager
  • Hash Cond: (((d.doc #>> '{maintenancesite,region}'::text[]) = r.region) AND (g.gr_id = (r.gr_id)::integer))
  • Buffers: shared hit=106
5. 0.004 4,048.098 ↑ 973.0 1 1

Nested Loop Left Join (cost=149.82..1,983.59 rows=973 width=677) (actual time=4,048.088..4,048.098 rows=1 loops=1)

  • Output: d.doc, d.proc_inst_id, d.task_id, d.ownerid, d.last_comment, d.proc_status, d.task_events, d.last_update, d.proc_comment, g.gr_id, t.start_time_, t.end_time_, t.owner_
  • Buffers: shared hit=95
6. 0.031 4,012.091 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.87..5.32 rows=1 width=673) (actual time=4,012.090..4,012.091 rows=1 loops=1)

  • Output: d.doc, d.proc_inst_id, d.task_id, d.ownerid, d.last_comment, d.proc_status, d.task_events, d.last_update, d.proc_comment, t.start_time_, t.end_time_, t.owner_
  • Inner Unique: true
  • Buffers: shared hit=8
7. 4,012.035 4,012.035 ↑ 1.0 1 1

Index Scan using task_draft_task_id_idx on activiti.task_draft d (cost=0.43..2.66 rows=1 width=653) (actual time=4,012.034..4,012.035 rows=1 loops=1)

  • Output: d.id, d.task_id, d.ownerid, d.doc, d.proc_inst_id, d.proc_status, d.proc_def, d.last_update, d.update_by, d.initiated_by_proc_id, d.last_comment, d.proc_comment, d.task_events, d.geom
  • Index Cond: (d.task_id = '5416242611'::bigint)
  • Filter: ((d.proc_status <> 3) AND (d.proc_def = 'NOCSiteFaultManagement'::text) AND (d.proc_status = 1))
  • Buffers: shared hit=4
8. 0.025 0.025 ↑ 1.0 1 1

Index Scan using act_idx_hi_task_id on activiti.act_hi_taskinst t (cost=0.44..2.66 rows=1 width=30) (actual time=0.025..0.025 rows=1 loops=1)

  • Output: t.id_, t.proc_def_id_, t.task_def_key_, t.proc_inst_id_, t.execution_id_, t.name_, t.parent_task_id_, t.description_, t.owner_, t.assignee_, t.start_time_, t.claim_time_, t.end_time_, t.duration_, t.delete_reason_, t.priority_, t.due_date_, t.form_key_, t.category_, t.tenant_id_
  • Index Cond: ((t.id_)::text = (d.task_id)::text)
  • Buffers: shared hit=4
9. 0.018 36.003 ↑ 973.0 1 1

Hash Join (cost=148.94..1,968.55 rows=973 width=8) (actual time=35.995..36.003 rows=1 loops=1)

  • Output: g.gr_id, g.entity_id
  • Inner Unique: true
  • Hash Cond: (g.gr_id = (((g_1.cl_attributes ->> 'country_id'::text))::integer))
  • Buffers: shared hit=87
10. 0.087 0.097 ↑ 431.2 5 1

Bitmap Heap Scan on affectli.gr_entities g (cost=25.72..1,839.61 rows=2,156 width=8) (actual time=0.090..0.097 rows=5 loops=1)

  • Output: g.id, g.ent_type, g.ent_text, g.gr_id, g.ent_permissions, g.entity_id
  • Recheck Cond: (g.entity_id = ((d.doc #>> '{maintenancesite,id_thing}'::text[]))::integer)
  • Filter: (g.ent_type = 'thing'::text)
  • Heap Blocks: exact=5
  • Buffers: shared hit=13
11. 0.010 0.010 ↑ 454.8 5 1

Bitmap Index Scan on idx_gr_entities_entity_id_2 (cost=0.00..25.19 rows=2,274 width=0) (actual time=0.010..0.010 rows=5 loops=1)

  • Index Cond: (g.entity_id = ((d.doc #>> '{maintenancesite,id_thing}'::text[]))::integer)
  • Buffers: shared hit=3
12. 0.004 35.888 ↑ 33.3 6 1

Hash (cost=120.72..120.72 rows=200 width=4) (actual time=35.888..35.888 rows=6 loops=1)

  • Output: (((g_1.cl_attributes ->> 'country_id'::text))::integer)
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=74
13. 34.403 35.884 ↑ 33.3 6 1

HashAggregate (cost=115.22..118.72 rows=200 width=4) (actual time=35.883..35.884 rows=6 loops=1)

  • Output: (((g_1.cl_attributes ->> 'country_id'::text))::integer)
  • Group Key: ((g_1.cl_attributes ->> 'country_id'::text))::integer
  • Buffers: shared hit=74
14. 1.481 1.481 ↑ 3.5 528 1

Seq Scan on affectli.groups g_1 (cost=0.00..110.65 rows=1,827 width=4) (actual time=0.022..1.481 rows=528 loops=1)

  • Output: ((g_1.cl_attributes ->> 'country_id'::text))::integer
  • Filter: ((g_1.cl_attributes ->> 'country_id'::text) IS NOT NULL)
  • Rows Removed by Filter: 1140
  • Buffers: shared hit=74
15. 0.012 0.026 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=38) (actual time=0.026..0.026 rows=34 loops=1)

  • Output: r.region_manager, r.srm_noc_manager, r.region, r.gr_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
16. 0.014 0.014 ↑ 1.0 34 1

Seq Scan on ums.region_managers r (cost=0.00..1.34 rows=34 width=38) (actual time=0.010..0.014 rows=34 loops=1)

  • Output: r.region_manager, r.srm_noc_manager, r.region, r.gr_id
  • Buffers: shared hit=1
17. 190.658 262.283 ↑ 5.1 952 1

Sort (cost=1,046.78..1,058.89 rows=4,842 width=36) (actual time=262.198..262.283 rows=952 loops=1)

  • Output: u.user_login_id, u.activiti_id, ((u.activiti_id)::text)
  • Sort Key: ((u.activiti_id)::text)
  • Sort Method: quicksort Memory: 594kB
  • Buffers: shared hit=702
18. 71.625 71.625 ↑ 1.0 4,842 1

Seq Scan on affectli.user u (cost=0.00..750.42 rows=4,842 width=36) (actual time=0.008..71.625 rows=4,842 loops=1)

  • Output: u.user_login_id, u.activiti_id, (u.activiti_id)::text
  • Buffers: shared hit=702
19.          

SubPlan (for Merge Left Join)

20. 0.005 0.015 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)

  • Output: min(CASE (((unnest(d.task_events))).event_name) WHEN 'acc_change_impact_time'::text THEN date_trunc('milliseconds'::text, (((unnest(d.task_events))).date_created)) ELSE NULL::timestamp with time zone END)
21. 0.004 0.010 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: ((unnest(d.task_events))).event_name, ((unnest(d.task_events))).date_created
22. 0.004 0.006 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.005..0.006 rows=1 loops=1)

  • Output: unnest(d.task_events)
23. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)

24. 0.004 0.008 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: min(CASE (((unnest(d.task_events))).event_name) WHEN 'incident_time'::text THEN date_trunc('milliseconds'::text, (((unnest(d.task_events))).date_created)) ELSE NULL::timestamp with time zone END)
25. 0.002 0.004 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: ((unnest(d.task_events))).event_name, ((unnest(d.task_events))).date_created
26. 0.001 0.002 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: unnest(d.task_events)
27. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.003 0.008 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: min(CASE (((unnest(d.task_events))).event_name) WHEN 'resolution_time'::text THEN date_trunc('milliseconds'::text, (((unnest(d.task_events))).date_created)) ELSE NULL::timestamp with time zone END)
29. 0.002 0.005 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: ((unnest(d.task_events))).event_name, ((unnest(d.task_events))).date_created
30. 0.002 0.003 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: unnest(d.task_events)
31. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

32. 0.049 0.175 ↑ 3.0 1 1

Result (cost=0.43..16.98 rows=3 width=32) (actual time=0.174..0.175 rows=1 loops=1)

  • Output: (thing.cl_attributes ->> 'UMS/tenantbts/Tenant_BTS_Cabinet_Technology'::text)
  • Buffers: shared hit=20
33. 0.093 0.126 ↑ 3.0 1 1

Append (cost=0.43..16.95 rows=3 width=164) (actual time=0.125..0.126 rows=1 loops=1)

  • Buffers: shared hit=17
34. 0.011 0.011 ↓ 0.0 0 1

Index Scan using ix_thing_thing_id on affectli.thing (cost=0.43..2.65 rows=1 width=405) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: thing.cl_attributes
  • Index Cond: (thing.thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
  • Buffers: shared hit=3
35. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on affectli.project (cost=0.00..11.65 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: project.cl_attributes
  • Filter: (project.thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
36. 0.016 0.016 ↑ 1.0 1 1

Index Scan using ix_sites_ihs_thing_id on affectli.sites_ihs (cost=0.42..2.64 rows=1 width=55) (actual time=0.015..0.016 rows=1 loops=1)

  • Output: sites_ihs.cl_attributes
  • Index Cond: (sites_ihs.thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
  • Buffers: shared hit=4
37. 0.049 0.049 ↑ 1.0 1 1

Index Scan using ix_sites_ihs_thing_id on affectli.sites_ihs sites_ihs_1 (cost=0.42..2.64 rows=1 width=32) (actual time=0.048..0.049 rows=1 loops=1)

  • Output: (sites_ihs_1.cl_attributes -> 'UMS/site_watch_dog/watch_dog_attributes'::text)
  • Index Cond: (sites_ihs_1.thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
  • Buffers: shared hit=7
38. 0.002 0.011 ↑ 1.0 1 1

Aggregate (cost=0.69..0.70 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: string_agg(concat('(', COALESCE(((date_trunc('seconds'::text, ((unnest(d.proc_comment))).created))::text), ''::text), ')', ' ', COALESCE(regexp_replace((((unnest(d.proc_comment))).message), '[\n\r]+'::text, ''::text, 'g'::text), ''::text)), ';'::text)
39. 0.005 0.009 ↓ 0.0 0 1

Sort (cost=0.48..0.51 rows=10 width=72) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: (((unnest(d.proc_comment))).message), ((date_trunc('seconds'::text, ((unnest(d.proc_comment))).created))::text), (((unnest(d.proc_comment))).created)
  • Sort Key: (((unnest(d.proc_comment))).created)
  • Sort Method: quicksort Memory: 25kB
40. 0.001 0.004 ↓ 0.0 0 1

Result (cost=0.00..0.32 rows=10 width=72) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: ((unnest(d.proc_comment))).message, (date_trunc('seconds'::text, ((unnest(d.proc_comment))).created))::text, ((unnest(d.proc_comment))).created
41. 0.002 0.003 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: unnest(d.proc_comment)
42. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

43. 0.005 26.360 ↑ 1.0 1 1

Aggregate (cost=39.13..39.14 rows=1 width=32) (actual time=26.360..26.360 rows=1 loops=1)

  • Output: array_agg(affectli.get_avatar_of_file((c.id)::integer, d.ownerid))
  • Buffers: shared hit=6
44. 0.002 26.355 ↓ 0.0 0 1

Bitmap Heap Scan on activiti.related_content c (cost=3.29..32.50 rows=26 width=8) (actual time=26.354..26.355 rows=0 loops=1)

  • Output: c.id, c.name, c.created, c.created_by, c.task_id, c.process_id, c.content_source, c.source_id, c.store_id, c.mime_type, c.field, c.related_content, c.link, c.link_url, c.content_available, c.locked, c.lock_date, c.lock_exp_date, c.lock_owner, c.checked_out, c.checkout_date, c.checkout_owner, c.last_modified, c.last_modified_by, c.checked_out_to_local, c.content_size, c.latitude, c.longitude
  • Recheck Cond: (((c.process_id)::text = (d.proc_inst_id)::text) OR ((c.task_id)::text = (d.task_id)::text))
  • Buffers: shared hit=6
45. 0.011 26.353 ↓ 0.0 0 1

BitmapOr (cost=3.29..3.29 rows=26 width=0) (actual time=26.353..26.353 rows=0 loops=1)

  • Buffers: shared hit=6
46. 26.331 26.331 ↓ 0.0 0 1

Bitmap Index Scan on idx_relcont_procid (cost=0.00..1.67 rows=17 width=0) (actual time=26.331..26.331 rows=0 loops=1)

  • Index Cond: ((c.process_id)::text = (d.proc_inst_id)::text)
  • Buffers: shared hit=3
47. 0.011 0.011 ↓ 0.0 0 1

Bitmap Index Scan on idx_relcont_taskid (cost=0.00..1.61 rows=10 width=0) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((c.task_id)::text = (d.task_id)::text)
  • Buffers: shared hit=3Settings: effective_cache_size = '23808MB', effective_io_concurrency = '200', max_parallel_workers = '4', random_page_cost = '1.1', work_mem = '6772kB'
Execution time : 5,007.269 ms