explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fn5s : db.t2.large

Settings
# exclusive inclusive rows x rows loops node
1. 86,279.702 288,020.377 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,972.05..3,718,616.89 rows=17,534,487 width=242) (actual time=11,772.036..288,020.377 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, ml.key, ml.coordinate_normalized, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, uuid_generate_v5('00000000-0000-0000-0000-000000000000'::uuid, concat(amn.value, ',', cn.value, ',', cms.value, ',', csf.value, ',', (substr((at.value)::text, 1, 1) = 'F'::text), ',', COALESCE(pu.value, dqlu.value, ''::character varying))), a.media, COALESCE(p.characteristic, 'e129f27c-5103-5c5c-844b-cdf0a15e160d'::uuid), COALESCE(p.method_speciation, 'e129f27c-5103-5c5c-844b-cdf0a15e160d'::uuid), COALESCE(p.sample_fraction, 'e129f27c-5103-5c5c-844b-cdf0a15e160d'::uuid), CASE WHEN (substr((at.value)::text, 1, 1) = 'F'::text) THEN true ELSE false END, CASE WHEN (r.detection_condition = '0fe4f690-4bbb-5748-95bd-6f927f61b175'::uuid) THEN 3 WHEN (r.detection_condition = '440afe45-ec4b-50c8-987f-e713cf203bd0'::uuid) THEN 2 WHEN (r.detection_condition = '9bec08aa-7cd8-5f39-9be6-fcb64deef260'::uuid) THEN 1 WHEN (r.detection_condition = 'd48d8097-aa99-536f-bff5-c497287c4d87'::uuid) THEN 0 ELSE NULL::integer END, COALESCE(r.measure_normalized, dql.measure_normalized), COALESCE(p.unit_normalized, dql.unit_normalized, 'e129f27c-5103-5c5c-844b-cdf0a15e160d'::uuid), r.guidelines, r.create_timestamp, r.update_timestamp
  • Inner Unique: true
  • Hash Cond: (dql.unit_normalized = dqlu.id)
  • Buffers: shared hit=35 read=834,911 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,756.265
2. 12,481.364 201,740.194 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,948.03..3,233,962.26 rows=17,534,487 width=353) (actual time=11,771.499..201,740.194 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value, amn.value, cn.value, cms.value, csf.value, pu.value
  • Inner Unique: true
  • Hash Cond: (p.unit_normalized = pu.id)
  • Buffers: shared hit=26 read=834,911 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,756.265
3. 11,687.352 189,258.321 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,924.01..3,187,750.15 rows=17,534,487 width=347) (actual time=11,770.983..189,258.321 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value, amn.value, cn.value, cms.value, csf.value
  • Inner Unique: true
  • Hash Cond: (p.sample_fraction = csf.id)
  • Buffers: shared hit=25 read=834,906 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,756.256
4. 11,280.949 177,570.939 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,922.15..3,137,688.78 rows=17,534,487 width=336) (actual time=11,770.946..177,570.939 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value, amn.value, cn.value, cms.value
  • Inner Unique: true
  • Hash Cond: (p.method_speciation = cms.id)
  • Buffers: shared hit=24 read=834,906 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,756.256
5. 12,454.126 166,289.935 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,919.35..3,091,045.95 rows=17,534,487 width=329) (actual time=11,770.884..166,289.935 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value, amn.value, cn.value
  • Inner Unique: true
  • Hash Cond: (p.characteristic = cn.id)
  • Buffers: shared hit=23 read=834,906 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,756.256
6. 11,292.908 153,826.159 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,452.13..3,044,536.59 rows=17,534,487 width=286) (actual time=11,761.214..153,826.159 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value, amn.value
  • Inner Unique: true
  • Hash Cond: (a.media = amn.id)
  • Buffers: shared hit=21 read=834,752 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,755.468
7. 13,279.703 142,533.182 ↓ 1.0 18,224,010 1

Hash Join (cost=58,449.69..2,995,458.14 rows=17,534,487 width=270) (actual time=11,761.133..142,533.182 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value
  • Inner Unique: true
  • Hash Cond: (r.parameter_ukey = p.ukey)
  • Buffers: shared hit=21 read=834,751 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,755.465
8. 12,667.083 129,252.420 ↓ 1.0 18,224,010 1

Hash Join (cost=58,393.63..2,949,245.20 rows=17,534,487 width=222) (actual time=11,760.047..129,252.420 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, r.parameter_ukey, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, dql.measure_normalized, dql.unit_normalized, ml.key, ml.coordinate_normalized, at.value
  • Inner Unique: true
  • Hash Cond: (r.detection_quantitation_limit_ukey = dql.ukey)
  • Join Filter: ((r.measure_normalized IS NOT NULL) OR (dql.measure_normalized IS NOT NULL))
  • Buffers: shared hit=10 read=834,737 dirtied=1, temp read=562,357 written=562,357
  • I/O Timings: read=25,755.422
9. 10,961.779 116,584.479 ↓ 1.0 18,224,010 1

Hash Join (cost=58,347.64..2,902,981.77 rows=17,546,908 width=217) (actual time=11,759.180..116,584.479 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, r.parameter_ukey, r.detection_quantitation_limit_ukey, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, ml.key, ml.coordinate_normalized, at.value
  • Inner Unique: true
  • Hash Cond: (a.type = at.id)
  • Buffers: shared hit=6 read=834,723, temp read=562,357 written=562,357
  • I/O Timings: read=25,755.380
10. 32,147.461 105,622.659 ↓ 1.0 18,224,010 1

Hash Join (cost=58,344.83..2,853,206.49 rows=17,816,860 width=194) (actual time=11,759.132..105,622.659 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, r.parameter_ukey, r.detection_quantitation_limit_ukey, a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, a.type, ml.key, ml.coordinate_normalized
  • Inner Unique: true
  • Hash Cond: (r.activity_ukey = a.ukey)
  • Buffers: shared hit=6 read=834,722, temp read=562,357 written=562,357
  • I/O Timings: read=25,755.377
11. 25,392.054 72,581.599 ↓ 1.0 18,224,010 1

Hash Join (cost=7,642.23..1,977,559.42 rows=17,816,860 width=146) (actual time=8,472.039..72,581.599 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, r.activity_ukey, r.parameter_ukey, r.detection_quantitation_limit_ukey, ml.key, ml.coordinate_normalized
  • Inner Unique: true
  • Hash Cond: (r.monitoring_location_ukey = ml.ukey)
  • Buffers: shared hit=4 read=818,965, temp read=215,932 written=215,932
  • I/O Timings: read=25,694.187
12. 12,243.410 47,076.828 ↓ 1.0 18,224,010 1

Hash Join (cost=60.13..1,260,783.52 rows=17,816,860 width=126) (actual time=8,223.595..47,076.828 rows=18,224,010 loops=1)

  • Output: r.key, r.dataset_id, r.detection_condition, r.measure_normalized, r.guidelines, r.create_timestamp, r.update_timestamp, r.activity_ukey, r.parameter_ukey, r.detection_quantitation_limit_ukey, r.monitoring_location_ukey
  • Inner Unique: true
  • Hash Cond: (r.analytical_method_ukey = am.ukey)
  • Buffers: shared hit=2 read=815,511
  • I/O Timings: read=25,681.068
13. 34,832.325 34,832.325 ↓ 1.0 18,224,010 1

Seq Scan on dataset.results_test r (cost=0.00..1,213,845.70 rows=17,816,860 width=142) (actual time=8,222.484..34,832.325 rows=18,224,010 loops=1)

  • Output: r.key, r.ukey, r.dataset_id, r.monitoring_location_ukey, r.parameter_ukey, r.activity_ukey, r.analytical_method_ukey, r.detection_quantitation_limit_ukey, r.measure, r.value_type, r.detection_condition, r.status_id, r.comment, r.analysis_start_date, r.analysis_start_time, r.analysis_start_time_zone, r.laboratory, r.laboratory_sample_id, r.measure_normalized, r.guidelines, r.embargo, r.create_timestamp, r.update_timestamp
  • Filter: (((r.detection_condition = ANY ('{0fe4f690-4bbb-5748-95bd-6f927f61b175,440afe45-ec4b-50c8-987f-e713cf203bd0}'::uuid[])) OR (r.detection_condition IS NULL)) AND (r.dataset_id = 'f3877597-9114-4ace-ad6f-e8a68435c0ba'::uuid))
  • Rows Removed by Filter: 8,316,850
  • Buffers: shared read=815,494
  • I/O Timings: read=25,681.020
14. 0.539 1.093 ↑ 1.0 1,828 1

Hash (cost=37.28..37.28 rows=1,828 width=16) (actual time=1.091..1.093 rows=1,828 loops=1)

  • Output: am.ukey
  • Buckets: 2,048 Batches: 1 Memory Usage: 102kB
  • Buffers: shared hit=2 read=17
  • I/O Timings: read=0.048
15. 0.554 0.554 ↑ 1.0 1,828 1

Seq Scan on dataset.analytical_methods am (cost=0.00..37.28 rows=1,828 width=16) (actual time=0.023..0.554 rows=1,828 loops=1)

  • Output: am.ukey
  • Buffers: shared hit=2 read=17
  • I/O Timings: read=0.048
16. 53.412 112.717 ↑ 1.0 127,871 1

Hash (cost=4,734.71..4,734.71 rows=127,871 width=52) (actual time=112.715..112.717 rows=127,871 loops=1)

  • Output: ml.key, ml.coordinate_normalized, ml.ukey
  • Buckets: 65,536 Batches: 4 Memory Usage: 3,065kB
  • Buffers: shared hit=2 read=3,454, temp written=805
  • I/O Timings: read=13.119
17. 59.305 59.305 ↑ 1.0 127,871 1

Seq Scan on dataset.monitoring_locations ml (cost=0.00..4,734.71 rows=127,871 width=52) (actual time=0.010..59.305 rows=127,871 loops=1)

  • Output: ml.key, ml.coordinate_normalized, ml.ukey
  • Buffers: shared hit=2 read=3,454
  • I/O Timings: read=13.119
18. 487.256 893.599 ↑ 1.0 992,827 1

Hash (cost=25,687.27..25,687.27 rows=992,827 width=80) (actual time=893.598..893.599 rows=992,827 loops=1)

  • Output: a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, a.ukey, a.type
  • Buckets: 65,536 Batches: 32 Memory Usage: 3,894kB
  • Buffers: shared hit=2 read=15,757, temp written=11,747
  • I/O Timings: read=61.190
19. 406.343 406.343 ↑ 1.0 992,827 1

Seq Scan on dataset.activities a (cost=0.00..25,687.27 rows=992,827 width=80) (actual time=0.012..406.343 rows=992,827 loops=1)

  • Output: a.depth_height_measure, a.start_date, a.start_time, a.start_time_zone, a.media, a.ukey, a.type
  • Buffers: shared hit=2 read=15,757
  • I/O Timings: read=61.190
20. 0.010 0.041 ↑ 3.1 21 1

Hash (cost=1.99..1.99 rows=65 width=55) (actual time=0.040..0.041 rows=21 loops=1)

  • Output: at.value, at.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared read=1
  • I/O Timings: read=0.003
21. 0.031 0.031 ↑ 3.1 21 1

Seq Scan on value.activity_types at (cost=0.00..1.99 rows=65 width=55) (actual time=0.010..0.031 rows=21 loops=1)

  • Output: at.value, at.id
  • Filter: (substr((at.value)::text, 1, 1) <> 'Q'::text)
  • Rows Removed by Filter: 45
  • Buffers: shared read=1
  • I/O Timings: read=0.003
22. 0.420 0.858 ↑ 1.0 1,244 1

Hash (cost=30.44..30.44 rows=1,244 width=37) (actual time=0.857..0.858 rows=1,244 loops=1)

  • Output: dql.measure_normalized, dql.unit_normalized, dql.ukey
  • Buckets: 2,048 Batches: 1 Memory Usage: 100kB
  • Buffers: shared hit=4 read=14 dirtied=1
  • I/O Timings: read=0.041
23. 0.438 0.438 ↑ 1.0 1,244 1

Seq Scan on dataset.detection_quantitation_limits dql (cost=0.00..30.44 rows=1,244 width=37) (actual time=0.003..0.438 rows=1,244 loops=1)

  • Output: dql.measure_normalized, dql.unit_normalized, dql.ukey
  • Buffers: shared hit=4 read=14 dirtied=1
  • I/O Timings: read=0.041
24. 0.531 1.059 ↑ 1.0 1,514 1

Hash (cost=37.14..37.14 rows=1,514 width=80) (actual time=1.058..1.059 rows=1,514 loops=1)

  • Output: p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, p.ukey
  • Buckets: 2,048 Batches: 1 Memory Usage: 151kB
  • Buffers: shared hit=8 read=14
  • I/O Timings: read=0.043
25. 0.528 0.528 ↑ 1.0 1,514 1

Seq Scan on dataset.parameters p (cost=0.00..37.14 rows=1,514 width=80) (actual time=0.007..0.528 rows=1,514 loops=1)

  • Output: p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, p.ukey
  • Buffers: shared hit=8 read=14
  • I/O Timings: read=0.043
26. 0.028 0.069 ↑ 1.0 64 1

Hash (cost=1.64..1.64 rows=64 width=32) (actual time=0.068..0.069 rows=64 loops=1)

  • Output: amn.value, amn.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared read=1
  • I/O Timings: read=0.004
27. 0.041 0.041 ↑ 1.0 64 1

Seq Scan on value.activity_media_names amn (cost=0.00..1.64 rows=64 width=32) (actual time=0.024..0.041 rows=64 loops=1)

  • Output: amn.value, amn.id
  • Buffers: shared read=1
  • I/O Timings: read=0.004
28. 4.991 9.650 ↑ 1.0 13,832 1

Hash (cost=294.32..294.32 rows=13,832 width=59) (actual time=9.649..9.650 rows=13,832 loops=1)

  • Output: cn.value, cn.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 1,367kB
  • Buffers: shared hit=2 read=154
  • I/O Timings: read=0.788
29. 4.659 4.659 ↑ 1.0 13,832 1

Seq Scan on value.characteristic_names cn (cost=0.00..294.32 rows=13,832 width=59) (actual time=0.009..4.659 rows=13,832 loops=1)

  • Output: cn.value, cn.id
  • Buffers: shared hit=2 read=154
  • I/O Timings: read=0.788
30. 0.030 0.055 ↑ 1.0 80 1

Hash (cost=1.80..1.80 rows=80 width=23) (actual time=0.054..0.055 rows=80 loops=1)

  • Output: cms.value, cms.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
31. 0.025 0.025 ↑ 1.0 80 1

Seq Scan on value.method_speciations cms (cost=0.00..1.80 rows=80 width=23) (actual time=0.005..0.025 rows=80 loops=1)

  • Output: cms.value, cms.id
  • Buffers: shared hit=1
32. 0.016 0.030 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=27) (actual time=0.029..0.030 rows=38 loops=1)

  • Output: csf.value, csf.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
33. 0.014 0.014 ↑ 1.0 38 1

Seq Scan on value.sample_fractions csf (cost=0.00..1.38 rows=38 width=27) (actual time=0.004..0.014 rows=38 loops=1)

  • Output: csf.value, csf.id
  • Buffers: shared hit=1
34. 0.281 0.509 ↑ 1.0 801 1

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.508..0.509 rows=801 loops=1)

  • Output: pu.value, pu.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
  • Buffers: shared hit=1 read=5
  • I/O Timings: read=0.009
35. 0.228 0.228 ↑ 1.0 801 1

Seq Scan on value.units pu (cost=0.00..14.01 rows=801 width=22) (actual time=0.008..0.228 rows=801 loops=1)

  • Output: pu.value, pu.id
  • Buffers: shared hit=1 read=5
  • I/O Timings: read=0.009
36. 0.271 0.481 ↑ 1.0 801 1

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.479..0.481 rows=801 loops=1)

  • Output: dqlu.value, dqlu.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
  • Buffers: shared hit=6
37. 0.210 0.210 ↑ 1.0 801 1

Seq Scan on value.units dqlu (cost=0.00..14.01 rows=801 width=22) (actual time=0.004..0.210 rows=801 loops=1)

  • Output: dqlu.value, dqlu.id
  • Buffers: shared hit=6
Planning time : 55.476 ms