explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2hm3 : Optimization for: plan #j891p

Settings

Optimization path:

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

Unique (cost=243.62..245.94 rows=15 width=506) (actual time=0.321..0.321 rows=0 loops=1)

2. 0.125 0.321 ↓ 0.0 0 1

Sort (cost=243.62..243.65 rows=15 width=506) (actual time=0.321..0.321 rows=0 loops=1)

  • Sort Key: patient0_.name, contactnum3_.id, patient0_.patient_id, patient0_.created_at, patient0_.created_by, patient0_.migrated_from, patient0_.is_read_only, patient0_.is_sync, patient0_.updated_at, patient0_.updated_by, patient0_.version_id, patient0_.alias_name, patient0_.clinic_id, patient0_.date_of_birth, patient0_.date_of_marriage, patient0_.education_level_education_level_id, patient0_.email_address, patient0_.ethnic_group_ethnic_group_id, patient0_.ethnic_sub_group_sub_ethnic_group_id, patient0_.gender_gender_id, patient0_.government_staff, patient0_.has_pr, patient0_.house_hold_account_id, patient0_.is_baby, patient0_.is_dob_estimated, patient0_.is_malaysian_citizen, patient0_.is_pensioner, patient0_.is_temporary_registration, patient0_.is_unverified_registration, patient0_.linked_type, patient0_.marital_status_marital_status_id, patient0_.moh_staff, patient0_.my_hix_global_id, patient0_.nationality, patient0_.no_contact_number, patient0_.occupation_occupation_id, patient0_.patient_status, patient0_.pc_id, patient0_.pc_id_status, patient0_.pensioner_number, patient0_.pmi_number, patient0_.profile_id, patient0_.registration_date_time, patient0_.religion_religion_id, patient0_.remark, patient0_.temp_pc_id, contactnum3_.created_at, contactnum3_.created_by, contactnum3_.migrated_from, contactnum3_.is_read_only, contactnum3_.is_sync, contactnum3_.updated_at, contactnum3_.updated_by, contactnum3_.version_id, contactnum3_.contact_number, contactnum3_.contact_number_type, contactnum3_.country_code, contactnum3_.default_contact_number, contactnum3_.patient_id
  • Sort Method: quicksort Memory: 25kB
3. 0.023 0.196 ↓ 0.0 0 1

Hash Join (cost=148.18..243.32 rows=15 width=506) (actual time=0.196..0.196 rows=0 loops=1)

  • Hash Cond: (identifica1_.identity_type_id = identityty2_.identity_type_id)
4. 0.015 0.149 ↓ 0.0 0 1

Hash Right Join (cost=146.91..241.85 rows=15 width=510) (actual time=0.149..0.149 rows=0 loops=1)

  • Hash Cond: (contactnum3_.patient_id = patient0_.patient_id)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on mst_pm_contact_number contactnum3_ (cost=0.00..84.77 rows=2,677 width=151) (never executed)

6. 0.001 0.134 ↓ 0.0 0 1

Hash (cost=146.72..146.72 rows=15 width=359) (actual time=0.134..0.134 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
7. 0.000 0.133 ↓ 0.0 0 1

Nested Loop (cost=4.69..146.72 rows=15 width=359) (actual time=0.133..0.133 rows=0 loops=1)

8. 0.008 0.133 ↓ 0.0 0 1

Bitmap Heap Scan on mst_pm_person_identification identifica1_ (cost=4.41..41.76 rows=16 width=12) (actual time=0.133..0.133 rows=0 loops=1)

  • Recheck Cond: (upper((identity_reference)::text) = '111111111111'::text)
9. 0.125 0.125 ↓ 0.0 0 1

Bitmap Index Scan on mst_pm_person_identification_upper_identity_reference_idx (cost=0.00..4.40 rows=16 width=0) (actual time=0.125..0.125 rows=0 loops=1)

  • Index Cond: (upper((identity_reference)::text) = '111111111111'::text)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient patient0_ (cost=0.28..6.55 rows=1 width=355) (never executed)

  • Index Cond: (patient_id = identifica1_.patient_id)
11. 0.008 0.024 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=4) (actual time=0.024..0.024 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
12. 0.016 0.016 ↑ 1.0 12 1

Seq Scan on ref_co_identity_type identityty2_ (cost=0.00..1.12 rows=12 width=4) (actual time=0.012..0.016 rows=12 loops=1)

Planning time : 2.740 ms
Execution time : 0.713 ms