explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TNQ9

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 245,104.813 ↓ 9.0 9 1

Limit (cost=1.83..360,224.23 rows=1 width=200) (actual time=245,008.959..245,104.813 rows=9 loops=1)

2. 0.093 245,104.810 ↓ 9.0 9 1

Nested Loop (cost=1.83..360,224.23 rows=1 width=200) (actual time=245,008.957..245,104.810 rows=9 loops=1)

  • Join Filter: (file_location.file_storage_root_id = file_storage_root.file_storage_root_id)
  • Rows Removed by Join Filter: 54
3. 0.012 245,045.740 ↓ 9.0 9 1

Nested Loop (cost=1.83..360,170.53 rows=1 width=150) (actual time=245,005.743..245,045.740 rows=9 loops=1)

4. 0.522 245,040.679 ↓ 9.0 9 1

Nested Loop (cost=1.26..360,168.00 rows=1 width=100) (actual time=245,003.656..245,040.679 rows=9 loops=1)

  • Join Filter: ((iec.image_equivalence_class_id = image_equivalence_class.image_equivalence_class_id) AND (iec.processing_status = image_equivalence_class.processing_status))
5. 11.514 245,037.349 ↓ 1.3 702 1

Merge Join (cost=0.84..359,497.44 rows=555 width=30) (actual time=15,394.163..245,037.349 rows=702 loops=1)

  • Merge Cond: (iec.image_equivalence_class_id = image_equivalence_class_out_image.image_equivalence_class_id)
6. 276.527 244,941.671 ↓ 1.3 704 1

Index Scan using image_equivalence_class_pk on image_equivalence_class iec (cost=0.42..381,300.14 rows=531 width=109) (actual time=15,360.017..244,941.671 rows=704 loops=1)

  • Filter: ((NOT hidden) AND (processing_status = 'Reviewed'::text) AND (review_status = 'Good'::text) AND ((SubPlan 5) = 'Computed Radiography Image Storage'::text))
  • Rows Removed by Filter: 175,907
7.          

SubPlan (for Index Scan)

8. 136.152 244,665.144 ↑ 1.0 1 136,152

Limit (cost=1.03..2.16 rows=1 width=21) (actual time=1.797..1.797 rows=1 loops=136,152)

9.          

Initplan (for Limit)

10. 0.000 13,615.200 ↑ 1.0 1 136,152

Limit (cost=0.44..0.46 rows=1 width=4) (actual time=0.100..0.100 rows=1 loops=136,152)

11. 13,615.200 13,615.200 ↑ 1,870.0 1 136,152

Index Only Scan using image_equivalence_class_input_image_uniq on image_equivalence_class_input_image i_1 (cost=0.44..40.86 rows=1,870 width=4) (actual time=0.100..0.100 rows=1 loops=136,152)

  • Index Cond: (image_equivalence_class_id = iec.image_equivalence_class_id)
  • Heap Fetches: 1,449
12. 230,913.792 230,913.792 ↑ 2.0 1 136,152

Index Scan using dicom_file_file_id_index on dicom_file (cost=0.57..2.83 rows=2 width=21) (actual time=1.696..1.696 rows=1 loops=136,152)

  • Index Cond: (file_id = $5)
13. 84.164 84.164 ↓ 1.0 151,365 1

Index Scan using image_equivalence_class_out_image_idx on image_equivalence_class_out_image (cost=0.42..5,815.93 rows=149,897 width=17) (actual time=0.026..84.164 rows=151,365 loops=1)

14. 2.808 2.808 ↓ 0.0 0 702

Index Scan using image_equivalence_class_pk on image_equivalence_class (cost=0.42..1.19 rows=1 width=83) (actual time=0.004..0.004 rows=0 loops=702)

  • Index Cond: (image_equivalence_class_id = image_equivalence_class_out_image.image_equivalence_class_id)
  • Filter: (visual_review_instance_id = 278)
  • Rows Removed by Filter: 1
15. 5.049 5.049 ↑ 1.0 1 9

Index Scan using file_location_file_id_idx on file_location (cost=0.57..2.52 rows=1 width=54) (actual time=0.561..0.561 rows=1 loops=9)

  • Index Cond: (file_id = image_equivalence_class_out_image.file_id)
16. 0.018 0.018 ↑ 1.0 7 9

Seq Scan on file_storage_root (cost=0.00..1.07 rows=7 width=35) (actual time=0.001..0.002 rows=7 loops=9)

17.          

SubPlan (for Nested Loop)

18. 0.027 0.054 ↑ 1.0 1 9

Aggregate (cost=45.54..45.55 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=9)

19. 0.027 0.027 ↑ 1,870.0 1 9

Index Only Scan using image_equivalence_class_input_image_uniq on image_equivalence_class_input_image i (cost=0.44..40.86 rows=1,870 width=4) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: (image_equivalence_class_id = image_equivalence_class.image_equivalence_class_id)
  • Heap Fetches: 9
20. 0.018 37.377 ↑ 1.0 1 9

Limit (cost=0.69..1.82 rows=1 width=6) (actual time=4.153..4.153 rows=1 loops=9)

21. 37.359 37.359 ↑ 3,831.0 1 9

Index Scan using file_series_uid_idx on file_series (cost=0.69..4,333.02 rows=3,831 width=6) (actual time=4.151..4.151 rows=1 loops=9)

  • Index Cond: (series_instance_uid = image_equivalence_class.series_instance_uid)
22. 0.000 21.528 ↑ 1.0 1 9

Limit (cost=1.25..5.16 rows=1 width=10) (actual time=2.392..2.392 rows=1 loops=9)

23. 0.027 21.528 ↑ 3,831.0 1 9

Nested Loop (cost=1.25..14,976.09 rows=3,831 width=10) (actual time=2.392..2.392 rows=1 loops=9)

24. 0.243 0.243 ↑ 3,831.0 1 9

Index Scan using file_series_uid_idx on file_series file_series_1 (cost=0.69..4,333.02 rows=3,831 width=4) (actual time=0.027..0.027 rows=1 loops=9)

  • Index Cond: (series_instance_uid = image_equivalence_class.series_instance_uid)
25. 21.258 21.258 ↑ 1.0 1 9

Index Scan using file_patient_pkey on file_patient (cost=0.56..2.77 rows=1 width=14) (actual time=2.362..2.362 rows=1 loops=9)

  • Index Cond: (file_id = file_series_1.file_id)
Planning time : 9.994 ms
Execution time : 245,105.094 ms