explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6oZ : Optimization for: Optimization for: plan #EQwZ; plan #vT7b

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.007 0.360 ↑ 1,048.3 6 1

Unique (cost=13,770.62..14,745.57 rows=6,290 width=526) (actual time=0.354..0.360 rows=6 loops=1)

2. 0.065 0.353 ↑ 1,048.3 6 1

Sort (cost=13,770.62..13,786.35 rows=6,290 width=526) (actual time=0.353..0.353 rows=6 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: 28kB
3. 0.005 0.288 ↑ 1,048.3 6 1

Nested Loop Left Join (cost=39.48..13,373.76 rows=6,290 width=526) (actual time=0.192..0.288 rows=6 loops=1)

4. 0.009 0.223 ↑ 1,221.5 2 1

Hash Join (cost=39.05..11,870.38 rows=2,443 width=376) (actual time=0.165..0.223 rows=2 loops=1)

  • Hash Cond: (identifica1_.identity_type_id = identityty2_.identity_type_id)
5. 0.005 0.206 ↑ 1,221.5 2 1

Nested Loop (cost=37.78..11,835.52 rows=2,443 width=380) (actual time=0.149..0.206 rows=2 loops=1)

6. 0.024 0.111 ↑ 1,221.5 2 1

Bitmap Heap Scan on mst_pm_person_identification identifica1_ (cost=37.36..3,268.06 rows=2,443 width=12) (actual time=0.100..0.111 rows=2 loops=1)

  • Recheck Cond: (upper((identity_reference)::text) = '900519025268'::text)
  • Heap Blocks: exact=2
7. 0.087 0.087 ↑ 1,221.5 2 1

Bitmap Index Scan on mst_pm_person_identification_upper_identity_reference_idx (cost=0.00..36.75 rows=2,443 width=0) (actual time=0.087..0.087 rows=2 loops=1)

  • Index Cond: (upper((identity_reference)::text) = '900519025268'::text)
8. 0.090 0.090 ↑ 1.0 1 2

Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient patient0_ (cost=0.42..3.50 rows=1 width=376) (actual time=0.044..0.045 rows=1 loops=2)

  • Index Cond: (patient_id = identifica1_.patient_id)
9. 0.002 0.008 ↑ 1.0 12 1

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

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

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

11. 0.060 0.060 ↑ 1.0 3 2

Index Scan using mst_pm_contact_number_patient_id_idx on mst_pm_contact_number contactnum3_ (cost=0.42..0.59 rows=3 width=150) (actual time=0.029..0.030 rows=3 loops=2)

  • Index Cond: (patient0_.patient_id = patient_id)
Planning time : 1.438 ms
Execution time : 0.496 ms