explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ML91 : measured with results - db.t2.medium

Settings
# exclusive inclusive rows x rows loops node
1. 87,605.480 445,868.723 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,969.05..4,557,700.77 rows=17,493,297 width=242) (actual time=51,688.911..445,868.723 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,507 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
2. 12,412.617 358,261.379 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,945.03..4,074,184.59 rows=17,493,297 width=353) (actual time=51,687.005..358,261.379 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,501 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
3. 11,534.062 345,846.292 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,921.01..4,028,080.98 rows=17,493,297 width=347) (actual time=51,683.955..345,846.292 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,495 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
4. 11,227.167 334,311.179 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,919.15..3,978,137.20 rows=17,493,297 width=336) (actual time=51,683.303..334,311.179 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,494 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
5. 12,323.198 323,082.964 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,916.35..3,931,603.92 rows=17,493,297 width=329) (actual time=51,682.222..323,082.964 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,493 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
6. 11,299.405 310,721.001 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,449.13..3,885,202.72 rows=17,493,297 width=286) (actual time=51,639.661..310,721.001 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,337 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
7. 13,233.134 299,420.618 ↓ 1.0 18,224,010 1

Hash Join (cost=58,446.69..3,836,239.55 rows=17,493,297 width=270) (actual time=51,638.025..299,420.618 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,336 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
8. 12,571.929 286,182.331 ↓ 1.0 18,224,010 1

Hash Join (cost=58,392.63..3,790,137.04 rows=17,493,297 width=222) (actual time=51,631.131..286,182.331 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,316 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
9. 10,925.968 273,608.900 ↓ 1.0 18,224,010 1

Hash Join (cost=58,347.64..3,743,984.45 rows=17,505,203 width=217) (actual time=51,629.612..273,608.900 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,299 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
10. 36,974.218 262,682.890 ↓ 1.0 18,224,010 1

Hash Join (cost=58,344.83..3,694,327.47 rows=17,774,514 width=194) (actual time=51,629.001..262,682.890 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,298 read=1,659,886, temp read=562,429 written=562,429
  • I/O Timings: read=163,140.826
11. 37,023.658 224,785.368 ↓ 1.0 18,224,010 1

Hash Join (cost=7,642.23..2,820,611.56 rows=17,774,514 width=146) (actual time=39,476.424..224,785.368 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,539 read=1,659,886, temp read=216,292 written=216,292
  • I/O Timings: read=163,140.826
12. 11,082.436 187,600.139 ↓ 1.0 18,224,010 1

Hash Join (cost=60.13..2,105,518.82 rows=17,774,514 width=126) (actual time=38,557.033..187,600.139 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=83 read=1,659,886
  • I/O Timings: read=163,140.826
13. 176,513.270 176,513.270 ↓ 1.0 18,224,010 1

Seq Scan on dataset.results r (cost=0.00..2,058,692.42 rows=17,774,514 width=142) (actual time=38,552.866..176,513.270 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.analytical_method_ukey, r.parameter_ukey, r.detection_quantitation_limit_ukey, r.monitoring_location_ukey
  • 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,372,150
  • Buffers: shared hit=64 read=1,659,886
  • I/O Timings: read=163,140.826
14. 3.224 4.433 ↑ 1.0 1,828 1

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

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

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

Hash (cost=4,734.71..4,734.71 rows=127,871 width=52) (actual time=161.568..161.571 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. 47.269 47.269 ↑ 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.011..47.269 rows=127,871 loops=1)

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

Hash (cost=25,687.27..25,687.27 rows=992,827 width=80) (actual time=923.302..923.304 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. 344.878 344.878 ↑ 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.013..344.878 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.012 0.042 ↑ 3.1 21 1

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

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

Seq Scan on value.activity_types at (cost=0.00..1.99 rows=65 width=55) (actual time=0.008..0.030 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. 1.118 1.502 ↑ 1.0 1,244 1

Hash (cost=29.44..29.44 rows=1,244 width=37) (actual time=1.500..1.502 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.384 0.384 ↑ 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.007..0.384 rows=1,244 loops=1)

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

Hash (cost=35.14..35.14 rows=1,514 width=80) (actual time=5.152..5.153 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.513 0.513 ↑ 1.0 1,514 1

Seq Scan on dataset.parameters p (cost=0.00..35.14 rows=1,514 width=80) (actual time=0.016..0.513 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.573 0.978 ↑ 1.0 64 1

Hash (cost=1.64..1.64 rows=64 width=32) (actual time=0.976..0.978 rows=64 loops=1)

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

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

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

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

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

  • Output: cn.value, cn.id
  • Buffers: shared hit=156
30. 0.498 1.048 ↑ 1.0 80 1

Hash (cost=1.80..1.80 rows=80 width=23) (actual time=1.047..1.048 rows=80 loops=1)

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

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

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

Hash (cost=1.38..1.38 rows=38 width=27) (actual time=0.627..1.051 rows=38 loops=1)

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

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

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

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=2.469..2.470 rows=801 loops=1)

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

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

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

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=1.863..1.864 rows=801 loops=1)

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

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

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