explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2fss

Settings
# exclusive inclusive rows x rows loops node
1. 10.729 29,676.984 ↓ 2,505.0 2,505 1

Nested Loop Left Join (cost=362,259.37..652,577.36 rows=1 width=9) (actual time=14,919.601..29,676.984 rows=2,505 loops=1)

  • Join Filter: (bsnphone.patient_demog_id = tmp.patient_demog_id)
  • Rows Removed by Join Filter: 17535
2. 83.186 29,641.205 ↓ 2,505.0 2,505 1

Nested Loop Left Join (cost=362,259.37..652,565.72 rows=1 width=17) (actual time=14,919.581..29,641.205 rows=2,505 loops=1)

  • Join Filter: (homephone.patient_demog_id = tmp.patient_demog_id)
  • Rows Removed by Join Filter: 212925
3. 1,831.747 29,465.334 ↓ 2,505.0 2,505 1

Hash Join (cost=362,259.37..652,554.08 rows=1 width=17) (actual time=14,919.501..29,465.334 rows=2,505 loops=1)

  • Hash Cond: (visit.msg_header_id = tmp.msg_header_id)
4. 12,717.231 12,717.231 ↓ 1.0 4,309,751 1

Seq Scan on patient_visit visit (cost=0.00..274,640.24 rows=4,174,524 width=8) (actual time=0.017..12,717.231 rows=4,309,751 loops=1)

5. 1.780 14,916.356 ↓ 2,505.0 2,505 1

Hash (cost=362,259.35..362,259.35 rows=1 width=41) (actual time=14,916.356..14,916.356 rows=2,505 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 228kB
6. 2,165.655 14,914.576 ↓ 2,505.0 2,505 1

Hash Right Join (cost=224,318.46..362,259.35 rows=1 width=41) (actual time=14,367.047..14,914.576 rows=2,505 loops=1)

  • Hash Cond: (address.patient_demog_id = tmp.patient_demog_id)
7. 7,268.096 7,268.096 ↑ 1.0 5,116,986 1

Seq Scan on patient_address address (cost=0.00..118,749.37 rows=5,117,737 width=8) (actual time=0.568..7,268.096 rows=5,116,986 loops=1)

8. 1.331 5,480.825 ↓ 2,258.0 2,258 1

Hash (cost=224,318.45..224,318.45 rows=1 width=41) (actual time=5,480.824..5,480.825 rows=2,258 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 209kB
9. 1,872.839 5,479.494 ↓ 2,258.0 2,258 1

Hash Right Join (cost=115,729.03..224,318.45 rows=1 width=41) (actual time=5,164.387..5,479.494 rows=2,258 loops=1)

  • Hash Cond: (race.patient_demog_id = tmp.patient_demog_id)
10. 1,913.734 1,913.734 ↑ 1.0 4,586,298 1

Seq Scan on patient_race race (cost=0.00..91,385.93 rows=4,587,593 width=8) (actual time=0.016..1,913.734 rows=4,586,298 loops=1)

11. 1.278 1,692.921 ↓ 2,258.0 2,258 1

Hash (cost=115,729.02..115,729.02 rows=1 width=41) (actual time=1,692.920..1,692.921 rows=2,258 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 209kB
12. 771.120 1,691.643 ↓ 2,258.0 2,258 1

Hash Join (cost=13,161.73..115,729.02 rows=1 width=41) (actual time=1,098.714..1,691.643 rows=2,258 loops=1)

  • Hash Cond: (dgn.msg_header_id = tmp.msg_header_id)
13. 917.434 917.434 ↓ 1.0 1,894,840 1

Seq Scan on diagnosis dgn (cost=0.00..95,715.43 rows=1,827,143 width=8) (actual time=0.004..917.434 rows=1,894,840 loops=1)

14. 0.131 3.089 ↓ 212.0 212 1

Hash (cost=13,161.71..13,161.71 rows=1 width=33) (actual time=3.085..3.089 rows=212 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
15. 0.380 2.958 ↓ 212.0 212 1

Nested Loop (cost=0.43..13,161.71 rows=1 width=33) (actual time=0.054..2.958 rows=212 loops=1)

16. 0.130 0.130 ↑ 5.1 306 1

Seq Scan on tmp_fls_ptnts tmp (cost=0.00..25.70 rows=1,570 width=16) (actual time=0.007..0.130 rows=306 loops=1)

17. 2.448 2.448 ↑ 1.0 1 306

Index Scan using patient_demog_pkey on patient_demog demog (cost=0.43..8.36 rows=1 width=25) (actual time=0.008..0.008 rows=1 loops=306)

  • Index Cond: (id = tmp.patient_demog_id)
  • Filter: ((tmp.msg_header_id = msg_header_id) AND ((facility_code)::text = ANY ('{709679,636349,MD0024}'::text[])))
  • Rows Removed by Filter: 0
18. 92.685 92.685 ↓ 85.0 85 2,505

Seq Scan on temp_phone homephone (cost=0.00..11.62 rows=1 width=8) (actual time=0.002..0.037 rows=85 loops=2,505)

  • Filter: ((phone_use_code)::text = 'PRN'::text)
  • Rows Removed by Filter: 7
19. 25.050 25.050 ↓ 7.0 7 2,505

Seq Scan on temp_phone bsnphone (cost=0.00..11.62 rows=1 width=8) (actual time=0.002..0.010 rows=7 loops=2,505)

  • Filter: ((phone_use_code)::text = 'WPN'::text)
  • Rows Removed by Filter: 85
Planning time : 9.920 ms
Execution time : 29,678.077 ms