explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xv8i : measured - db.t2.medium

Settings
# exclusive inclusive rows x rows loops node
1. 86,648.158 341,867.383 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,969.05..3,726,906.88 rows=17,596,551 width=242) (actual time=23,964.493..341,867.383 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,513 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
2. 12,310.778 255,218.749 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,945.03..3,240,536.88 rows=17,596,551 width=353) (actual time=23,963.946..255,218.749 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,504 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
3. 11,459.373 242,907.478 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,921.01..3,194,161.29 rows=17,596,551 width=347) (actual time=23,963.444..242,907.478 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,498 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
4. 11,403.442 231,448.076 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,919.15..3,143,922.73 rows=17,596,551 width=336) (actual time=23,963.403..231,448.076 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,497 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
5. 12,399.521 220,044.577 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,916.35..3,097,114.80 rows=17,596,551 width=329) (actual time=23,963.334..220,044.577 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,496 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
6. 11,210.991 207,636.215 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,449.13..3,050,442.48 rows=17,596,551 width=286) (actual time=23,954.459..207,636.215 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,340 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
7. 13,182.164 196,425.170 ↓ 1.0 18,224,010 1

Hash Join (cost=58,446.69..3,001,190.32 rows=17,596,551 width=270) (actual time=23,954.387..196,425.170 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,339 read=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
8. 12,661.736 183,241.967 ↓ 1.0 18,224,010 1

Hash Join (cost=58,392.63..2,954,816.01 rows=17,596,551 width=222) (actual time=23,953.316..183,241.967 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=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
9. 10,961.420 170,579.441 ↓ 1.0 18,224,010 1

Hash Join (cost=58,347.64..2,908,389.76 rows=17,609,103 width=217) (actual time=23,952.515..170,579.441 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=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
10. 35,030.305 159,617.572 ↓ 1.0 18,224,010 1

Hash Join (cost=58,344.83..2,858,438.06 rows=17,880,012 width=194) (actual time=23,952.467..159,617.572 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=815,430, temp read=561,837 written=561,837
  • I/O Timings: read=65,900.383
11. 36,258.511 123,741.527 ↓ 1.0 18,224,010 1

Hash Join (cost=7,642.23..1,979,913.22 rows=17,880,012 width=146) (actual time=18,076.790..123,741.527 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=815,430, temp read=215,369 written=215,369
  • I/O Timings: read=65,900.383
12. 12,093.396 87,382.761 ↓ 1.0 18,224,010 1

Hash Join (cost=60.13..1,260,627.54 rows=17,880,012 width=126) (actual time=17,647.493..87,382.761 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=815,430
  • I/O Timings: read=65,900.383
13. 75,287.778 75,287.778 ↓ 1.0 18,224,010 1

Seq Scan on dataset.results_test r (cost=0.00..1,213,523.56 rows=17,880,012 width=142) (actual time=17,646.423..75,287.778 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 hit=64 read=815,430
  • I/O Timings: read=65,900.383
14. 0.553 1.587 ↑ 1.0 1,828 1

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

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

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

Hash (cost=4,734.71..4,734.71 rows=127,871 width=52) (actual time=100.254..100.255 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.254 47.254 ↑ 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.006..47.254 rows=127,871 loops=1)

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

Hash (cost=25,687.27..25,687.27 rows=992,827 width=80) (actual time=845.362..845.740 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. 350.728 350.728 ↑ 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..350.728 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.422 0.449 ↑ 3.1 21 1

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

  • Output: at.value, at.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
21. 0.027 0.027 ↑ 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.027 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.406 0.790 ↑ 1.0 1,244 1

Hash (cost=29.44..29.44 rows=1,244 width=37) (actual time=0.789..0.790 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.005..0.384 rows=1,244 loops=1)

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

Hash (cost=35.14..35.14 rows=1,514 width=80) (actual time=1.038..1.039 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.479 0.479 ↑ 1.0 1,514 1

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

Hash (cost=1.64..1.64 rows=64 width=32) (actual time=0.053..0.054 rows=64 loops=1)

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

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

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

Hash (cost=294.32..294.32 rows=13,832 width=59) (actual time=8.840..8.841 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.843 3.843 ↑ 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.006..3.843 rows=13,832 loops=1)

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

Hash (cost=1.80..1.80 rows=80 width=23) (actual time=0.056..0.057 rows=80 loops=1)

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

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

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

Hash (cost=1.38..1.38 rows=38 width=27) (actual time=0.028..0.029 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.276 0.493 ↑ 1.0 801 1

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.492..0.493 rows=801 loops=1)

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

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

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

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.475..0.476 rows=801 loops=1)

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

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

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