explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kyyW

Settings
# exclusive inclusive rows x rows loops node
1. 49.260 75,104.481 ↓ 1.5 13,202 1

GroupAggregate (cost=2,918,851.14..2,919,100.40 rows=9,064 width=77) (actual time=75,048.045..75,104.481 rows=13,202 loops=1)

  • Group Key: ((bc.posted_date)::date), (COALESCE(rami.override_department, dit.dept_name, ramc.override_department, dch.dept_name, dep_1.dept_name, dep_2.dept_name, dep.dept_name, rami.allocation_department, ramc.allocation_department, '-NA-'::character varying)), b.visit_id, hcm.center_name
2. 248.328 75,055.221 ↓ 6.9 62,700 1

Sort (cost=2,918,851.14..2,918,873.80 rows=9,064 width=69) (actual time=75,047.996..75,055.221 rows=62,700 loops=1)

  • Sort Key: ((bc.posted_date)::date), (COALESCE(rami.override_department, dit.dept_name, ramc.override_department, dch.dept_name, dep_1.dept_name, dep_2.dept_name, dep.dept_name, rami.allocation_department, ramc.allocation_department, '-NA-'::character varying)), b.visit_id, hcm.center_name
  • Sort Method: quicksort Memory: 6,435kB
3. 56.329 74,806.893 ↓ 6.9 62,700 1

Hash Left Join (cost=80,307.52..2,918,255.36 rows=9,064 width=69) (actual time=15,365.566..74,806.893 rows=62,700 loops=1)

  • Hash Cond: ((bc.payee_doctor_id)::text = (doc.doctor_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'CDEPT'::text)
  • Rows Removed by Join Filter: 28,383
4. 43.424 74,749.023 ↓ 6.9 62,700 1

Hash Left Join (cost=80,113.98..2,917,923.12 rows=9,064 width=127) (actual time=15,364.002..74,749.023 rows=62,700 loops=1)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (doc_1.doctor_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'PDEPT'::text)
5. 42.733 74,703.942 ↓ 6.9 62,700 1

Hash Left Join (cost=79,920.45..2,917,635.37 rows=9,064 width=121) (actual time=15,362.320..74,703.942 rows=62,700 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc_2.doctor_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'ADEPT'::text)
  • Rows Removed by Join Filter: 62,700
6. 37.208 74,640.655 ↓ 6.9 62,700 1

Hash Left Join (cost=79,726.92..2,917,294.63 rows=9,064 width=116) (actual time=15,341.737..74,640.655 rows=62,700 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (ramc.charge_head)::text)
7. 0.000 74,603.218 ↓ 39.0 62,700 1

Gather (cost=79,702.16..2,917,090.80 rows=1,608 width=94) (actual time=15,341.492..74,603.218 rows=62,700 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 10.824 74,718.845 ↓ 31.2 20,900 3 / 3

Hash Left Join (cost=78,702.16..2,915,930.00 rows=670 width=94) (actual time=15,333.189..74,718.845 rows=20,900 loops=3)

  • Hash Cond: ((rami.allocation_department)::text = (dit.dept_id)::text)
9. 14.139 74,707.968 ↓ 31.2 20,900 3 / 3

Hash Left Join (cost=78,699.83..2,915,925.79 rows=670 width=81) (actual time=15,333.107..74,707.968 rows=20,900 loops=3)

  • Hash Cond: ((bc.act_description_id)::text = (rami.item_id)::text)
10. 14.236 74,692.930 ↓ 31.2 20,900 3 / 3

Hash Join (cost=78,663.33..2,915,884.22 rows=670 width=73) (actual time=15,332.170..74,692.930 rows=20,900 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
11. 121.805 74,678.591 ↓ 31.2 20,900 3 / 3

Hash Join (cost=78,649.34..2,915,868.30 rows=670 width=59) (actual time=15,331.992..74,678.591 rows=20,900 loops=3)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
12. 254.705 59,601.759 ↓ 5.7 146,955 3 / 3

Nested Loop (cost=0.56..2,837,151.53 rows=25,902 width=47) (actual time=2.931..59,601.759 rows=146,955 loops=3)

13. 42,006.403 42,006.403 ↓ 5.5 146,955 3 / 3

Parallel Seq Scan on bill_charge bc (cost=0.00..2,651,734.75 rows=26,760 width=46) (actual time=2.292..42,006.403 rows=146,955 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_group)::text = 'DIA'::text) AND ((posted_date)::date >= '2019-01-01'::date) AND ((posted_date)::date <= '2019-01-31'::date))
  • Rows Removed by Filter: 14,322,262
14. 17,340.651 17,340.651 ↑ 1.0 1 440,864 / 3

Index Scan using bill_pkey on bill b (cost=0.56..6.93 rows=1 width=29) (actual time=0.118..0.118 rows=1 loops=440,864)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
15. 202.590 14,955.027 ↑ 1.0 203,581 3 / 3

Hash (cost=76,005.32..76,005.32 rows=211,477 width=28) (actual time=14,955.027..14,955.027 rows=203,581 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 13,977kB
16. 14,752.437 14,752.437 ↑ 1.0 203,581 3 / 3

Index Scan using patient_registration_visit_type_idx on patient_registration pr (cost=0.43..76,005.32 rows=211,477 width=28) (actual time=0.614..14,752.437 rows=203,581 loops=3)

  • Index Cond: (visit_type = 'i'::bpchar)
17. 0.015 0.103 ↑ 1.0 44 3 / 3

Hash (cost=13.44..13.44 rows=44 width=22) (actual time=0.103..0.103 rows=44 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 0.088 0.088 ↑ 1.0 44 3 / 3

Seq Scan on hospital_center_master hcm (cost=0.00..13.44 rows=44 width=22) (actual time=0.024..0.088 rows=44 loops=3)

19. 0.281 0.899 ↑ 1.0 1,136 3 / 3

Hash (cost=21.78..21.78 rows=1,178 width=23) (actual time=0.899..0.899 rows=1,136 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 70kB
20. 0.618 0.618 ↑ 1.0 1,178 3 / 3

Seq Scan on rev_allocation_map rami (cost=0.00..21.78 rows=1,178 width=23) (actual time=0.233..0.618 rows=1,178 loops=3)

21. 0.022 0.053 ↑ 1.0 59 3 / 3

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.053..0.053 rows=59 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
22. 0.031 0.031 ↑ 1.0 59 3 / 3

Seq Scan on department dit (cost=0.00..1.59 rows=59 width=21) (actual time=0.021..0.031 rows=59 loops=3)

23. 0.015 0.229 ↑ 1.0 42 1

Hash (cost=24.23..24.23 rows=42 width=34) (actual time=0.229..0.229 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
24. 0.030 0.214 ↑ 1.0 42 1

Hash Left Join (cost=2.33..24.23 rows=42 width=34) (actual time=0.051..0.214 rows=42 loops=1)

  • Hash Cond: ((ramc.allocation_department)::text = (dch.dept_id)::text)
25. 0.156 0.156 ↑ 1.0 42 1

Seq Scan on rev_allocation_map ramc (cost=0.00..21.78 rows=42 width=21) (actual time=0.008..0.156 rows=42 loops=1)

  • Filter: (item_id IS NULL)
  • Rows Removed by Filter: 1,136
26. 0.018 0.028 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.028..0.028 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
27. 0.010 0.010 ↑ 1.0 59 1

Seq Scan on department dch (cost=0.00..1.59 rows=59 width=21) (actual time=0.003..0.010 rows=59 loops=1)

28. 0.572 20.554 ↑ 1.0 2,023 1

Hash (cost=168.25..168.25 rows=2,023 width=21) (actual time=20.554..20.554 rows=2,023 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
29. 0.924 19.982 ↑ 1.0 2,023 1

Hash Join (cost=2.33..168.25 rows=2,023 width=21) (actual time=0.594..19.982 rows=2,023 loops=1)

  • Hash Cond: ((doc_2.dept_id)::text = (dep_2.dept_id)::text)
30. 18.947 18.947 ↑ 1.0 2,023 1

Seq Scan on doctors doc_2 (cost=0.00..160.23 rows=2,023 width=16) (actual time=0.455..18.947 rows=2,023 loops=1)

31. 0.088 0.111 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.111..0.111 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
32. 0.023 0.023 ↑ 1.0 59 1

Seq Scan on department dep_2 (cost=0.00..1.59 rows=59 width=21) (actual time=0.007..0.023 rows=59 loops=1)

33. 0.452 1.657 ↑ 1.0 2,023 1

Hash (cost=168.25..168.25 rows=2,023 width=21) (actual time=1.657..1.657 rows=2,023 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
34. 0.896 1.205 ↑ 1.0 2,023 1

Hash Join (cost=2.33..168.25 rows=2,023 width=21) (actual time=0.057..1.205 rows=2,023 loops=1)

  • Hash Cond: ((doc_1.dept_id)::text = (dep_1.dept_id)::text)
35. 0.281 0.281 ↑ 1.0 2,023 1

Seq Scan on doctors doc_1 (cost=0.00..160.23 rows=2,023 width=16) (actual time=0.007..0.281 rows=2,023 loops=1)

36. 0.016 0.028 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.028..0.028 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
37. 0.012 0.012 ↑ 1.0 59 1

Seq Scan on department dep_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.005..0.012 rows=59 loops=1)

38. 0.450 1.541 ↑ 1.0 2,023 1

Hash (cost=168.25..168.25 rows=2,023 width=21) (actual time=1.541..1.541 rows=2,023 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
39. 0.784 1.091 ↑ 1.0 2,023 1

Hash Join (cost=2.33..168.25 rows=2,023 width=21) (actual time=0.061..1.091 rows=2,023 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dep.dept_id)::text)
40. 0.266 0.266 ↑ 1.0 2,023 1

Seq Scan on doctors doc (cost=0.00..160.23 rows=2,023 width=16) (actual time=0.004..0.266 rows=2,023 loops=1)

41. 0.029 0.041 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.041..0.041 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
42. 0.012 0.012 ↑ 1.0 59 1

Seq Scan on department dep (cost=0.00..1.59 rows=59 width=21) (actual time=0.006..0.012 rows=59 loops=1)