explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OQu1

Settings
# exclusive inclusive rows x rows loops node
1. 792.511 43,721.844 ↑ 1.1 637,032 1

Group (cost=853,467.16..927,791.04 rows=725,111 width=545) (actual time=37,215.189..43,721.844 rows=637,032 loops=1)

  • Group Key: p.pc_id, p.name, p.created_at, (CASE WHEN (pid.identity_type_name IS NOT NULL) THEN pid.identity_type_name ELSE 'N/A'::character varying END), (CASE WHEN (pid.identity_reference IS NOT NULL) THEN pid.identity_reference ELSE 'N/A'::character varying END), p.date_of_birth, g.gender_name, (CASE WHEN (p.is_malaysian_citizen IS TRUE) THEN 'Citizen'::text WHEN (p.has_pr IS TRUE) THEN 'PR'::text ELSE 'Foreigner'::text END), et.ethnic_group_name, (CASE WHEN (((pa.address1 IS NOT NULL) AND (btrim((pa.address1)::text) <> ''::text)) OR ((pa.address2 IS NOT NULL) AND (btrim((pa.address2)::text) <> ''::text)) OR ((pa.address3 IS NOT NULL) AND (btrim((pa.address3)::text) <> ''::text))) THEN concat(pa.address1, pa.address2, pa.address3) ELSE 'N/A'::text END), pa.postal_code, pd.district_name, ps.state_name, pci.homecontactno, pci.officecontactno, pci.mobilecontactno, p.email_address, p.government_staff, (CASE WHEN ((egl.patient_id IS NOT NULL) AND (p.government_staff IS FALSE)) THEN 'Yes'::text ELSE 'No'::text END), p.is_pensioner, pnk.name, pnk.ic_no, sl.simple_list_config_name, (CASE WHEN (pnk.contact_no IS NOT NULL) THEN concat(pnk.country_code, pnk.contact_no) ELSE 'N/A'::text END), dv.visit_date_time, f.facility_name, ppcd.patientprofilecategory, pvad.visitageinyears, pvad.visitagerange, ds.service_name, bdt.visit_encounter_id, au.personnel_name
2. 24,761.569 42,929.333 ↓ 1.2 862,607 1

Sort (cost=853,467.16..855,279.94 rows=725,111 width=545) (actual time=37,215.184..42,929.333 rows=862,607 loops=1)

  • Sort Key: p.pc_id, p.name, p.created_at, (CASE WHEN (pid.identity_type_name IS NOT NULL) THEN pid.identity_type_name ELSE 'N/A'::character varying END), (CASE WHEN (pid.identity_reference IS NOT NULL) THEN pid.identity_reference ELSE 'N/A'::character varying END), p.date_of_birth, g.gender_name, (CASE WHEN (p.is_malaysian_citizen IS TRUE) THEN 'Citizen'::text WHEN (p.has_pr IS TRUE) THEN 'PR'::text ELSE 'Foreigner'::text END), et.ethnic_group_name, (CASE WHEN (((pa.address1 IS NOT NULL) AND (btrim((pa.address1)::text) <> ''::text)) OR ((pa.address2 IS NOT NULL) AND (btrim((pa.address2)::text) <> ''::text)) OR ((pa.address3 IS NOT NULL) AND (btrim((pa.address3)::text) <> ''::text))) THEN concat(pa.address1, pa.address2, pa.address3) ELSE 'N/A'::text END), pa.postal_code, pd.district_name, ps.state_name, pci.homecontactno, pci.officecontactno, pci.mobilecontactno, p.email_address, p.government_staff, (CASE WHEN ((egl.patient_id IS NOT NULL) AND (p.government_staff IS FALSE)) THEN 'Yes'::text ELSE 'No'::text END), p.is_pensioner, pnk.name, pnk.ic_no, sl.simple_list_config_name, (CASE WHEN (pnk.contact_no IS NOT NULL) THEN concat(pnk.country_code, pnk.contact_no) ELSE 'N/A'::text END), dv.visit_date_time, f.facility_name, ppcd.patientprofilecategory, pvad.visitageinyears, pvad.visitagerange, ds.service_name, bdt.visit_encounter_id, au.personnel_name
  • Sort Method: external merge Disk: 275288kB
3. 1,514.664 18,167.764 ↓ 1.2 862,607 1

Hash Join (cost=288,370.83..655,422.92 rows=725,111 width=545) (actual time=11,962.364..18,167.764 rows=862,607 loops=1)

  • Hash Cond: (pe.user_id = au.user_id)
4. 291.184 16,651.986 ↓ 1.2 862,607 1

Hash Join (cost=288,221.32..629,894.52 rows=725,111 width=531) (actual time=11,961.212..16,651.986 rows=862,607 loops=1)

  • Hash Cond: (dv.service_id = ds.service_id)
5. 1,508.751 16,360.783 ↓ 1.2 862,607 1

Hash Join (cost=288,219.82..619,922.75 rows=725,111 width=317) (actual time=11,961.180..16,360.783 rows=862,607 loops=1)

  • Hash Cond: (bdt.visit_id = dv.visit_id)
6. 838.374 2,890.941 ↓ 1.0 726,992 1

Merge Join (cost=12.75..293,047.46 rows=725,111 width=24) (actual time=0.020..2,890.941 rows=726,992 loops=1)

  • Merge Cond: (bdt.visit_encounter_id = pe.visit_encounter_id)
7. 474.412 474.412 ↑ 1.0 726,993 1

Index Scan using mat_biodatadentaltreatments_visit_encounter_id_idx on mat_biodatadentaltreatments bdt (cost=0.42..63,198.81 rows=732,583 width=16) (actual time=0.008..474.412 rows=726,993 loops=1)

8. 1,578.155 1,578.155 ↓ 1.0 4,625,718 1

Index Scan using mat_patientencounter_visit_encounter_id_idx on mat_patientencounter pe (cost=0.43..210,719.95 rows=4,535,759 width=16) (actual time=0.009..1,578.155 rows=4,625,718 loops=1)

9. 1,209.296 11,961.091 ↓ 1.2 449,837 1

Hash (cost=267,764.20..267,764.20 rows=381,989 width=309) (actual time=11,961.091..11,961.091 rows=449,837 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 63743kB
10. 149.222 10,751.795 ↓ 1.2 449,837 1

Hash Join (cost=222,388.92..267,764.20 rows=381,989 width=309) (actual time=8,845.230..10,751.795 rows=449,837 loops=1)

  • Hash Cond: (dv.clinic_id = f.facility_id)
11. 400.603 10,602.557 ↓ 1.2 449,837 1

Hash Left Join (cost=222,387.29..262,510.22 rows=381,989 width=294) (actual time=8,845.205..10,602.557 rows=449,837 loops=1)

  • Hash Cond: (p.patient_id = pci.patient_id)
12. 132.231 9,926.296 ↓ 1.2 449,837 1

Hash Left Join (cost=212,698.76..243,693.60 rows=381,989 width=281) (actual time=8,569.394..9,926.296 rows=449,837 loops=1)

  • Hash Cond: (pa.state_id = ps.state_id)
13. 135.570 9,794.053 ↓ 1.2 449,837 1

Hash Left Join (cost=212,697.40..238,742.30 rows=381,989 width=276) (actual time=8,569.368..9,794.053 rows=449,837 loops=1)

  • Hash Cond: (pa.district_district_id = pd.district_id)
14. 258.183 9,658.428 ↓ 1.2 449,837 1

Hash Left Join (cost=212,692.52..233,787.61 rows=381,989 width=271) (actual time=8,569.287..9,658.428 rows=449,837 loops=1)

  • Hash Cond: (p.patient_id = pnk.patient_id)
15. 750.391 9,323.925 ↓ 1.2 449,837 1

Hash Right Join (cost=207,712.80..224,445.09 rows=381,989 width=212) (actual time=8,492.898..9,323.925 rows=449,837 loops=1)

  • Hash Cond: (pid.patient_id = p.patient_id)
16. 80.797 80.797 ↓ 1.0 364,272 1

Seq Scan on mat_patientidentificationdata pid (cost=0.00..9,313.21 rows=362,200 width=26) (actual time=0.013..80.797 rows=364,272 loops=1)

  • Filter: (is_primary IS TRUE)
  • Rows Removed by Filter: 114849
17. 1,522.406 8,492.737 ↓ 1.2 449,837 1

Hash (cost=202,937.94..202,937.94 rows=381,989 width=194) (actual time=8,492.737..8,492.737 rows=449,837 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 99629kB
18. 599.031 6,970.331 ↓ 1.2 449,837 1

Hash Left Join (cost=101,310.67..202,937.94 rows=381,989 width=194) (actual time=3,080.646..6,970.331 rows=449,837 loops=1)

  • Hash Cond: (p.patient_id = pa.patient_id)
19. 128.173 5,629.481 ↓ 1.2 449,837 1

Hash Left Join (cost=83,540.81..177,529.98 rows=381,989 width=140) (actual time=2,338.553..5,629.481 rows=449,837 loops=1)

  • Hash Cond: (p.ethnic_group_ethnic_group_id = et.ethnic_group_id)
20. 542.198 5,501.294 ↓ 1.2 449,837 1

Hash Left Join (cost=83,539.52..172,311.99 rows=381,989 width=132) (actual time=2,338.505..5,501.294 rows=449,837 loops=1)

  • Hash Cond: (dv.visit_id = ppcd.visit_id)
21. 127.003 4,198.114 ↓ 1.2 449,837 1

Hash Join (cost=49,409.93..131,462.45 rows=381,989 width=123) (actual time=1,577.227..4,198.114 rows=449,837 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
22. 516.837 4,071.108 ↓ 1.2 449,837 1

Hash Join (cost=49,408.86..126,209.03 rows=381,989 width=119) (actual time=1,577.213..4,071.108 rows=449,837 loops=1)

  • Hash Cond: (dv.patient_id = p.patient_id)
23. 1,628.289 2,245.177 ↑ 1.0 381,989 1

Hash Right Join (cost=22,141.75..91,302.14 rows=381,989 width=54) (actual time=267.836..2,245.177 rows=381,989 loops=1)

  • Hash Cond: (pvad.visit_id = dv.visit_id)
24. 349.202 349.202 ↑ 1.0 1,859,275 1

Seq Scan on mat_patientvisitagedata pvad (cost=0.00..46,747.75 rows=1,859,275 width=30) (actual time=0.031..349.202 rows=1,859,275 loops=1)

25. 115.027 267.686 ↑ 1.0 381,989 1

Hash (cost=17,366.89..17,366.89 rows=381,989 width=32) (actual time=267.686..267.686 rows=381,989 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 23875kB
26. 152.659 152.659 ↑ 1.0 381,989 1

Seq Scan on mat_dentalvisitdata dv (cost=0.00..17,366.89 rows=381,989 width=32) (actual time=0.005..152.659 rows=381,989 loops=1)

27. 480.700 1,309.094 ↓ 1.0 381,185 1

Hash (cost=22,712.41..22,712.41 rows=364,376 width=73) (actual time=1,309.094..1,309.094 rows=381,185 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 39190kB
28. 182.901 828.394 ↓ 1.0 381,185 1

Hash Right Join (cost=20,758.75..22,712.41 rows=364,376 width=73) (actual time=637.437..828.394 rows=381,185 loops=1)

  • Hash Cond: (egl.patient_id = p.patient_id)
29. 8.397 8.397 ↑ 1.0 44,937 1

Index Only Scan using trx_pm_egl_information_patient_id_idx on trx_pm_egl_information egl (cost=0.29..1,055.21 rows=44,937 width=8) (actual time=0.070..8.397 rows=44,937 loops=1)

  • Heap Fetches: 3153
30. 461.235 637.096 ↓ 1.0 365,026 1

Hash (cost=16,203.76..16,203.76 rows=364,376 width=65) (actual time=637.096..637.096 rows=365,026 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 37181kB
31. 175.861 175.861 ↓ 1.0 365,026 1

Seq Scan on mst_pm_patient p (cost=0.00..16,203.76 rows=364,376 width=65) (actual time=0.006..175.861 rows=365,026 loops=1)

32. 0.000 0.003 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on ref_co_gender g (cost=0.00..1.03 rows=3 width=12) (actual time=0.002..0.003 rows=3 loops=1)

34. 292.264 760.982 ↓ 1.1 381,989 1

Hash (cost=29,907.65..29,907.65 rows=337,755 width=17) (actual time=760.982..760.982 rows=381,989 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 19391kB
35. 44.078 468.718 ↓ 1.1 381,989 1

Subquery Scan on ppcd (cost=0.42..29,907.65 rows=337,755 width=17) (actual time=0.065..468.718 rows=381,989 loops=1)

36. 76.654 424.640 ↓ 1.1 381,989 1

Unique (cost=0.42..26,530.10 rows=337,755 width=17) (actual time=0.062..424.640 rows=381,989 loops=1)

37. 347.986 347.986 ↑ 1.0 539,570 1

Index Scan using mat_patientprofilecategorydata_visit_id_idx on mat_patientprofilecategorydata (cost=0.42..25,181.18 rows=539,570 width=17) (actual time=0.062..347.986 rows=539,570 loops=1)

38. 0.005 0.014 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=16) (actual time=0.014..0.014 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
39. 0.009 0.009 ↑ 1.0 13 1

Seq Scan on ref_co_ethnic_group et (cost=0.00..1.13 rows=13 width=16) (actual time=0.005..0.009 rows=13 loops=1)

40. 617.896 741.819 ↑ 1.0 364,216 1

Hash (cost=13,217.16..13,217.16 rows=364,216 width=62) (actual time=741.819..741.819 rows=364,216 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 34487kB
41. 123.923 123.923 ↑ 1.0 364,216 1

Seq Scan on mat_patientaddressdata pa (cost=0.00..13,217.16 rows=364,216 width=62) (actual time=0.009..123.923 rows=364,216 loops=1)

42. 35.458 76.320 ↑ 1.0 50,688 1

Hash (cost=4,332.37..4,332.37 rows=51,788 width=67) (actual time=76.320..76.320 rows=50,688 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 4792kB
43. 14.068 40.862 ↑ 1.0 50,688 1

Hash Left Join (cost=67.76..4,332.37 rows=51,788 width=67) (actual time=0.945..40.862 rows=50,688 loops=1)

  • Hash Cond: (pnk.relationship_id = sl.simple_list_config_id)
44. 25.870 25.870 ↑ 1.0 50,688 1

Seq Scan on mst_pm_next_of_kin pnk (cost=0.00..3,508.87 rows=51,788 width=63) (actual time=0.008..25.870 rows=50,688 loops=1)

  • Filter: (next_of_kin IS TRUE)
  • Rows Removed by Filter: 41593
45. 0.365 0.924 ↑ 1.0 1,761 1

Hash (cost=45.67..45.67 rows=1,767 width=20) (actual time=0.924..0.924 rows=1,761 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 96kB
46. 0.559 0.559 ↑ 1.0 1,761 1

Seq Scan on ref_am_simple_list_config sl (cost=0.00..45.67 rows=1,767 width=20) (actual time=0.018..0.559 rows=1,761 loops=1)

47. 0.030 0.055 ↑ 1.0 128 1

Hash (cost=3.28..3.28 rows=128 width=13) (actual time=0.055..0.055 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
48. 0.025 0.025 ↑ 1.0 128 1

Seq Scan on ref_co_district pd (cost=0.00..3.28 rows=128 width=13) (actual time=0.004..0.025 rows=128 loops=1)

49. 0.003 0.012 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=13) (actual time=0.012..0.012 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
50. 0.009 0.009 ↑ 1.0 16 1

Seq Scan on ref_co_state ps (cost=0.00..1.16 rows=16 width=13) (actual time=0.004..0.009 rows=16 loops=1)

51. 214.779 275.658 ↑ 1.0 324,157 1

Hash (cost=5,636.57..5,636.57 rows=324,157 width=29) (actual time=275.658..275.658 rows=324,157 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 20309kB
52. 60.879 60.879 ↑ 1.0 324,157 1

Seq Scan on mat_patientcontactinfo pci (cost=0.00..5,636.57 rows=324,157 width=29) (actual time=0.007..60.879 rows=324,157 loops=1)

53. 0.003 0.016 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=23) (actual time=0.016..0.016 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
54. 0.013 0.013 ↑ 1.0 28 1

Seq Scan on mst_am_facility f (cost=0.00..1.28 rows=28 width=23) (actual time=0.003..0.013 rows=28 loops=1)

55. 0.010 0.019 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=222) (actual time=0.019..0.019 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
56. 0.009 0.009 ↑ 1.0 22 1

Seq Scan on mat_dentalservices ds (cost=0.00..1.22 rows=22 width=222) (actual time=0.005..0.009 rows=22 loops=1)

57. 0.308 1.114 ↓ 1.0 1,624 1

Hash (cost=129.23..129.23 rows=1,623 width=30) (actual time=1.114..1.114 rows=1,624 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
58. 0.806 0.806 ↓ 1.0 1,624 1

Seq Scan on mst_am_user au (cost=0.00..129.23 rows=1,623 width=30) (actual time=0.004..0.806 rows=1,624 loops=1)

Planning time : 28.154 ms
Execution time : 43,807.951 ms