explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZLhl : slow_query

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

Merge Left Join (cost=193,130.32..193,424.98 rows=14 width=13,279) (actual time=1.248..1.248 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_tender.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_tender.source_system_cd)::text)
2. 0.001 1.248 ↓ 0.0 0 1

Merge Left Join (cost=193,127.95..193,422.56 rows=14 width=12,601) (actual time=1.248..1.248 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_product.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_product.source_system_cd)::text)
3. 0.000 1.247 ↓ 0.0 0 1

Merge Left Join (cost=193,125.57..193,420.13 rows=14 width=12,283) (actual time=1.247..1.247 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_preference.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_preference.source_system_cd)::text)
4. 0.000 1.247 ↓ 0.0 0 1

Merge Left Join (cost=193,123.20..193,417.70 rows=14 width=11,927) (actual time=1.247..1.247 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_notice.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_notice.source_system_cd)::text)
5. 0.001 1.247 ↓ 0.0 0 1

Merge Left Join (cost=193,120.83..193,415.28 rows=14 width=10,933) (actual time=1.247..1.247 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_event.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_event.source_system_cd)::text)
6. 0.000 1.246 ↓ 0.0 0 1

Merge Left Join (cost=193,118.46..193,412.85 rows=14 width=9,989) (actual time=1.246..1.246 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_employer_geocode.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_employer_geocode.source_system_cd)::text)
7. 0.000 1.246 ↓ 0.0 0 1

Merge Left Join (cost=193,116.09..193,410.42 rows=14 width=9,503) (actual time=1.246..1.246 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_employer_address.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_employer_address.source_system_cd)::text)
8. 0.006 1.246 ↓ 0.0 0 1

Merge Left Join (cost=193,113.72..193,408.00 rows=14 width=8,443) (actual time=1.246..1.246 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_device.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_device.source_system_cd)::text)
9. 0.000 1.240 ↓ 0.0 0 1

Merge Left Join (cost=193,111.35..193,405.57 rows=14 width=7,295) (actual time=1.240..1.240 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = (customer_address_geocode.source_customer_id)::text)
  • Join Filter: ((customer_master.source_system_cd)::text = (customer_address_geocode.source_system_cd)::text)
10. 0.001 1.240 ↓ 0.0 0 1

Nested Loop Left Join (cost=193,108.98..193,403.15 rows=14 width=6,809) (actual time=1.240..1.240 rows=0 loops=1)

11. 0.000 1.239 ↓ 0.0 0 1

Nested Loop Left Join (cost=193,108.41..193,345.15 rows=14 width=6,479) (actual time=1.239..1.239 rows=0 loops=1)

12. 0.002 1.239 ↓ 0.0 0 1

Merge Left Join (cost=193,107.85..193,306.12 rows=14 width=6,284) (actual time=1.239..1.239 rows=0 loops=1)

  • Merge Cond: ((customer_master.source_customer_id)::text = ((cm1.omni_customer_id)::text))
13. 0.000 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.95..202.09 rows=14 width=4,724) (actual time=1.237..1.237 rows=0 loops=1)

14. 0.000 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.38..130.49 rows=9 width=4,669) (actual time=1.237..1.237 rows=0 loops=1)

15. 0.000 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.82..51.73 rows=3 width=4,151) (actual time=1.237..1.237 rows=0 loops=1)

16. 0.000 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.25..12.49 rows=1 width=3,689) (actual time=1.237..1.237 rows=0 loops=1)

17. 0.000 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.82..10.52 rows=1 width=3,421) (actual time=1.237..1.237 rows=0 loops=1)

18. 0.002 1.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.69..9.90 rows=1 width=523) (actual time=1.237..1.237 rows=0 loops=1)

19. 0.000 1.235 ↓ 0.0 0 1

Nested Loop (cost=1.12..5.59 rows=1 width=415) (actual time=1.235..1.235 rows=0 loops=1)

20. 1.235 1.235 ↓ 0.0 0 1

Index Scan using customer_ssn_idx2 on customer_ssn (cost=0.56..2.79 rows=1 width=49) (actual time=1.235..1.235 rows=0 loops=1)

  • Index Cond: (((ssn_encrypted)::text = '383081001'::text) AND ((source_system_cd)::text = 'OMNI'::text) AND (source_deleted_flg = 'N'::bpchar (...)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_master_pk on customer_master (cost=0.56..2.79 rows=1 width=381) (never executed)

  • Index Cond: (((source_system_cd)::text = 'OMNI'::text) AND ((source_customer_id)::text = (customer_ssn.source_customer_id)::text))
  • Filter: ((source_deleted_flg = 'N'::bpchar) AND (migration_flg = 'N'::bpchar) AND (omni_master_record_flg = 'Y'::bpchar) AND (dob = to_date( (...)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_address_pk on customer_address (cost=0.56..4.28 rows=3 width=123) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_ma (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_communication_pk_idx on customer_communication (cost=0.14..0.61 rows=1 width=3,074) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.s (...)
  • Filter: ((source_product_id IS NULL) AND (source_deleted_flg = 'N'::bpchar))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_denial_a_pk_idx on customer_denial (cost=0.42..1.96 rows=1 width=286) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_ (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_reference_a_pk_idx on customer_reference (cost=0.57..38.58 rows=66 width=478) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_custom (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_contact_idx2 on customer_contact (cost=0.57..25.97 rows=29 width=533) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_customer_id) (...)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_banking_a_pk_qa_only on customer_banking (cost=0.56..7.89 rows=7 width=70) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_customer_id)::text (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
28. 0.000 0.000 ↓ 0.0 0

Sort (cost=193,103.90..193,103.91 rows=1 width=1,564) (never executed)

  • Sort Key: ((cm1.omni_customer_id)::text)
29. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=192,897.78..193,103.89 rows=1 width=1,564) (never executed)

  • Merge Cond: (((cd1.source_system_cd)::text = (cm1.source_system_cd)::text) AND ((cd1.source_customer_id)::text = (cm1.source_customer_id)::text))
30. 0.000 0.000 ↓ 0.0 0

Sort (cost=185,566.16..185,619.47 rows=21,326 width=1,560) (never executed)

  • Sort Key: cd1.source_system_cd, cd1.source_customer_id
31. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..175,578.58 rows=21,326 width=1,560) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
32. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on customer_document cd1 (cost=0.00..172,445.98 rows=8,886 width=1,560) (never executed)

  • Filter: (source_deleted_flg = 'N'::bpchar)
33. 0.000 0.000 ↓ 0.0 0

Sort (cost=7,331.63..7,347.01 rows=6,154 width=20) (never executed)

  • Sort Key: cm1.source_system_cd, cm1.source_customer_id
34. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_master_a_idx_dob on customer_master cm1 (cost=0.56..6,944.31 rows=6,154 width=20) (never executed)

  • Index Cond: ((dob = to_date('1980-01-01'::character varying, 'YYYY-MM-DD'::character varying)) AND (omni_master_record_flg = 'N'::bpchar))
  • Filter: (source_deleted_flg = 'N'::bpchar)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_identity_pk on customer_identity (cost=0.56..2.78 rows=1 width=210) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_customer_id)::text = (source_c (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_income_a_pk_idx on customer_income (cost=0.56..4.11 rows=3 width=346) (never executed)

  • Index Cond: (((customer_master.source_system_cd)::text = (source_system_cd)::text) AND ((source_system_cd)::text = 'OMNI'::text) AND ((customer_master.source_customer_id)::text = (source_custome (...)
  • Filter: (source_deleted_flg = 'N'::bpchar)
37. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=662) (never executed)

  • Sort Key: customer_address_geocode.source_customer_id
38. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_address_geocode_pk_idx on customer_address_geocode (cost=0.14..2.36 rows=1 width=662) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
39. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=1,324) (never executed)

  • Sort Key: customer_device.source_customer_id
40. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_device_pk_idx on customer_device (cost=0.14..2.36 rows=1 width=1,324) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
41. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=1,236) (never executed)

  • Sort Key: customer_employer_address.source_customer_id
42. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_employer_address_pk_idx on customer_employer_address (cost=0.14..2.36 rows=1 width=1,236) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
43. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=662) (never executed)

  • Sort Key: customer_employer_geocode.source_customer_id
44. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_employer_geocode_pk_idx on customer_employer_geocode (cost=0.14..2.36 rows=1 width=662) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
45. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=1,120) (never executed)

  • Sort Key: customer_event.source_customer_id
46. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_event_pk_idx on customer_event (cost=0.14..2.36 rows=1 width=1,120) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
47. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=1,170) (never executed)

  • Sort Key: customer_notice.source_customer_id
48. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_notice_pk_idx on customer_notice (cost=0.14..2.36 rows=1 width=1,170) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
49. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=532) (never executed)

  • Sort Key: customer_preference.source_customer_id
50. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_preference_pk_idx on customer_preference (cost=0.14..2.36 rows=1 width=532) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
51. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=494) (never executed)

  • Sort Key: customer_product.source_customer_id
52. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_product_pk_idx on customer_product (cost=0.14..2.36 rows=1 width=494) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
53. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.37..2.38 rows=1 width=854) (never executed)

  • Sort Key: customer_tender.source_customer_id
54. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_tender_pk_idx on customer_tender (cost=0.14..2.36 rows=1 width=854) (never executed)

  • Index Cond: ((source_system_cd)::text = 'OMNI'::text)
  • Filter: (source_deleted_flg = 'N'::bpchar)
Planning time : 50.644 ms
Execution time : 2.670 ms