explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gpiC

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 513,388.700 ↓ 0.0 0 1

Unique (cost=27,552,136.17..27,554,097.47 rows=78,452 width=279) (actual time=513,388.700..513,388.700 rows=0 loops=1)

2. 0.017 513,388.699 ↓ 0.0 0 1

Sort (cost=27,552,136.17..27,552,332.30 rows=78,452 width=279) (actual time=513,388.699..513,388.699 rows=0 loops=1)

  • Sort Key: file_series.series_instance_uid, (COALESCE(ctp_file.project_name, 'UNKNOWN'::text)), (COALESCE(ctp_file.site_name, 'UNKNOWN'::text)), file_patient.patient_id, file_study.study_instance_uid, file_sop_common.sop_instance_uid, dicom_file.dicom_file_type, file_series.modality, file_patient.file_id
  • Sort Method: quicksort Memory: 25kB
3. 3,677.672 513,388.682 ↓ 0.0 0 1

Hash Join (cost=19,222,374.87..27,539,788.61 rows=78,452 width=279) (actual time=513,388.682..513,388.682 rows=0 loops=1)

  • Hash Cond: (activity_timepoint_file.activity_timepoint_id = activity_timepoint.activity_timepoint_id)
4. 14,729.360 509,710.895 ↓ 1.8 40,792,910 1

Merge Join (cost=19,222,354.62..27,453,961.22 rows=22,672,699 width=241) (actual time=276,716.162..509,710.895 rows=40,792,910 loops=1)

  • Merge Cond: (file_patient.file_id = file_series.file_id)
5. 14,509.473 443,453.926 ↓ 1.8 40,792,910 1

Merge Join (cost=19,221,888.56..25,112,269.93 rows=22,883,610 width=198) (actual time=273,410.390..443,453.926 rows=40,792,910 loops=1)

  • Merge Cond: (file_patient.file_id = file_sop_common.file_id)
6. 14,658.641 384,819.099 ↓ 1.8 40,792,910 1

Merge Join (cost=19,221,773.74..22,872,506.65 rows=22,883,610 width=134) (actual time=271,945.206..384,819.099 rows=40,792,910 loops=1)

  • Merge Cond: (file_patient.file_id = file_study.file_id)
7. 15,962.276 334,727.672 ↓ 1.8 40,792,910 1

Merge Join (cost=19,221,680.95..20,646,115.48 rows=22,883,610 width=71) (actual time=270,482.570..334,727.672 rows=40,792,910 loops=1)

  • Merge Cond: (file_patient.file_id = file.file_id)
8. 90,096.487 293,962.437 ↓ 1.7 40,792,910 1

Sort (cost=19,221,630.67..19,281,270.50 rows=23,855,930 width=67) (actual time=269,798.779..293,962.437 rows=40,792,910 loops=1)

  • Sort Key: file_patient.file_id
  • Sort Method: external merge Disk: 3361928kB
9. 51,544.365 203,865.950 ↓ 1.7 40,792,910 1

Hash Join (cost=11,277,369.92..14,579,037.24 rows=23,855,930 width=67) (actual time=133,857.022..203,865.950 rows=40,792,910 loops=1)

  • Hash Cond: (activity_timepoint_file.file_id = file_patient.file_id)
10. 18,495.033 18,495.033 ↓ 1.0 55,604,236 1

Seq Scan on activity_timepoint_file (cost=0.00..802,078.78 rows=55,604,078 width=8) (actual time=0.014..18,495.033 rows=55,604,236 loops=1)

11. 6,426.630 133,826.552 ↓ 1.1 23,823,755 1

Hash (cost=10,784,052.23..10,784,052.23 rows=21,225,095 width=59) (actual time=133,826.552..133,826.552 rows=23,823,755 loops=1)

  • Buckets: 65536 Batches: 1024 Memory Usage: 2664kB
12. 32,004.094 127,399.922 ↓ 1.1 23,823,755 1

Hash Join (cost=6,930,972.92..10,784,052.23 rows=21,225,095 width=59) (actual time=72,153.838..127,399.922 rows=23,823,755 loops=1)

  • Hash Cond: (dicom_file.file_id = file_patient.file_id)
13. 23,250.378 23,250.378 ↓ 1.0 48,743,857 1

Seq Scan on dicom_file (cost=0.00..1,462,061.84 rows=48,140,384 width=24) (actual time=0.013..23,250.378 rows=48,743,857 loops=1)

14. 5,325.121 72,145.450 ↓ 1.1 23,815,365 1

Hash (cost=6,487,911.05..6,487,911.05 rows=21,812,229 width=35) (actual time=72,145.450..72,145.450 rows=23,815,365 loops=1)

  • Buckets: 65536 Batches: 512 Memory Usage: 3571kB
15. 34,275.128 66,820.329 ↓ 1.1 23,815,365 1

Hash Left Join (cost=1,745,773.73..6,487,911.05 rows=21,812,229 width=35) (actual time=23,317.424..66,820.329 rows=23,815,365 loops=1)

  • Hash Cond: (file_patient.file_id = ctp_file.file_id)
  • Filter: (ctp_file.visibility IS NULL)
  • Rows Removed by Filter: 24870596
16. 9,235.805 9,235.805 ↑ 1.0 48,685,961 1

Seq Scan on file_patient (cost=0.00..877,213.54 rows=49,472,054 width=13) (actual time=0.006..9,235.805 rows=48,685,961 loops=1)

17. 12,816.316 23,309.396 ↓ 1.1 43,857,526 1

Hash (cost=921,670.88..921,670.88 rows=40,571,188 width=33) (actual time=23,309.396..23,309.396 rows=43,857,526 loops=1)

  • Buckets: 65536 Batches: 1024 Memory Usage: 3167kB
18. 10,493.080 10,493.080 ↓ 1.1 43,857,526 1

Seq Scan on ctp_file (cost=0.00..921,670.88 rows=40,571,188 width=33) (actual time=0.494..10,493.080 rows=43,857,526 loops=1)

19. 24,802.959 24,802.959 ↓ 1.5 72,468,171 1

Index Only Scan using file_file_id_idx on file (cost=0.56..987,438.78 rows=47,455,672 width=4) (actual time=0.055..24,802.959 rows=72,468,171 loops=1)

  • Heap Fetches: 18426827
20. 35,432.786 35,432.786 ↓ 1.4 72,094,711 1

Index Scan using file_study_pkey on file_study (cost=0.56..1,834,046.36 rows=49,753,984 width=63) (actual time=2.468..35,432.786 rows=72,094,711 loops=1)

21. 44,125.354 44,125.354 ↓ 1.5 72,095,715 1

Index Scan using file_sop_common_pkey on file_sop_common (cost=0.56..1,872,783.00 rows=49,671,628 width=64) (actual time=5.333..44,125.354 rows=72,095,715 loops=1)

22. 51,527.609 51,527.609 ↓ 1.5 72,089,537 1

Index Scan using file_series_pkey on file_series (cost=0.56..1,991,008.35 rows=49,016,084 width=67) (actual time=1.996..51,527.609 rows=72,089,537 loops=1)

23. 0.001 0.115 ↑ 1.0 2 1

Hash (cost=20.23..20.23 rows=2 width=4) (actual time=0.115..0.115 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.114 0.114 ↑ 1.0 2 1

Seq Scan on activity_timepoint (cost=0.00..20.23 rows=2 width=4) (actual time=0.013..0.114 rows=2 loops=1)

  • Filter: (activity_id = 26)
  • Rows Removed by Filter: 582
Planning time : 16.338 ms
Execution time : 513,746.461 ms