explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cWTA : measured with results - db.t2.large

Settings
# exclusive inclusive rows x rows loops node
1. 82,270.316 333,324.417 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,969.05..4,548,684.24 rows=17,425,506 width=242) (actual time=24,761.874..333,324.417 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,492 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
2. 11,943.834 251,053.624 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,945.03..4,067,041.70 rows=17,425,506 width=353) (actual time=24,761.347..251,053.624 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,483 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
3. 11,303.634 239,109.301 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,921.01..4,021,116.67 rows=17,425,506 width=347) (actual time=24,760.853..239,109.301 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,477 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
4. 10,975.248 227,805.639 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,919.15..3,971,366.43 rows=17,425,506 width=336) (actual time=24,760.818..227,805.639 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,476 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
5. 12,136.948 216,830.336 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,916.35..3,925,013.46 rows=17,425,506 width=329) (actual time=24,760.756..216,830.336 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,475 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
6. 11,090.759 204,684.639 ↓ 1.0 18,224,010 1

Hash Left Join (cost=58,449.13..3,878,790.27 rows=17,425,506 width=286) (actual time=24,751.985..204,684.639 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,319 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
7. 12,755.029 193,593.830 ↓ 1.0 18,224,010 1

Hash Join (cost=58,446.69..3,830,016.83 rows=17,425,506 width=270) (actual time=24,751.924..193,593.830 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,318 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
8. 12,214.538 180,837.780 ↓ 1.0 18,224,010 1

Hash Join (cost=58,392.63..3,784,092.76 rows=17,425,506 width=222) (actual time=24,750.879..180,837.780 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,295 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
9. 10,903.915 168,622.450 ↓ 1.0 18,224,010 1

Hash Join (cost=58,347.64..3,738,114.94 rows=17,438,854 width=217) (actual time=24,750.079..168,622.450 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,278 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
10. 31,951.304 157,718.498 ↓ 1.0 18,224,010 1

Hash Join (cost=58,344.83..3,688,646.16 rows=17,707,144 width=194) (actual time=24,750.036..157,718.498 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,277 read=1,659,907, temp read=560,619 written=560,619
  • I/O Timings: read=73,927.361
11. 26,801.804 124,917.155 ↓ 1.0 18,224,010 1

Hash Join (cost=7,642.23..2,818,003.10 rows=17,707,144 width=146) (actual time=18,544.406..124,917.155 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,518 read=1,659,907, temp read=214,927 written=214,927
  • I/O Timings: read=73,927.361
12. 10,893.867 98,006.072 ↓ 1.0 18,224,010 1

Hash Join (cost=60.13..2,105,587.21 rows=17,707,144 width=126) (actual time=18,045.463..98,006.072 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=62 read=1,659,907
  • I/O Timings: read=73,927.361
13. 87,111.163 87,111.163 ↓ 1.0 18,224,010 1

Seq Scan on dataset.results r (cost=0.00..2,058,938.06 rows=17,707,144 width=142) (actual time=18,044.405..87,111.163 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=43 read=1,659,907
  • I/O Timings: read=73,927.361
14. 0.543 1.042 ↑ 1.0 1,828 1

Hash (cost=37.28..37.28 rows=1,828 width=16) (actual time=1.041..1.042 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.008..0.499 rows=1,828 loops=1)

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

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

  • Output: ml.key, ml.coordinate_normalized, ml.ukey
  • Buckets: 65,536 Batches: 4 Memory Usage: 3,066kB
  • Buffers: shared hit=3,456, temp written=805
17. 50.984 50.984 ↑ 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..50.984 rows=127,871 loops=1)

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

Hash (cost=25,687.27..25,687.27 rows=992,827 width=80) (actual time=850.038..850.039 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,897kB
  • Buffers: shared hit=15,759, temp written=11,747
19. 348.048 348.048 ↑ 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..348.048 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.010 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.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.408 0.792 ↑ 1.0 1,244 1

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

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

Hash (cost=35.14..35.14 rows=1,514 width=80) (actual time=1.020..1.021 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.485 0.485 ↑ 1.0 1,514 1

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

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

  • Output: amn.value, amn.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
27. 0.023 0.023 ↑ 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.023 rows=64 loops=1)

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

Hash (cost=294.32..294.32 rows=13,832 width=59) (actual time=8.749..8.749 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.887 3.887 ↑ 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.887 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.015 0.028 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=27) (actual time=0.027..0.028 rows=38 loops=1)

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

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

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

Hash (cost=14.01..14.01 rows=801 width=22) (actual time=0.488..0.489 rows=801 loops=1)

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

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

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

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

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

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

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