explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U5Rs : Optimization for: Optimization for: Optimization for: Optimization for: plan #J5VP; plan #dYEw; plan #8vcQ; plan #mw4C

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.018 764.550 ↓ 0.0 0 1

Sort (cost=222,503.23..222,503.24 rows=1 width=61) (actual time=764.550..764.550 rows=0 loops=1)

  • Sort Key: b.restriction_type, (COALESCE(b.closed_date, b.finalized_date, b.mod_time))
  • Sort Method: quicksort Memory: 25kB
2. 0.000 764.532 ↓ 0.0 0 1

Nested Loop Left Join (cost=510.94..222,503.22 rows=1 width=61) (actual time=764.532..764.532 rows=0 loops=1)

  • Join Filter: ((store_sales_main.bill_no)::text = (b.bill_no)::text)
3. 0.047 764.532 ↓ 0.0 0 1

Nested Loop Anti Join (cost=499.82..222,489.17 rows=1 width=61) (actual time=764.532..764.532 rows=0 loops=1)

4. 0.022 0.253 ↓ 4.0 4 1

Nested Loop (cost=0.86..16.62 rows=1 width=61) (actual time=0.130..0.253 rows=4 loops=1)

5. 0.051 0.051 ↓ 2.5 10 1

Index Scan using patient_registration_mr_no_index on patient_registration pr (cost=0.43..5.96 rows=4 width=27) (actual time=0.027..0.051 rows=10 loops=1)

  • Index Cond: ((mr_no)::text = 'GBAJ.0000000145'::text)
6. 0.180 0.180 ↓ 0.0 0 10

Index Scan using bill_visit_id_idx on bill b (cost=0.43..2.66 rows=1 width=57) (actual time=0.017..0.018 rows=0 loops=10)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: ((deposit_set_off > 0::numeric) OR (COALESCE(ip_deposit_set_off, 0::numeric) > 0::numeric))
  • Rows Removed by Filter: 0
7. 7.840 764.232 ↑ 28.0 1 4

Hash Join (cost=498.96..111,485.62 rows=28 width=11) (actual time=191.058..191.058 rows=1 loops=4)

  • Hash Cond: (""*SELECT* 1"".common_order_id = bc.order_number)
8. 7.603 756.280 ↑ 1.5 28,797 4

Append (cost=497.11..111,321.50 rows=43,196 width=4) (actual time=2.307..189.070 rows=28,797 loops=4)

9. 2.620 451.188 ↓ 1.5 7,416 4

Subquery Scan on "*SELECT* 1" (cost=497.11..23,430.50 rows=4,834 width=4) (actual time=2.306..112.797 rows=7,416 loops=4)

10. 12.224 448.568 ↓ 1.5 7,416 4

Nested Loop (cost=497.11..23,382.16 rows=4,834 width=8) (actual time=2.306..112.142 rows=7,416 loops=4)

11. 261.056 399.848 ↑ 1.1 4,562 4

Hash Join (cost=496.68..18,953.70 rows=5,245 width=8) (actual time=2.296..99.962 rows=4,562 loops=4)

  • Hash Cond: (pp.package_id = p.package_id)
12. 132.496 132.496 ↑ 1.2 531,212 4

Seq Scan on package_prescribed pp (cost=0.00..16,034.14 rows=632,114 width=8) (actual time=0.002..33.124 rows=531,212 loops=4)

13. 0.061 6.296 ↑ 1.0 141 1

Hash (cost=494.92..494.92 rows=141 width=4) (actual time=6.296..6.296 rows=141 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
14. 6.235 6.235 ↑ 1.0 141 1

Seq Scan on pack_master p (cost=0.00..494.92 rows=141 width=4) (actual time=0.037..6.235 rows=141 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 16,851
15. 36.496 36.496 ↑ 6.5 2 18,248

Index Scan using services_prescribed_package_ref_is_null_idx on services_prescribed sp (cost=0.42..0.71 rows=13 width=8) (actual time=0.002..0.002 rows=2 loops=18,248)

  • Index Cond: (package_ref = pp.prescription_id)
16. 6.630 297.489 ↑ 1.2 28,509 3

Subquery Scan on "*SELECT* 2" (cost=497.12..59,597.97 rows=34,576 width=4) (actual time=1.371..99.163 rows=28,509 loops=3)

17. 12.132 290.859 ↑ 1.2 28,509 3

Nested Loop (cost=497.12..59,252.21 rows=34,576 width=8) (actual time=1.371..96.953 rows=28,509 loops=3)

18. 129.423 208.581 ↑ 1.3 3,897 3

Hash Join (cost=496.68..18,953.70 rows=5,245 width=8) (actual time=1.363..69.527 rows=3,897 loops=3)

  • Hash Cond: (pp_1.package_id = p_1.package_id)
19. 76.485 76.485 ↑ 1.5 423,024 3

Seq Scan on package_prescribed pp_1 (cost=0.00..16,034.14 rows=632,114 width=8) (actual time=0.002..25.495 rows=423,024 loops=3)

20. 0.025 2.673 ↑ 1.0 141 1

Hash (cost=494.92..494.92 rows=141 width=4) (actual time=2.673..2.673 rows=141 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
21. 2.648 2.648 ↑ 1.0 141 1

Seq Scan on pack_master p_1 (cost=0.00..494.92 rows=141 width=4) (actual time=0.018..2.648 rows=141 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 16,851
22. 70.146 70.146 ↑ 32.3 7 11,691

Index Scan using tests_prescribed_package_ref_is_null_idx on tests_prescribed tp (cost=0.43..5.42 rows=226 width=8) (actual time=0.003..0.006 rows=7 loops=11,691)

  • Index Cond: (package_ref = pp_1.prescription_id)
23. 0.000 0.000 ↓ 0.0 0

Subquery Scan on "*SELECT* 3" (cost=497.55..27,902.98 rows=3,785 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=497.55..27,865.13 rows=3,785 width=8) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=497.11..25,355.16 rows=3,785 width=12) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=496.68..18,953.70 rows=5,245 width=8) (never executed)

  • Hash Cond: (pp_2.package_id = p_2.package_id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on package_prescribed pp_2 (cost=0.00..16,034.14 rows=632,114 width=8) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash (cost=494.92..494.92 rows=141 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on pack_master p_2 (cost=0.00..494.92 rows=141 width=4) (never executed)

  • Filter: multi_visit_package
30. 0.000 0.000 ↓ 0.0 0

Index Scan using doctor_consultation_pkg_ref_idx on doctor_consultation dc (cost=0.43..0.97 rows=25 width=12) (never executed)

  • Index Cond: (package_ref = pp_2.prescription_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.44..0.65 rows=1 width=16) (never executed)

  • Index Cond: ((activity_id)::text = (dc.consultation_id)::text)
  • Filter: ((activity_code)::text = 'DOC'::text)
32. 0.000 0.000 ↓ 0.0 0

Subquery Scan on "*SELECT* 4" (cost=0.71..390.05 rows=1 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..390.04 rows=1 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..389.72 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on other_services_prescribed osp (cost=0.00..4.48 rows=148 width=8) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using package_prescribed_pkey on package_prescribed pp_3 (cost=0.42..2.59 rows=1 width=8) (never executed)

  • 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. 0.020 0.112 ↑ 5.5 6 4

Hash (cost=1.44..1.44 rows=33 width=15) (actual time=0.028..0.028 rows=6 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
39. 0.092 0.092 ↑ 5.5 6 4

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.43..1.44 rows=33 width=15) (actual time=0.016..0.023 rows=6 loops=4)

  • Index Cond: ((b.bill_no)::text = (bill_no)::text)
40. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=11.12..12.03 rows=90 width=48) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on store_sales_main (cost=0.00..10.90 rows=90 width=48) (never executed)

Total runtime : 765.086 ms