explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t2EB

Settings
# exclusive inclusive rows x rows loops node
1. 0.670 4,199.803 ↑ 1.0 5,000 1

Limit (cost=602.49..1,240,913.33 rows=5,000 width=1,125) (actual time=11.934..4,199.803 rows=5,000 loops=1)

2. 21.873 4,199.133 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=602.49..4,114,217.41 rows=16,583 width=1,125) (actual time=11.933..4,199.133 rows=5,000 loops=1)

3. 4.293 1,532.260 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=602.36..180,819.68 rows=16,583 width=862) (actual time=9.024..1,532.260 rows=5,000 loops=1)

4. 0.706 1,527.967 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=602.21..178,116.65 rows=16,583 width=855) (actual time=9.023..1,527.967 rows=5,000 loops=1)

5. 1.073 1,522.261 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=601.92..172,329.26 rows=16,583 width=839) (actual time=9.019..1,522.261 rows=5,000 loops=1)

6. 1.084 1,496.188 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=601.63..167,236.30 rows=16,583 width=851) (actual time=8.762..1,496.188 rows=5,000 loops=1)

7. 6.792 1,480.104 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=601.36..162,342.60 rows=16,583 width=837) (actual time=8.754..1,480.104 rows=5,000 loops=1)

8. 4.491 1,463.312 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=601.22..159,736.17 rows=16,583 width=627) (actual time=8.504..1,463.312 rows=5,000 loops=1)

  • Join Filter: (vn.visit_type = pr.visit_type)
  • Rows Removed by Join Filter: 3077
9. 6.312 1,458.821 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=601.22..158,889.40 rows=16,583 width=597) (actual time=8.263..1,458.821 rows=5,000 loops=1)

10. 5.202 667.509 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=600.79..80,191.42 rows=16,583 width=575) (actual time=7.318..667.509 rows=5,000 loops=1)

11. 5.231 652.307 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=600.65..77,634.18 rows=16,583 width=466) (actual time=6.890..652.307 rows=5,000 loops=1)

12. 3.148 62.076 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=600.23..17,036.21 rows=16,583 width=420) (actual time=5.613..62.076 rows=5,000 loops=1)

13. 4.462 53.928 ↑ 3.3 5,000 1

Nested Loop Left Join (cost=599.95..12,024.59 rows=16,583 width=401) (actual time=5.610..53.928 rows=5,000 loops=1)

14. 5.608 39.466 ↑ 3.3 5,000 1

Nested Loop (cost=599.67..7,013.00 rows=16,583 width=387) (actual time=5.598..39.466 rows=5,000 loops=1)

15. 2.873 13.858 ↑ 3.3 5,000 1

Hash Left Join (cost=599.39..1,502.25 rows=16,583 width=301) (actual time=4.757..13.858 rows=5,000 loops=1)

  • Hash Cond: (bos.prescribed_id = op.prescribed_id)
16. 3.727 6.296 ↑ 3.3 5,000 1

Hash Left Join (cost=1.97..659.83 rows=16,583 width=297) (actual time=0.051..6.296 rows=5,000 loops=1)

  • Hash Cond: ((bos.theatre_name)::text = (tm.theatre_id)::text)
17. 2.546 2.546 ↑ 3.3 5,000 1

Seq Scan on bed_operation_schedule bos (cost=0.00..610.04 rows=16,583 width=87) (actual time=0.014..2.546 rows=5,000 loops=1)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 256
18. 0.007 0.023 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=256) (actual time=0.023..0.023 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.016 0.016 ↑ 1.0 43 1

Seq Scan on theatre_master tm (cost=0.00..1.43 rows=43 width=256) (actual time=0.013..0.016 rows=43 loops=1)

20. 1.823 4.689 ↑ 1.0 17,344 1

Hash (cost=373.21..373.21 rows=17,937 width=8) (actual time=4.689..4.689 rows=17,344 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 934kB
21. 2.866 2.866 ↑ 1.0 17,757 1

Seq Scan on operation_procedures op (cost=0.00..373.21 rows=17,937 width=8) (actual time=0.008..2.866 rows=17,757 loops=1)

  • Filter: ((oper_priority)::text = 'P'::text)
  • Rows Removed by Filter: 595
22. 20.000 20.000 ↑ 1.0 1 5,000

Index Scan using operation_master_pkey on operation_master om (cost=0.28..0.33 rows=1 width=104) (actual time=0.004..0.004 rows=1 loops=5,000)

  • Index Cond: ((op_id)::text = (bos.operation_name)::text)
23. 10.000 10.000 ↑ 1.0 1 5,000

Index Scan using doctors_pkey on doctors doc (cost=0.28..0.30 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=5,000)

  • Index Cond: ((doctor_id)::text = (bos.surgeon)::text)
24. 5.000 5.000 ↓ 0.0 0 5,000

Index Scan using doctors_pkey on doctors doctor (cost=0.28..0.30 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=5,000)

  • Index Cond: ((doctor_id)::text = (bos.anaesthetist)::text)
25. 585.000 585.000 ↑ 1.0 1 5,000

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..3.65 rows=1 width=61) (actual time=0.117..0.117 rows=1 loops=5,000)

  • Index Cond: ((mr_no)::text = (bos.mr_no)::text)
26. 10.000 10.000 ↑ 1.0 1 5,000

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=156) (actual time=0.002..0.002 rows=1 loops=5,000)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
27. 785.000 785.000 ↑ 1.0 1 5,000

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..4.75 rows=1 width=38) (actual time=0.157..0.157 rows=1 loops=5,000)

  • Index Cond: ((bos.patient_id)::text = (patient_id)::text)
28. 0.000 0.000 ↑ 2.0 2 5,000

Materialize (cost=0.00..1.06 rows=4 width=40) (actual time=0.000..0.000 rows=2 loops=5,000)

29. 0.236 0.236 ↑ 2.0 2 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (actual time=0.235..0.236 rows=2 loops=1)

30. 10.000 10.000 ↑ 1.0 1 5,000

Index Scan using department_pkey on department admdep (cost=0.14..0.16 rows=1 width=256) (actual time=0.002..0.002 rows=1 loops=5,000)

  • Index Cond: ((pr.admitted_dept)::text = (dept_id)::text)
31. 15.000 15.000 ↑ 1.0 1 5,000

Index Scan using doctors_pkey on doctors dr (cost=0.28..0.30 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=5,000)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
32. 25.000 25.000 ↓ 0.0 0 5,000

Index Scan using admission_pkey on admission ad (cost=0.29..0.31 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=5,000)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
33. 5.000 5.000 ↓ 0.0 0 5,000

Index Scan using bed_names_pkey on bed_names bn (cost=0.29..0.35 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=5,000)

  • Index Cond: (bed_id = ad.bed_id)
34. 0.000 0.000 ↓ 0.0 0 5,000

Index Scan using ward_names_pkey on ward_names wn (cost=0.14..0.16 rows=1 width=25) (actual time=0.000..0.000 rows=0 loops=5,000)

  • Index Cond: ((ward_no)::text = (bn.ward_no)::text)
35. 5.000 5.000 ↑ 1.0 1 5,000

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.15 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=5,000)

  • Index Cond: (center_id = pr.center_id)
36.          

SubPlan (for Nested Loop Left Join)

37. 10.000 1,335.000 ↑ 1.0 1 5,000

Aggregate (cost=115.68..115.69 rows=1 width=32) (actual time=0.267..0.267 rows=1 loops=5,000)

38. 6.272 1,325.000 ↓ 0.0 0 5,000

Nested Loop (cost=0.28..115.43 rows=1 width=104) (actual time=0.222..0.265 rows=0 loops=5,000)

39. 1,315.000 1,315.000 ↓ 0.0 0 5,000

Seq Scan on surgery_anesthesia_details sad (cost=0.00..107.11 rows=1 width=7) (actual time=0.220..0.263 rows=0 loops=5,000)

  • Filter: (bos.prescribed_id = prescribed_id)
  • Rows Removed by Filter: 5283
40. 3.728 3.728 ↑ 1.0 1 932

Index Scan using anesthesia_type_master_pkey on anesthesia_type_master atm (cost=0.28..8.29 rows=1 width=111) (actual time=0.004..0.004 rows=1 loops=932)

  • Index Cond: ((anesthesia_type_id)::text = (sad.anesthesia_type)::text)
41. 0.000 1,305.000 ↑ 1.0 1 5,000

Aggregate (cost=121.07..121.08 rows=1 width=32) (actual time=0.261..0.261 rows=1 loops=5,000)

42. 5.000 1,305.000 ↓ 0.0 0 5,000

Nested Loop (cost=0.55..120.82 rows=1 width=22) (actual time=0.261..0.261 rows=0 loops=5,000)

43. 2.354 1,300.000 ↓ 0.0 0 5,000

Nested Loop (cost=0.28..112.51 rows=1 width=8) (actual time=0.220..0.260 rows=0 loops=5,000)

44. 1,295.000 1,295.000 ↓ 0.0 0 5,000

Seq Scan on operation_anaesthesia_details asad (cost=0.00..104.19 rows=1 width=15) (actual time=0.219..0.259 rows=0 loops=5,000)

  • Filter: (operation_details_id = op.operation_details_id)
  • Rows Removed by Filter: 5134
45. 2.646 2.646 ↑ 1.0 1 882

Index Only Scan using anesthesia_type_master_pkey on anesthesia_type_master atm_1 (cost=0.28..8.29 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=882)

  • Index Cond: (anesthesia_type_id = (asad.anesthesia_type)::text)
  • Heap Fetches: 882
46. 0.000 0.000 ↓ 0.0 0 882

Index Scan using doctors_pkey on doctors d (cost=0.28..8.29 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=882)

  • Index Cond: ((doctor_id)::text = (asad.doctor_id)::text)
Planning time : 11.575 ms
Execution time : 4,200.424 ms