explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F4vv : measured with results_test - db.t2.large

Settings
# exclusive inclusive rows x rows loops node
1. 82,743.598 287,793.520 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,969.05..3,723,667.46 rows=17,572,738 width=242) (actual time=12,906.364..287,793.520 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=19,443 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
2. 12,058.968 205,049.438 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,945.03..3,237,955.63 rows=17,572,738 width=353) (actual time=12,905.851..205,049.438 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=19,437 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
3. 11,310.464 192,989.986 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,921.01..3,191,642.77 rows=17,572,738 width=347) (actual time=12,905.360..192,989.986 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=19,431 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
4. 10,986.037 181,679.492 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,919.15..3,141,472.18 rows=17,572,738 width=336) (actual time=12,905.325..181,679.492 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=19,430 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
5. 12,070.093 170,693.400 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,916.35..3,094,727.60 rows=17,572,738 width=329) (actual time=12,905.262..170,693.400 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=19,429 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
6. 10,964.169 158,614.575 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,449.13..3,048,117.82 rows=17,572,738 width=286) (actual time=12,896.511..158,614.575 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=19,273 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
7. 12,722.382 147,650.355 ↓ 1.0 18,224,010 1

Hash Join (cost=58,446.69..2,998,932.31 rows=17,572,738 width=270) (actual time=12,896.448..147,650.355 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=19,272 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
8. 12,233.700 134,926.960 ↓ 1.0 18,224,010 1

Hash Join (cost=58,392.63..2,952,620.68 rows=17,572,738 width=222) (actual time=12,895.427..134,926.960 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=19,252 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
9. 10,829.099 122,692.530 ↓ 1.0 18,224,010 1

Hash Join (cost=58,347.64..2,906,258.70 rows=17,584,698 width=217) (actual time=12,894.690..122,692.530 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=19,235 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
10. 31,355.186 111,863.394 ↓ 1.0 18,224,010 1

Hash Join (cost=58,344.83..2,856,376.22 rows=17,855,232 width=194) (actual time=12,894.647..111,863.394 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=19,234 read=815,494, temp read=561,934 written=561,934
  • I/O Timings: read=32,190.659
11. 26,042.770 79,663.227 ↓ 1.0 18,224,010 1

Hash Join (cost=7,642.23..1,978,980.43 rows=17,855,232 width=146) (actual time=9,166.070..79,663.227 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=3,475 read=815,494, temp read=215,730 written=215,730
  • I/O Timings: read=32,190.659
12. 12,075.425 53,522.093 ↓ 1.0 18,224,010 1

Hash Join (cost=60.13..1,260,679.79 rows=17,855,232 width=126) (actual time=8,871.184..53,522.093 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=19 read=815,494
  • I/O Timings: read=32,190.659
13. 41,445.609 41,445.609 ↓ 1.0 18,224,010 1

Seq Scan on dataset.results_test r (cost=0.00..1,213,641.01 rows=17,855,232 width=142) (actual time=8,870.112..41,445.609 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=32,190.659
14. 0.560 1.059 ↑ 1.0 1,828 1

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

  • Output: am.ukey
  • Buckets: 2,048 Batches: 1 Memory Usage: 102kB
  • Buffers: shared hit=19
15. 0.499 0.499 ↑ 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.007..0.499 rows=1,828 loops=1)

  • Output: am.ukey
  • Buffers: shared hit=19
16. 52.063 98.364 ↑ 1.0 127,871 1

Hash (cost=4,734.71..4,734.71 rows=127,871 width=52) (actual time=98.363..98.364 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=3,456, temp written=805
17. 46.301 46.301 ↑ 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.005..46.301 rows=127,871 loops=1)

  • Output: ml.key, ml.coordinate_normalized, ml.ukey
  • Buffers: shared hit=3,456
18. 491.646 844.981 ↑ 1.0 992,827 1

Hash (cost=25,687.27..25,687.27 rows=992,827 width=80) (actual time=844.980..844.981 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=15,759, temp written=11,747
19. 353.335 353.335 ↑ 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.009..353.335 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=15,759
20. 0.011 0.037 ↑ 3.1 21 1

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

  • Output: at.value, at.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
21. 0.026 0.026 ↑ 3.1 21 1

Seq Scan on value.activity_types at (cost=0.00..1.99 rows=65 width=55) (actual time=0.005..0.026 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 hit=1
22. 0.374 0.730 ↑ 1.0 1,244 1

Hash (cost=29.44..29.44 rows=1,244 width=37) (actual time=0.729..0.730 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=17
23. 0.356 0.356 ↑ 1.0 1,244 1

Seq Scan on dataset.detection_quantitation_limits dql (cost=0.00..29.44 rows=1,244 width=37) (actual time=0.004..0.356 rows=1,244 loops=1)

  • Output: dql.measure_normalized, dql.unit_normalized, dql.ukey
  • Buffers: shared hit=17
24. 0.553 1.013 ↑ 1.0 1,514 1

Hash (cost=35.14..35.14 rows=1,514 width=80) (actual time=1.012..1.013 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=20
25. 0.460 0.460 ↑ 1.0 1,514 1

Seq Scan on dataset.parameters p (cost=0.00..35.14 rows=1,514 width=80) (actual time=0.006..0.460 rows=1,514 loops=1)

  • Output: p.characteristic, p.method_speciation, p.sample_fraction, p.unit_normalized, p.ukey
  • Buffers: shared hit=20
26. 0.027 0.051 ↑ 1.0 64 1

Hash (cost=1.64..1.64 rows=64 width=32) (actual time=0.050..0.051 rows=64 loops=1)

  • Output: amn.value, amn.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
27. 0.024 0.024 ↑ 1.0 64 1

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

  • Output: amn.value, amn.id
  • Buffers: shared hit=1
28. 4.843 8.732 ↑ 1.0 13,832 1

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

  • Output: cn.value, cn.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 1,367kB
  • Buffers: shared hit=156
29. 3.889 3.889 ↑ 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.005..3.889 rows=13,832 loops=1)

  • Output: cn.value, cn.id
  • Buffers: shared hit=156
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.265 0.484 ↑ 1.0 801 1

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.483..0.484 rows=801 loops=1)

  • Output: pu.value, pu.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
  • Buffers: shared hit=6
35. 0.219 0.219 ↑ 1.0 801 1

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

  • Output: pu.value, pu.id
  • Buffers: shared hit=6
36. 0.269 0.484 ↑ 1.0 801 1

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.483..0.484 rows=801 loops=1)

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

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

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