explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ciqi

Settings
# exclusive inclusive rows x rows loops node
1. 852.858 2,780.977 ↓ 1.0 206,010 1

Hash Left Join (cost=105,781.72..363,387.38 rows=203,973 width=854) (actual time=708.613..2,780.977 rows=206,010 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (dfd.patient_id)::text)
2. 35.472 1,922.548 ↓ 1.0 205,974 1

Hash Left Join (cost=104,272.83..248,880.96 rows=203,973 width=653) (actual time=702.987..1,922.548 rows=205,974 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
3. 42.724 1,887.031 ↓ 1.0 205,974 1

Hash Left Join (cost=104,259.97..248,332.45 rows=203,973 width=631) (actual time=702.939..1,887.031 rows=205,974 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
4. 35.594 1,844.255 ↓ 1.0 205,974 1

Hash Left Join (cost=104,247.11..247,777.49 rows=203,973 width=609) (actual time=702.885..1,844.255 rows=205,974 loops=1)

  • Hash Cond: ((pr.secondary_insurance_co)::text = (sicm.insurance_co_id)::text)
5. 42.844 1,808.601 ↓ 1.0 205,974 1

Hash Left Join (cost=104,234.55..247,229.30 rows=203,973 width=586) (actual time=702.823..1,808.601 rows=205,974 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
6. 49.999 1,765.691 ↓ 1.0 205,974 1

Hash Left Join (cost=104,221.99..246,675.27 rows=203,973 width=563) (actual time=702.754..1,765.691 rows=205,974 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 54.594 1,715.494 ↓ 1.0 205,974 1

Hash Left Join (cost=104,173.79..246,089.46 rows=203,973 width=549) (actual time=702.553..1,715.494 rows=205,974 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dept.dept_id)::text)
8. 51.724 1,660.887 ↓ 1.0 205,974 1

Hash Join (cost=104,171.71..245,504.97 rows=203,973 width=339) (actual time=702.531..1,660.887 rows=205,974 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 216.441 1,609.150 ↓ 1.0 205,974 1

Hash Join (cost=104,170.31..244,842.06 rows=203,973 width=221) (actual time=702.511..1,609.150 rows=205,974 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
10. 292.993 1,063.766 ↓ 1.0 205,974 1

Hash Right Join (cost=66,931.92..190,435.24 rows=203,973 width=160) (actual time=372.954..1,063.766 rows=205,974 loops=1)

  • Hash Cond: ((md.visit_id)::text = (pr.patient_id)::text)
11. 398.057 398.057 ↑ 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.006..398.057 rows=949,996 loops=1)

  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 1,995,148
12. 63.629 372.716 ↓ 1.0 205,885 1

Hash (cost=60,796.25..60,796.25 rows=203,973 width=120) (actual time=372.716..372.716 rows=205,885 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 1,820kB
13. 309.087 309.087 ↓ 1.0 205,885 1

Seq Scan on patient_registration pr (cost=0.00..60,796.25 rows=203,973 width=120) (actual time=0.011..309.087 rows=205,885 loops=1)

  • Filter: ((codification_status = 'R'::bpchar) OR (codification_status = 'P'::bpchar))
  • Rows Removed by Filter: 794,320
14. 160.292 328.943 ↑ 1.0 588,096 1

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,486kB
15. 168.651 168.651 ↑ 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.006..168.651 rows=588,096 loops=1)

16. 0.004 0.013 ↑ 1.0 18 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.009 0.009 ↑ 1.0 18 1

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

18. 0.006 0.013 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.007 0.007 ↑ 1.0 48 1

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

20. 0.102 0.198 ↑ 1.0 990 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 77kB
21. 0.096 0.096 ↑ 1.0 990 1

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

22. 0.036 0.066 ↑ 1.0 336 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
23. 0.030 0.030 ↑ 1.0 336 1

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

24. 0.035 0.060 ↑ 1.0 336 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
25. 0.025 0.025 ↑ 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.025 rows=336 loops=1)

26. 0.026 0.052 ↑ 1.2 255 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
27. 0.026 0.026 ↑ 1.2 255 1

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

28. 0.026 0.045 ↑ 1.2 255 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
29. 0.019 0.019 ↑ 1.2 255 1

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

30. 2.374 5.571 ↑ 1.0 23,284 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,368kB
31. 3.197 3.197 ↑ 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.002..3.197 rows=23,284 loops=1)

Planning time : 4.655 ms
Execution time : 2,786.304 ms