explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WSQG

Settings
# exclusive inclusive rows x rows loops node
1. 89.794 1,333.369 ↑ 2.4 21,176 1

Hash Left Join (cost=95,467.95..254,675.86 rows=49,773 width=854) (actual time=553.858..1,333.369 rows=21,176 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (dfd.patient_id)::text)
2. 3.990 1,236.187 ↑ 2.4 21,170 1

Hash Left Join (cost=93,959.06..225,593.58 rows=49,773 width=653) (actual time=546.410..1,236.187 rows=21,170 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
3. 5.238 1,232.143 ↑ 2.4 21,170 1

Hash Left Join (cost=93,946.20..225,450.01 rows=49,773 width=631) (actual time=546.354..1,232.143 rows=21,170 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
4. 4.078 1,226.837 ↑ 2.4 21,170 1

Hash Left Join (cost=93,933.34..225,304.87 rows=49,773 width=609) (actual time=546.267..1,226.837 rows=21,170 loops=1)

  • Hash Cond: ((pr.secondary_insurance_co)::text = (sicm.insurance_co_id)::text)
5. 4.724 1,222.687 ↑ 2.4 21,170 1

Hash Left Join (cost=93,920.78..225,161.61 rows=49,773 width=586) (actual time=546.193..1,222.687 rows=21,170 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
6. 5.304 1,217.880 ↑ 2.4 21,170 1

Hash Left Join (cost=93,908.22..225,016.92 rows=49,773 width=563) (actual time=546.106..1,217.880 rows=21,170 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 5.169 1,212.317 ↑ 2.4 21,170 1

Hash Left Join (cost=93,860.02..224,837.53 rows=49,773 width=549) (actual time=545.844..1,212.317 rows=21,170 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dept.dept_id)::text)
8. 5.527 1,207.130 ↑ 2.4 21,170 1

Hash Join (cost=93,857.94..224,693.34 rows=49,773 width=339) (actual time=545.819..1,207.130 rows=21,170 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 60.968 1,201.592 ↑ 2.4 21,170 1

Hash Join (cost=93,856.54..224,530.52 rows=49,773 width=221) (actual time=545.804..1,201.592 rows=21,170 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
10. 210.517 811.793 ↑ 2.4 21,170 1

Hash Join (cost=56,618.15..177,456.47 rows=49,773 width=160) (actual time=213.101..811.793 rows=21,170 loops=1)

  • Hash Cond: ((md.visit_id)::text = (pr.patient_id)::text)
11. 388.258 388.258 ↑ 1.0 949,996 1

Seq Scan on mrd_diagnosis md (cost=0.00..98,842.45 rows=951,187 width=56) (actual time=0.009..388.258 rows=949,996 loops=1)

  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 1995148
12. 12.297 213.018 ↑ 1.8 29,210 1

Hash (cost=55,042.91..55,042.91 rows=52,339 width=120) (actual time=213.018..213.018 rows=29,210 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 1190kB
13. 187.430 200.721 ↑ 1.8 29,210 1

Bitmap Heap Scan on patient_registration pr (cost=4,758.43..55,042.91 rows=52,339 width=120) (actual time=17.656..200.721 rows=29,210 loops=1)

  • Recheck Cond: (center_id = 9)
  • Filter: ((codification_status = 'R'::bpchar) OR (codification_status = 'P'::bpchar))
  • Rows Removed by Filter: 227233
  • Heap Blocks: exact=38012
14. 13.291 13.291 ↑ 1.0 256,517 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..4,745.35 rows=256,656 width=0) (actual time=13.291..13.291 rows=256,517 loops=1)

  • Index Cond: (center_id = 9)
15. 161.032 328.831 ↑ 1.0 588,096 1

Hash (cost=22,415.84..22,415.84 rows=588,284 width=76) (actual time=328.831..328.831 rows=588,096 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2486kB
16. 167.799 167.799 ↑ 1.0 588,096 1

Seq Scan on patient_details pd (cost=0.00..22,415.84 rows=588,284 width=76) (actual time=0.005..167.799 rows=588,096 loops=1)

17. 0.003 0.011 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=156) (actual time=0.011..0.011 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.008 0.008 ↑ 1.0 18 1

Seq Scan on salutation_master sm (cost=0.00..1.18 rows=18 width=156) (actual time=0.006..0.008 rows=18 loops=1)

19. 0.008 0.018 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=256) (actual time=0.018..0.018 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.010 0.010 ↑ 1.0 48 1

Seq Scan on department dept (cost=0.00..1.48 rows=48 width=256) (actual time=0.006..0.010 rows=48 loops=1)

21. 0.122 0.259 ↑ 1.0 990 1

Hash (cost=35.31..35.31 rows=1,031 width=30) (actual time=0.259..0.259 rows=990 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
22. 0.137 0.137 ↑ 1.0 990 1

Seq Scan on doctors dr (cost=0.00..35.31 rows=1,031 width=30) (actual time=0.005..0.137 rows=990 loops=1)

23. 0.043 0.083 ↑ 1.0 336 1

Hash (cost=8.36..8.36 rows=336 width=31) (actual time=0.083..0.083 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
24. 0.040 0.040 ↑ 1.0 336 1

Seq Scan on insurance_company_master icm (cost=0.00..8.36 rows=336 width=31) (actual time=0.005..0.040 rows=336 loops=1)

25. 0.043 0.072 ↑ 1.0 336 1

Hash (cost=8.36..8.36 rows=336 width=31) (actual time=0.072..0.072 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
26. 0.029 0.029 ↑ 1.0 336 1

Seq Scan on insurance_company_master sicm (cost=0.00..8.36 rows=336 width=31) (actual time=0.002..0.029 rows=336 loops=1)

27. 0.032 0.068 ↑ 1.2 255 1

Hash (cost=9.05..9.05 rows=305 width=32) (actual time=0.068..0.068 rows=255 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
28. 0.036 0.036 ↑ 1.2 255 1

Seq Scan on tpa_master tm (cost=0.00..9.05 rows=305 width=32) (actual time=0.005..0.036 rows=255 loops=1)

29. 0.032 0.054 ↑ 1.2 255 1

Hash (cost=9.05..9.05 rows=305 width=32) (actual time=0.054..0.054 rows=255 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
30. 0.022 0.022 ↑ 1.2 255 1

Seq Scan on tpa_master stm (cost=0.00..9.05 rows=305 width=32) (actual time=0.002..0.022 rows=255 loops=1)

31. 2.829 7.388 ↑ 1.0 23,284 1

Hash (cost=1,217.84..1,217.84 rows=23,284 width=65) (actual time=7.388..7.388 rows=23,284 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2368kB
32. 4.559 4.559 ↑ 1.0 23,284 1

Seq Scan on discharge_format_detail dfd (cost=0.00..1,217.84 rows=23,284 width=65) (actual time=0.004..4.559 rows=23,284 loops=1)

Planning time : 3.597 ms
Execution time : 1,334.147 ms