explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aq5v : Optimization for: Optimization for: Optimization for: plan #J5VP; plan #99Da; plan #xgdi

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.013 737.328 ↓ 0.0 0 1

Sort (cost=222,529.25..222,529.26 rows=1 width=61) (actual time=737.328..737.328 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 737.315 ↓ 0.0 0 1

Nested Loop Left Join (cost=536.97..222,529.24 rows=1 width=61) (actual time=737.315..737.315 rows=0 loops=1)

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

Nested Loop Anti Join (cost=525.85..222,515.19 rows=1 width=61) (actual time=737.315..737.315 rows=0 loops=1)

4. 0.021 0.227 ↓ 4.0 4 1

Nested Loop (cost=0.86..16.61 rows=1 width=61) (actual time=0.116..0.227 rows=4 loops=1)

5. 0.046 0.046 ↓ 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.029..0.046 rows=10 loops=1)

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

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

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 0
7. 8.044 737.044 ↑ 28.0 1 4

Hash Join (cost=524.99..111,511.65 rows=28 width=11) (actual time=184.261..184.261 rows=1 loops=4)

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

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

9. 2.884 428.312 ↓ 1.5 7,416 4

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

10. 12.344 425.428 ↓ 1.5 7,416 4

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

11. 235.807 376.588 ↑ 1.1 4,562 4

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

  • Hash Cond: (pp.package_id = p.package_id)
12. 134.480 134.480 ↑ 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.620 rows=531,212 loops=4)

13. 0.048 6.301 ↑ 1.0 141 1

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

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

Seq Scan on pack_master p (cost=0.00..494.92 rows=141 width=4) (actual time=0.039..6.253 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.393 292.800 ↑ 1.2 28,509 3

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

17. 11.997 286.407 ↑ 1.2 28,509 3

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

18. 128.664 204.264 ↑ 1.3 3,897 3

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

  • Hash Cond: (pp_1.package_id = p_1.package_id)
19. 72.930 72.930 ↑ 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.001..24.310 rows=423,024 loops=3)

20. 0.024 2.670 ↑ 1.0 141 1

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

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

Seq Scan on pack_master p_1 (cost=0.00..494.92 rows=141 width=4) (actual time=0.017..2.646 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.012 0.108 ↑ 5.5 6 4

Hash (cost=27.47..27.47 rows=33 width=15) (actual time=0.027..0.027 rows=6 loops=4)

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

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.43..27.47 rows=33 width=15) (actual time=0.016..0.024 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 : 737.676 ms