explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cPq2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=752,363.91..760,252.87 rows=98,612 width=4,401) (actual rows= loops=)

  • Output: wip.batch_id, pf.inquiry_id, gr.response_cd, gbu.rdc_firm_nbr, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, (COALESCE(pf.concatname_u, (pf.concatname)::text)), pf.concatname_u, (CASE WHEN (pf.business_name IS NULL) THEN 'P'::text ELSE 'O'::text END), pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, al.cvip_code, (to_char(al.alert_dt, 'YYYY-MM-DD HH:MI:SS'::text)), wip.submitted_ts, pf.taxid, pf.inquiry_notes, (CASE WHEN ((pf.pf_status)::text = 'ACT'::text) THEN pf.look_back ELSE 'N'::character varying END), pf.record_in_dt, pf.erase_dt
2.          

CTE gbu

3. 0.000 0.000 ↓ 0.0

Index Scan using ix_grid_biz_unit_idx_grid_biz_unit on dbo.grid_biz_unit (cost=0.28..8.30 rows=1 width=13) (actual rows= loops=)

  • Output: grid_biz_unit.rdc_firm_nbr, grid_biz_unit.biz_unit_id
  • Index Cond: (grid_biz_unit.biz_unit_id = 101,389)
4.          

CTE grid_batch

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.45..8,997.42 rows=3,331 width=20) (actual rows= loops=)

  • Output: grid_batch_1.biz_unit_id, grid_batch_1.batch_id, COALESCE(grid_batch_1.pf_batch_id, grid_batch_1.batch_id)
6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual rows= loops=)

  • Output: gbu_1.biz_unit_id
  • Group Key: gbu_1.biz_unit_id
7. 0.000 0.000 ↓ 0.0

CTE Scan on gbu gbu_1 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

  • Output: gbu_1.rdc_firm_nbr, gbu_1.biz_unit_id
8. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_grid_batch_idx_grid_batch_biz_unit_batch_id on dbo.grid_batch grid_batch_1 (cost=0.43..8,964.08 rows=3,331 width=20) (actual rows= loops=)

  • Output: grid_batch_1.biz_unit_id, grid_batch_1.created_dt, grid_batch_1.batch_id, grid_batch_1.pf_batch_id
  • Index Cond: (grid_batch_1.biz_unit_id = gbu_1.biz_unit_id)
9.          

CTE pf

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=75.38..48,116.24 rows=1,160,137 width=588) (actual rows= loops=)

  • Output: pf_1.inquiry_id, pf_1.association_id, pf_1.batch_id, pf_1.record_in_dt, pf_1.forward_look_process, pf_1.look_back, pf_1.branch_nbr, pf_1.acct_nbr, pf_1.acct_type, pf_1.name_type, pf_1.concatname, pf_1.last_name, pf_1.first_name, pf_1.middle_name, pf_1.suffix, pf_1.prefix, pf_1.title, pf_1.business_name, pf_1.delivery_line, pf_1.city_name, pf_1.postal_cd, pf_1.urbanization, pf_1.province, pf_1.province_cd, pf_1.country, pf_1.country_cd, pf_1.dt_of_birth, pf_1.age, pf_1.id_type, pf_1.taxid, pf_1.telephone_type, pf_1.telephone_nbr, pf_1.user_name, pf_1.batch_seq_nbr, pf_1.global_search, pf_1.inquiry_notes, pf_1.last_mod_user, pf_1.last_mod_dt, pf_1.pf_status, pf_1.last_name_u, pf_1.first_name_u, pf_1.middle_name_u, pf_1.business_name_u, pf_1.concatname_u, pf_1.delivery_line_u, pf_1.city_name_u, pf_1.urbanization_u, pf_1.province_u, pf_1.country_u, pf_1.latitude, pf_1.longitude, pf_1.sex, pf_1.erase_dt, pf_1.action_type, pf_1.search_action_if_duplicate, pf_1.pm_action_if_duplicate
11. 0.000 0.000 ↓ 0.0

HashAggregate (cost=74.95..76.95 rows=200 width=8) (actual rows= loops=)

  • Output: grid_batch_2.c_batch_id
  • Group Key: grid_batch_2.c_batch_id
12. 0.000 0.000 ↓ 0.0

CTE Scan on grid_batch grid_batch_2 (cost=0.00..66.62 rows=3,331 width=8) (actual rows= loops=)

  • Output: grid_batch_2.biz_unit_id, grid_batch_2.batch_id, grid_batch_2.c_batch_id
13. 0.000 0.000 ↓ 0.0

Index Scan using pf_101389_idx1 on dbo.grid_pf_101389 pf_1 (cost=0.43..238.38 rows=182 width=588) (actual rows= loops=)

  • Output: pf_1.inquiry_id, pf_1.association_id, pf_1.batch_id, pf_1.record_in_dt, pf_1.forward_look_process, pf_1.look_back, pf_1.branch_nbr, pf_1.acct_nbr, pf_1.acct_type, pf_1.name_type, pf_1.concatname, pf_1.last_name, pf_1.first_name, pf_1.middle_name, pf_1.suffix, pf_1.prefix, pf_1.title, pf_1.business_name, pf_1.delivery_line, pf_1.city_name, pf_1.postal_cd, pf_1.urbanization, pf_1.province, pf_1.province_cd, pf_1.country, pf_1.country_cd, pf_1.dt_of_birth, pf_1.age, pf_1.id_type, pf_1.taxid, pf_1.telephone_type, pf_1.telephone_nbr, pf_1.user_name, pf_1.batch_seq_nbr, pf_1.global_search, pf_1.inquiry_notes, pf_1.last_mod_user, pf_1.last_mod_dt, pf_1.pf_status, pf_1.last_name_u, pf_1.first_name_u, pf_1.middle_name_u, pf_1.business_name_u, pf_1.concatname_u, pf_1.delivery_line_u, pf_1.city_name_u, pf_1.urbanization_u, pf_1.province_u, pf_1.country_u, pf_1.latitude, pf_1.longitude, pf_1.sex, pf_1.erase_dt, pf_1.action_type, pf_1.search_action_if_duplicate, pf_1.pm_action_if_duplicate
  • Index Cond: (pf_1.batch_id = grid_batch_2.c_batch_id)
  • Filter: ((pf_1.pf_status)::text <> 'PRG'::text)
14.          

CTE al

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=900.36..144,424.62 rows=17,127 width=62) (actual rows= loops=)

  • Output: (al_2.inq_id)::integer, al_2.batch_id, (SubPlan 4), al_2.history, max(al_2.alert_dt), al_2.inq_id, al_2.cvip_code
  • Group Key: al_2.inq_id, al_2.batch_id, al_2.cvip_code, al_2.history
16. 0.000 0.000 ↓ 0.0

Seq Scan on dbo.alert_101389 al_2 (cost=0.00..686.27 rows=17,127 width=26) (actual rows= loops=)

  • Output: al_2.alert_pk_id, al_2.alert_dt, al_2.alert_tracking_id, al_2.link, al_2.full_abstract, al_2.batch_id, al_2.inq_id, al_2.name, al_2.id, al_2.cvip_code, al_2.type, al_2.history
  • Filter: (NOT al_2.history)
17.          

SubPlan (for HashAggregate)

18. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.36..8.37 rows=1 width=32) (actual rows= loops=)

  • Output: string_agg((al_1.cvip_code)::text, ', '::text)
19. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8.32..8.34 rows=1 width=26) (actual rows= loops=)

  • Output: al_1.inq_id, al_1.batch_id, al_1.cvip_code, al_1.history, NULL::timestamp without time zone
  • Group Key: al_1.inq_id, al_1.batch_id, al_1.cvip_code, al_1.history
20. 0.000 0.000 ↓ 0.0

Sort (cost=8.32..8.32 rows=1 width=18) (actual rows= loops=)

  • Output: al_1.inq_id, al_1.batch_id, al_1.cvip_code, al_1.history
  • Sort Key: al_1.cvip_code, al_1.history
21. 0.000 0.000 ↓ 0.0

Index Scan using alert_idx4_101389 on dbo.alert_101389 al_1 (cost=0.29..8.31 rows=1 width=18) (actual rows= loops=)

  • Output: al_1.inq_id, al_1.batch_id, al_1.cvip_code, al_1.history
  • Index Cond: ((al_1.batch_id = al_2.batch_id) AND (al_1.inq_id = al_2.inq_id))
  • Filter: (NOT al_1.history)
22. 0.000 0.000 ↓ 0.0

Sort (cost=550,817.34..551,063.87 rows=98,612 width=4,401) (actual rows= loops=)

  • Output: wip.batch_id, pf.inquiry_id, gr.response_cd, gbu.rdc_firm_nbr, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, (COALESCE(pf.concatname_u, (pf.concatname)::text)), pf.concatname_u, (CASE WHEN (pf.business_name IS NULL) THEN 'P'::text ELSE 'O'::text END), pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, al.cvip_code, (to_char(al.alert_dt, 'YYYY-MM-DD HH:MI:SS'::text)), wip.submitted_ts, pf.taxid, pf.inquiry_notes, (CASE WHEN ((pf.pf_status)::text = 'ACT'::text) THEN pf.look_back ELSE 'N'::character varying END), pf.record_in_dt, pf.erase_dt
  • Sort Key: wip.batch_id, pf.inquiry_id, gr.response_cd, gbu.rdc_firm_nbr, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, (COALESCE(pf.concatname_u, (pf.concatname)::text)), pf.concatname_u, (CASE WHEN (pf.business_name IS NULL) THEN 'P'::text ELSE 'O'::text END), pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, al.cvip_code, (to_char(al.alert_dt, 'YYYY-MM-DD HH:MI:SS'::text)), wip.submitted_ts, pf.taxid, pf.inquiry_notes, (CASE WHEN ((pf.pf_status)::text = 'ACT'::text) THEN pf.look_back ELSE 'N'::character varying END), pf.record_in_dt, pf.erase_dt
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=31,625.96..542,637.73 rows=98,612 width=4,401) (actual rows= loops=)

  • Output: wip.batch_id, pf.inquiry_id, gr.response_cd, gbu.rdc_firm_nbr, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, COALESCE(pf.concatname_u, (pf.concatname)::text), pf.concatname_u, CASE WHEN (pf.business_name IS NULL) THEN 'P'::text ELSE 'O'::text END, pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, al.cvip_code, to_char(al.alert_dt, 'YYYY-MM-DD HH:MI:SS'::text), wip.submitted_ts, pf.taxid, pf.inquiry_notes, CASE WHEN ((pf.pf_status)::text = 'ACT'::text) THEN pf.look_back ELSE 'N'::character varying END, pf.record_in_dt, pf.erase_dt
  • Filter: ((NOT gr.batch_reset) OR (gr.batch_reset IS NULL))
24. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=31,625.39..32,096.41 rows=98,612 width=4,822) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, wip.batch_id, wip.submitted_ts, pf.inquiry_id, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, pf.concatname_u, pf.concatname, pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, pf.taxid, pf.inquiry_notes, pf.pf_status, pf.look_back, pf.record_in_dt, pf.erase_dt, al.cvip_code, al.alert_dt
  • Hash Cond: ((al.inq_id = pf.inquiry_id) AND (al.batch_id = wip.batch_id))
25. 0.000 0.000 ↓ 0.0

CTE Scan on al (cost=0.00..342.54 rows=17,127 width=52) (actual rows= loops=)

  • Output: al.inq_id, al.batch_id, al.cvip_code, al.history, al.alert_dt
26. 0.000 0.000 ↓ 0.0

Hash (cost=30,146.21..30,146.21 rows=98,612 width=4,782) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, wip.batch_id, wip.submitted_ts, pf.inquiry_id, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, pf.concatname_u, pf.concatname, pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, pf.taxid, pf.inquiry_notes, pf.pf_status, pf.look_back, pf.record_in_dt, pf.erase_dt
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=156.67..30,146.21 rows=98,612 width=4,782) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, wip.batch_id, wip.submitted_ts, pf.inquiry_id, pf.acct_nbr, pf.branch_nbr, pf.user_name, pf.forward_look_process, pf.business_name, pf.first_name, pf.last_name, pf.middle_name, pf.concatname_u, pf.concatname, pf.city_name, pf.province, pf.country, pf.delivery_line, pf.postal_cd, pf.age, pf.dt_of_birth, pf.global_search, pf.taxid, pf.inquiry_notes, pf.pf_status, pf.look_back, pf.record_in_dt, pf.erase_dt
  • Hash Cond: (pf.batch_id = grid_batch.c_batch_id)
28. 0.000 0.000 ↓ 0.0

CTE Scan on pf (cost=0.00..23,202.74 rows=1,160,137 width=4,716) (actual rows= loops=)

  • Output: pf.inquiry_id, pf.association_id, pf.batch_id, pf.record_in_dt, pf.forward_look_process, pf.look_back, pf.branch_nbr, pf.acct_nbr, pf.acct_type, pf.name_type, pf.concatname, pf.last_name, pf.first_name, pf.middle_name, pf.suffix, pf.prefix, pf.title, pf.business_name, pf.delivery_line, pf.city_name, pf.postal_cd, pf.urbanization, pf.province, pf.province_cd, pf.country, pf.country_cd, pf.dt_of_birth, pf.age, pf.id_type, pf.taxid, pf.telephone_type, pf.telephone_nbr, pf.user_name, pf.batch_seq_nbr, pf.global_search, pf.inquiry_notes, pf.last_mod_user, pf.last_mod_dt, pf.pf_status, pf.last_name_u, pf.first_name_u, pf.middle_name_u, pf.business_name_u, pf.concatname_u, pf.delivery_line_u, pf.city_name_u, pf.urbanization_u, pf.province_u, pf.country_u, pf.latitude, pf.longitude, pf.sex, pf.erase_dt, pf.action_type, pf.search_action_if_duplicate, pf.pm_action_if_duplicate
29. 0.000 0.000 ↓ 0.0

Hash (cost=156.46..156.46 rows=17 width=82) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, grid_batch.c_batch_id, wip.batch_id, wip.submitted_ts
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.59..156.46 rows=17 width=82) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, grid_batch.c_batch_id, wip.batch_id, wip.submitted_ts
  • Inner Unique: true
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..79.31 rows=17 width=74) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, grid_batch.batch_id, grid_batch.c_batch_id
  • Hash Cond: (grid_batch.biz_unit_id = gbu.biz_unit_id)
32. 0.000 0.000 ↓ 0.0

CTE Scan on grid_batch (cost=0.00..66.62 rows=3,331 width=20) (actual rows= loops=)

  • Output: grid_batch.biz_unit_id, grid_batch.batch_id, grid_batch.c_batch_id
33. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=62) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, gbu.biz_unit_id
34. 0.000 0.000 ↓ 0.0

CTE Scan on gbu (cost=0.00..0.02 rows=1 width=62) (actual rows= loops=)

  • Output: gbu.rdc_firm_nbr, gbu.biz_unit_id
35. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_grid_wip_idx_batch_id_with_included_columns on dbo.grid_wip wip (cost=0.56..4.54 rows=1 width=16) (actual rows= loops=)

  • Output: wip.batch_id, wip.inq_cnt, wip.batch_type, wip.submitted_ts, wip.completed_ts, wip.search_type, wip.bal_ts, wip.irf_ts, wip.urf_ts, wip.kyc_submitted_cnt, wip.kyc_processed_cnt, wip.kyc_accepted_cnt, wip.kyc_reviewed_cnt, wip.kyc_submitted_ts, wip.kyc_processed_ts, wip.search_processed_cnt, wip.load_only_inquiry_count, wip.kyc_submitted_grid_match_cnt, wip.alert_grid_match_cnt, wip.alert_cnt, wip.purge_ts
  • Index Cond: (wip.batch_id = grid_batch.batch_id)
  • Filter: (wip.inq_cnt >= 0)
36. 0.000 0.000 ↓ 0.0

Index Scan using ix_grid_response_response_idx5 on dbo.grid_response gr (cost=0.57..5.16 rows=1 width=16) (actual rows= loops=)

  • Output: gr.grid_response_id, gr.batch_id, gr.response_cd, gr.batch_seq_nbr, gr.inquiry_id, gr.batch_reset
  • Index Cond: ((wip.batch_id = gr.batch_id) AND (pf.inquiry_id = gr.inquiry_id))