explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fp9r

Settings
# exclusive inclusive rows x rows loops node
1. 0.432 2,700.662 ↑ 1.0 5,000 1

Limit (cost=602.49..1,240,913.33 rows=5,000 width=1,125) (actual time=4.955..2,700.662 rows=5,000 loops=1)

2. 13.627 2,700.230 ↑ 3.3 5,000 1

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

3. 4.097 186.603 ↑ 3.3 5,000 1

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

4. 0.258 182.506 ↑ 3.3 5,000 1

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

5. 1.736 177.248 ↑ 3.3 5,000 1

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

6. 2.603 160.512 ↑ 3.3 5,000 1

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

7. 5.217 147.909 ↑ 3.3 5,000 1

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

8. 2.512 137.692 ↑ 3.3 5,000 1

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

  • Join Filter: (vn.visit_type = pr.visit_type)
  • Rows Removed by Join Filter: 3,077
9. 6.572 135.180 ↑ 3.3 5,000 1

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

10. 4.013 98.608 ↑ 3.3 5,000 1

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

11. 6.117 89.595 ↑ 3.3 5,000 1

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

12. 2.387 53.478 ↑ 3.3 5,000 1

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

13. 3.696 46.091 ↑ 3.3 5,000 1

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

14. 5.864 32.395 ↑ 3.3 5,000 1

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

15. 2.778 11.531 ↑ 3.3 5,000 1

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

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

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

  • Hash Cond: ((bos.theatre_name)::text = (tm.theatre_id)::text)
17. 1.659 1.659 ↑ 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.010..1.659 rows=5,000 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.011 0.011 ↑ 1.0 43 1

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

20. 1.586 4.174 ↑ 1.0 17,344 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 934kB
21. 2.588 2.588 ↑ 1.0 17,757 1

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

  • Filter: ((oper_priority)::text = 'P'::text)
  • Rows Removed by Filter: 595
22. 15.000 15.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.003..0.003 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. 30.000 30.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.006..0.006 rows=1 loops=5,000)

  • Index Cond: ((mr_no)::text = (bos.mr_no)::text)
26. 5.000 5.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.001..0.001 rows=1 loops=5,000)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
27. 30.000 30.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.006..0.006 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.002 0.002 ↑ 2.0 2 1

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

30. 5.000 5.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.001..0.001 rows=1 loops=5,000)

  • Index Cond: ((pr.admitted_dept)::text = (dept_id)::text)
31. 10.000 10.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.002..0.002 rows=1 loops=5,000)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
32. 15.000 15.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.003..0.003 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,255.000 ↑ 1.0 1 5,000

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

38. 3.136 1,245.000 ↓ 0.0 0 5,000

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

39. 1,240.000 1,240.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.208..0.248 rows=0 loops=5,000)

  • Filter: (bos.prescribed_id = prescribed_id)
  • Rows Removed by Filter: 5,283
40. 1.864 1.864 ↑ 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.002..0.002 rows=1 loops=932)

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

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

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

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

43. 3.236 1,235.000 ↓ 0.0 0 5,000

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

44. 1,230.000 1,230.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.207..0.246 rows=0 loops=5,000)

  • Filter: (operation_details_id = op.operation_details_id)
  • Rows Removed by Filter: 5,134
45. 1.764 1.764 ↑ 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.002..0.002 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 : 8.751 ms
Execution time : 2,701.217 ms