explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2aML

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.552 1,562.853 ↓ 1.2 747 1

Subquery Scan on t (cost=36,700.92..36,753.54 rows=619 width=4,317) (actual time=1,555.093..1,562.853 rows=747 loops=1)

2. 5.747 1,561.301 ↓ 1.2 747 1

Result (cost=36,700.92..36,747.35 rows=619 width=8,217) (actual time=1,555.089..1,561.301 rows=747 loops=1)

3. 2.424 1,555.554 ↓ 1.2 747 1

Sort (cost=36,700.92..36,702.47 rows=619 width=8,185) (actual time=1,555.063..1,555.554 rows=747 loops=1)

  • Sort Key: s_1.study_timedate
  • Sort Method: quicksort Memory: 783kB
4. 2.656 1,553.130 ↓ 1.2 747 1

Nested Loop (cost=7,412.10..34,498.72 rows=619 width=8,185) (actual time=228.376..1,553.130 rows=747 loops=1)

5. 1.062 1,549.727 ↓ 1.2 747 1

Nested Loop (cost=7,411.96..34,386.17 rows=619 width=4,604) (actual time=228.349..1,549.727 rows=747 loops=1)

6. 1.063 1,547.918 ↓ 1.2 747 1

Nested Loop (cost=7,411.82..34,290.64 rows=619 width=4,555) (actual time=228.341..1,547.918 rows=747 loops=1)

7. 1.110 1,546.108 ↓ 1.2 747 1

Nested Loop (cost=7,411.68..34,193.34 rows=619 width=3,972) (actual time=228.334..1,546.108 rows=747 loops=1)

8. 0.829 1,544.251 ↓ 1.2 747 1

Nested Loop (cost=7,411.54..34,096.04 rows=619 width=3,389) (actual time=228.316..1,544.251 rows=747 loops=1)

9. 58.935 1,541.181 ↓ 1.2 747 1

Hash Join (cost=7,411.12..33,779.35 rows=619 width=3,336) (actual time=228.286..1,541.181 rows=747 loops=1)

  • Hash Cond: (s_1.patient_uid = p_1.id)
10. 146.527 1,411.767 ↓ 1.1 227,091 1

Hash Left Join (cost=38.08..25,600.27 rows=213,292 width=3,002) (actual time=88.387..1,411.767 rows=227,091 loops=1)

  • Hash Cond: (s_1.id = li.table_uid)
11. 152.452 1,265.233 ↓ 1.1 227,091 1

Hash Left Join (cost=37.06..24,799.40 rows=213,292 width=2,997) (actual time=88.373..1,265.233 rows=227,091 loops=1)

  • Hash Cond: ((s_1.priority)::text = (p_2.code)::text)
12. 149.983 1,112.769 ↓ 1.1 227,091 1

Hash Join (cost=36.02..23,764.80 rows=213,292 width=2,993) (actual time=88.330..1,112.769 rows=227,091 loops=1)

  • Hash Cond: (pi.modality_uid = m.id)
13. 164.723 962.755 ↓ 1.1 227,091 1

Hash Join (cost=34.57..23,084.81 rows=213,292 width=2,998) (actual time=88.290..962.755 rows=227,091 loops=1)

  • Hash Cond: (s_1.procedure_info_uid = pi.id)
14. 146.365 797.980 ↓ 1.1 227,091 1

Hash Join (cost=32.02..22,487.97 rows=213,292 width=2,948) (actual time=88.233..797.980 rows=227,091 loops=1)

  • Hash Cond: (s_1.local_ae_uid = lae.id)
15. 146.235 651.604 ↓ 1.1 227,091 1

Hash Join (cost=30.79..21,689.57 rows=213,292 width=2,943) (actual time=88.216..651.604 rows=227,091 loops=1)

  • Hash Cond: (s_1.site_uid = si.id)
16. 181.519 417.253 ↓ 1.1 227,091 1

Hash Join (cost=1.90..21,096.65 rows=213,292 width=2,898) (actual time=0.051..417.253 rows=227,091 loops=1)

  • Hash Cond: (s_1.requesting_physician_uid = rp_1.id)
17. 235.702 235.702 ↓ 1.1 227,091 1

Seq Scan on study s_1 (cost=0.00..20,475.54 rows=213,292 width=2,315) (actual time=0.011..235.702 rows=227,091 loops=1)

  • Filter: ((id > 0) AND ((obsolete)::text = 'N'::text) AND (status = 40))
  • Rows Removed by Filter: 153797
18. 0.015 0.032 ↑ 1.0 40 1

Hash (cost=1.40..1.40 rows=40 width=591) (actual time=0.032..0.032 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
19. 0.017 0.017 ↑ 1.0 40 1

Seq Scan on user_info rp_1 (cost=0.00..1.40 rows=40 width=591) (actual time=0.006..0.017 rows=40 loops=1)

20. 0.145 88.116 ↑ 1.0 573 1

Hash (cost=21.73..21.73 rows=573 width=61) (actual time=88.116..88.116 rows=573 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
21. 87.971 87.971 ↑ 1.0 573 1

Seq Scan on site si (cost=0.00..21.73 rows=573 width=61) (actual time=0.009..87.971 rows=573 loops=1)

22. 0.004 0.011 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=21) (actual time=0.011..0.011 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.007 0.007 ↑ 1.0 10 1

Seq Scan on local_ae lae (cost=0.00..1.10 rows=10 width=21) (actual time=0.005..0.007 rows=10 loops=1)

24. 0.024 0.052 ↑ 1.0 69 1

Hash (cost=1.69..1.69 rows=69 width=58) (actual time=0.052..0.052 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
25. 0.028 0.028 ↑ 1.0 69 1

Seq Scan on procedure_info pi (cost=0.00..1.69 rows=69 width=58) (actual time=0.013..0.028 rows=69 loops=1)

26. 0.010 0.031 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=11) (actual time=0.031..0.031 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.021 0.021 ↑ 1.0 20 1

Seq Scan on modality m (cost=0.00..1.20 rows=20 width=11) (actual time=0.017..0.021 rows=20 loops=1)

28. 0.003 0.012 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on priority p_2 (cost=0.00..1.02 rows=2 width=4) (actual time=0.009..0.009 rows=2 loops=1)

30. 0.001 0.007 ↓ 0.0 0 1

Hash (cost=1.00..1.00 rows=1 width=13) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
31. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on lock_info li (cost=0.00..1.00 rows=1 width=13) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((table_name)::text = 'STUDY'::text)
32. 1.232 70.479 ↑ 1.1 936 1

Hash (cost=7,360.14..7,360.14 rows=1,032 width=342) (actual time=70.479..70.479 rows=936 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 209kB
33. 0.227 69.247 ↑ 1.1 936 1

Nested Loop Left Join (cost=29.61..7,360.14 rows=1,032 width=342) (actual time=0.513..69.247 rows=936 loops=1)

34. 0.727 2.564 ↑ 1.1 936 1

Hash Join (cost=29.32..2,918.04 rows=1,032 width=310) (actual time=0.486..2.564 rows=936 loops=1)

  • Hash Cond: (p_1.site_uid = s.id)
35. 1.422 1.422 ↑ 1.1 936 1

Index Scan using patient_last_name_fn_idx on patient p_1 (cost=0.42..2,886.42 rows=1,032 width=212) (actual time=0.063..1.422 rows=936 loops=1)

  • Index Cond: ((upper(btrim((last_name)::text)) >= 'DICOM'::text) AND (upper(btrim((last_name)::text)) < 'DICON'::text))
  • Filter: ((id > 0) AND ((obsolete)::text = 'N'::text) AND (upper(btrim((last_name)::text)) ~~ 'DICOM%'::text))
36. 0.260 0.415 ↑ 1.0 573 1

Hash (cost=21.73..21.73 rows=573 width=114) (actual time=0.415..0.415 rows=573 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 93kB
37. 0.155 0.155 ↑ 1.0 573 1

Seq Scan on site s (cost=0.00..21.73 rows=573 width=114) (actual time=0.006..0.155 rows=573 loops=1)

38. 66.456 66.456 ↓ 0.0 0 936

Index Scan using alias_patient_patient_idx on alias_patient p (cost=0.29..4.29 rows=1 width=40) (actual time=0.071..0.071 rows=0 loops=936)

  • Index Cond: (patient_uid = p_1.id)
39. 2.241 2.241 ↑ 1.0 1 747

Index Scan using visit_pk on visit v (cost=0.42..0.51 rows=1 width=69) (actual time=0.003..0.003 rows=1 loops=747)

  • Index Cond: (id = s_1.visit_uid)
  • Filter: (obsolete = 'N'::bpchar)
40. 0.747 0.747 ↑ 1.0 1 747

Index Scan using user_info_pk on user_info rp (cost=0.14..0.16 rows=1 width=591) (actual time=0.001..0.001 rows=1 loops=747)

  • Index Cond: (id = v.referring_physician_uid)
41. 0.747 0.747 ↑ 1.0 1 747

Index Scan using user_info_pk on user_info ap (cost=0.14..0.16 rows=1 width=591) (actual time=0.001..0.001 rows=1 loops=747)

  • Index Cond: (id = v.attending_physician_uid)
42. 0.747 0.747 ↑ 1.0 1 747

Index Scan using location_pk on location cl (cost=0.14..0.16 rows=1 width=57) (actual time=0.001..0.001 rows=1 loops=747)

  • Index Cond: (id = v.current_location_uid)
43. 0.747 0.747 ↑ 1.0 1 747

Index Scan using location_pk on location pl (cost=0.14..0.16 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=747)

  • Index Cond: (id = v.primary_location_uid)