explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vi5

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 38,503.160 ↑ 1.0 1 1

HashAggregate (cost=7,175,659.77..7,175,659.78 rows=1 width=82) (actual time=38,503.160..38,503.160 rows=1 loops=1)

  • Total runtime: 38514.267 ms
2. 38,503.121 38,503.138 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,175,647.28..7,175,659.76 rows=1 width=82) (actual time=38,503.134..38,503.138 rows=1 loops=1)

3. 0.000 0.017 ↓ 0.0 0 1

Join Filter: ((pd.mr_no)::text = (patient_deposits.mr_no)::text)posit_setoff_total dst (cost=0.29..8.31 rows=1 width=18) (actual time=0.017..0.017 rows=0 loops=1)

4. 0.022 38,503.093 ↑ 1.0 1 1

Hash Right Join (cost=7,175,642.84..7,175,643.59 rows=1 width=97) (actual time=38,503.089..38,503.093 rows=1 loops=1)

  • Hash Cond: (((r.mr_no)::text = (pd_1.mr_no)::text) AND ("*SELECT* 1".package_id = pd_1.package_id)) loops=1)
  • -> HashAggregate (cost=7175597.41..7175597.68 rows=27 width=22) (actual time=38502.920..38502.920 rows=0 loops=1)020..0.020 rows=0 loops=1)
5. 308.693 38,502.917 ↓ 0.0 0 1

Merge Join (cost=6,672,071.86..7,175,597.21 rows=27 width=22) (actual time=38,502.917..38,502.917 rows=0 loops=1)

  • Merge Cond: ((bc.bill_no)::text = (b.bill_no)::text)
  • -> Unique (cost=6671992.05..6860813.90 rows=25176247 width=16) (actual time=34725.692..38499.070 rows=12314 loops=1)oops=1)
6. 21,175.126 38,194.133 ↑ 17.0 1,482,510 1

Sort (cost=6,671,992.05..6,734,932.67 rows=25,176,247 width=16) (actual time=34,725.691..38,194.133 rows=1,482,510 loops=1)

  • Sort Key: bc.bill_no, "*SELECT* 1".package_id
  • Sort Method: external merge Disk: 43368kB
7. 1,184.719 17,019.007 ↑ 17.0 1,482,791 1

Hash Join (cost=1,228,541.02..2,286,355.91 rows=25,176,247 width=16) (actual time=13,966.284..17,019.007 rows=1,482,791 loops=1)

  • Hash Cond: ("*SELECT* 1".common_order_id = bc.order_number)
8. 32.445 2,026.288 ↓ 1.5 114,151 1

Append (cost=808.44..247,191.06 rows=76,373 width=8) (actual time=7.591..2,026.288 rows=114,151 loops=1)

9. 6.194 552.040 ↓ 1.8 19,138 1

Subquery Scan on *SELECT* 1 (cost=808.44..48,130.92 rows=10,584 width=8) (actual time=7.591..552.040 rows=19,138 loops=1)

10. 17.548 545.846 ↓ 1.8 19,138 1

Nested Loop (cost=808.44..48,025.08 rows=10,584 width=8) (actual time=7.589..545.846 rows=19,138 loops=1)

11. 287.669 488.650 ↓ 1.5 13,216 1

Hash Join (cost=808.01..34,620.25 rows=8,540 width=8) (actual time=7.577..488.650 rows=13,216 loops=1)

  • Hash Cond: (pp.package_id = p.package_id)
12. 194.684 194.684 ↓ 1.0 1,169,518 1

Seq Scan on package_prescribed pp (cost=0.00..29,341.16 rows=1,169,516 width=8) (actual time=0.002..194.684 rows=1,169,518 loops=1)

13. 0.076 6.297 ↑ 1.0 193 1

Hash (cost=805.52..805.52 rows=199 width=4) (actual time=6.297..6.297 rows=193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
14. 6.221 6.221 ↑ 1.0 193 1

Seq Scan on pack_master p (cost=0.00..805.52 rows=199 width=4) (actual time=0.053..6.221 rows=193 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 26292
15. 39.648 39.648 ↑ 33.0 1 13,216

Index Scan using services_prescribed_package_ref_is_null_idx on services_prescribed sp (cost=0.43..1.24 rows=33 width=8) (actual time=0.003..0.003 rows=1 loops=13,216)

  • Index Cond: (package_ref = pp.prescription_id)
16. 24.419 649.964 ↓ 1.3 78,621 1

Subquery Scan on *SELECT* 2 (cost=808.44..133,631.47 rows=58,710 width=8) (actual time=6.306..649.964 rows=78,621 loops=1)

17. 32.179 625.545 ↓ 1.3 78,621 1

Nested Loop (cost=808.44..133,044.37 rows=58,710 width=8) (actual time=6.306..625.545 rows=78,621 loops=1)

18. 265.006 461.206 ↓ 1.5 13,216 1

Hash Join (cost=808.01..34,620.25 rows=8,540 width=8) (actual time=6.290..461.206 rows=13,216 loops=1)

  • Hash Cond: (pp_1.package_id = p_1.package_id)
19. 191.118 191.118 ↓ 1.0 1,169,518 1

Seq Scan on package_prescribed pp_1 (cost=0.00..29,341.16 rows=1,169,516 width=8) (actual time=0.001..191.118 rows=1,169,518 loops=1)

20. 0.050 5.082 ↑ 1.0 193 1

Hash (cost=805.52..805.52 rows=199 width=4) (actual time=5.082..5.082 rows=193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
21. 5.032 5.032 ↑ 1.0 193 1

Seq Scan on pack_master p_1 (cost=0.00..805.52 rows=199 width=4) (actual time=0.042..5.032 rows=193 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 26292
22. 132.160 132.160 ↑ 53.3 6 13,216

Index Scan using tests_prescribed_package_ref_is_null_idx on tests_prescribed tp (cost=0.43..8.33 rows=320 width=8) (actual time=0.005..0.010 rows=6 loops=13,216)

  • Index Cond: (package_ref = pp_1.prescription_id)
23. 5.739 791.685 ↓ 2.3 16,392 1

Subquery Scan on *SELECT* 3 (cost=809.00..63,831.05 rows=7,078 width=8) (actual time=6.304..791.685 rows=16,392 loops=1)

24. 13.159 785.946 ↓ 2.3 16,392 1

Nested Loop Left Join (cost=809.00..63,760.27 rows=7,078 width=8) (actual time=6.303..785.946 rows=16,392 loops=1)

25. 11.318 526.907 ↓ 2.3 16,392 1

Nested Loop (cost=808.44..55,690.55 rows=7,078 width=12) (actual time=6.262..526.907 rows=16,392 loops=1)

26. 265.651 462.725 ↓ 1.5 13,216 1

Hash Join (cost=808.01..34,620.25 rows=8,540 width=8) (actual time=6.246..462.725 rows=13,216 loops=1)

  • Hash Cond: (pp_2.package_id = p_2.package_id)
27. 191.999 191.999 ↓ 1.0 1,169,518 1

Seq Scan on package_prescribed pp_2 (cost=0.00..29,341.16 rows=1,169,516 width=8) (actual time=0.002..191.999 rows=1,169,518 loops=1)

28. 0.058 5.075 ↑ 1.0 193 1

Hash (cost=805.52..805.52 rows=199 width=4) (actual time=5.075..5.075 rows=193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
29. 5.017 5.017 ↑ 1.0 193 1

Seq Scan on pack_master p_2 (cost=0.00..805.52 rows=199 width=4) (actual time=0.041..5.017 rows=193 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 26292
30. 52.864 52.864 ↑ 61.0 1 13,216

Index Scan using doctor_consultation_pkg_ref_idx on doctor_consultation dc (cost=0.43..1.86 rows=61 width=12) (actual time=0.004..0.004 rows=1 loops=13,216)

  • Index Cond: (package_ref = pp_2.prescription_id)
31. 245.880 245.880 ↑ 1.0 1 16,392

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.57..1.13 rows=1 width=18) (actual time=0.013..0.015 rows=1 loops=16,392)

  • Index Cond: ((activity_id)::text = (dc.consultation_id)::text)
  • Filter: ((activity_code)::text = 'DOC'::text)
  • Rows Removed by Filter: 2
32. 0.002 0.154 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.71..1,597.62 rows=1 width=8) (actual time=0.154..0.154 rows=0 loops=1)

33. 0.000 0.152 ↓ 0.0 0 1

Nested Loop (cost=0.71..1,597.61 rows=1 width=8) (actual time=0.152..0.152 rows=0 loops=1)

34. 0.118 0.152 ↓ 0.0 0 1

Nested Loop (cost=0.43..1,597.29 rows=1 width=8) (actual time=0.152..0.152 rows=0 loops=1)

35. 0.034 0.034 ↑ 1.0 191 1

Seq Scan on other_services_prescribed osp (cost=0.00..5.91 rows=191 width=8) (actual time=0.001..0.034 rows=191 loops=1)

36. 0.000 0.000 ↓ 0.0 0 191

Index Scan using package_prescribed_pkey on package_prescribed pp_3 (cost=0.43..8.32 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=191)

  • Index Cond: (prescription_id = osp.package_ref)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using pack_temp__id_primary on pack_master p_3 (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: (package_id = pp_3.package_id)
  • Filter: multi_visit_package
38. 4,129.050 13,808.000 ↑ 2.2 7,762,302 1

Hash (cost=931,299.26..931,299.26 rows=17,053,226 width=16) (actual time=13,808.000..13,808.000 rows=7,762,302 loops=1)

  • Buckets: 8192 Batches: 512 Memory Usage: 806kB
39. 9,678.950 9,678.950 ↓ 1.0 17,053,881 1

Seq Scan on bill_charge bc (cost=0.00..931,299.26 rows=17,053,226 width=16) (actual time=0.014..9,678.950 rows=17,053,881 loops=1)

40. 0.015 0.091 ↑ 6.0 1 1

Sort (cost=79.81..79.83 rows=6 width=31) (actual time=0.091..0.091 rows=1 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
41. 0.009 0.076 ↑ 6.0 1 1

Nested Loop (cost=0.99..79.73 rows=6 width=31) (actual time=0.074..0.076 rows=1 loops=1)

42. 0.039 0.039 ↑ 6.0 1 1

Index Scan using patient_registration_mr_no_index on patient_registration r (cost=0.43..28.21 rows=6 width=27) (actual time=0.038..0.039 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = 'RKON.0000019915'::text)
43. 0.028 0.028 ↑ 1.0 1 1

Index Scan using bill_visit_id_idx on bill b (cost=0.56..8.58 rows=1 width=27) (actual time=0.027..0.028 rows=1 loops=1)

  • Index Cond: ((visit_id)::text = (r.patient_id)::text)
44. 0.002 0.154 ↑ 1.0 1 1

Hash (cost=45.41..45.41 rows=1 width=69) (actual time=0.154..0.154 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.001 0.152 ↑ 1.0 1 1

Nested Loop Left Join (cost=17.28..45.41 rows=1 width=69) (actual time=0.149..0.152 rows=1 loops=1)

  • Join Filter: ((pd_1.mr_no)::text = (pd.mr_no)::text)
46. 0.001 0.130 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.15..29.25 rows=1 width=18) (actual time=0.127..0.130 rows=1 loops=1)

  • Join Filter: ((pd.mr_no)::text = (dst.mr_no)::text)
47. 0.008 0.112 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..20.93 rows=1 width=15) (actual time=0.109..0.112 rows=1 loops=1)

48. 0.005 0.077 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.71..16.76 rows=1 width=60) (actual time=0.075..0.077 rows=1 loops=1)

49. 0.052 0.052 ↑ 1.0 1 1

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..8.45 rows=1 width=183) (actual time=0.051..0.052 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = 'RKON.0000019915'::text)
50. 0.020 0.020 ↑ 1.0 1 1

Index Scan using city_pkey on city ci (cost=0.28..8.30 rows=1 width=44) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: ((pd.patient_city)::text = (city_id)::text)
51. 0.027 0.027 ↑ 1.0 1 1

Index Only Scan using state_master_pkey on state_master st (cost=0.14..4.16 rows=1 width=7) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (state_id = (pd.patient_state)::text)
  • Heap Fetches: 0
52. 0.017 0.017 ↓ 0.0 0 1

Index Scan using dst_mr_no_idx on deposit_setoff_total dst (cost=0.29..8.31 rows=1 width=18) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: ((mr_no)::text = 'RKON.0000019915'::text)
53. 0.001 0.021 ↓ 0.0 0 1

HashAggregate (cost=16.13..16.14 rows=1 width=24) (actual time=0.021..0.021 rows=0 loops=1)

54. 0.001 0.020 ↓ 0.0 0 1

Bitmap Heap Scan on patient_deposits pd_1 (cost=4.44..16.12 rows=1 width=24) (actual time=0.020..0.020 rows=0 loops=1)

  • Recheck Cond: ((mr_no)::text = 'RKON.0000019915'::text)
  • Filter: (package_id IS NOT NULL)
55. 0.019 0.019 ↓ 0.0 0 1

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.44 rows=3 width=0) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: ((mr_no)::text = 'RKON.0000019915'::text)
56. 0.001 0.023 ↓ 0.0 0 1

GroupAggregate (cost=4.44..16.15 rows=1 width=20) (actual time=0.023..0.023 rows=0 loops=1)

57. 0.002 0.022 ↓ 0.0 0 1

Bitmap Heap Scan on patient_deposits (cost=4.44..16.13 rows=1 width=20) (actual time=0.022..0.022 rows=0 loops=1)

  • Recheck Cond: ((mr_no)::text = 'RKON.0000019915'::text)
  • Filter: (realized <> 'Y'::bpchar)
58. 0.020 0.020 ↓ 0.0 0 1

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.44 rows=3 width=0) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: ((mr_no)::text = 'RKON.0000019915'::text)