explain.depesz.com

PostgreSQL's explain analyze made readable

Result: szsH

Settings
# exclusive inclusive rows x rows loops node
1. 4.606 30,649.452 ↑ 6.9 133 1

Nested Loop Left Join (cost=6,638,941.79..6,639,830.57 rows=919 width=138) (actual time=30,587.875..30,649.452 rows=133 loops=1)

  • Join Filter: ((c.cruise)::text = ANY (surv_lu.match))
  • Rows Removed by Join Filter: 9044
2. 8.378 30,641.920 ↑ 2.1 133 1

Nested Loop Left Join (cost=6,638,941.79..6,639,349.82 rows=279 width=144) (actual time=30,587.736..30,641.920 rows=133 loops=1)

  • Join Filter: ((c.station)::text = ANY (site_lu.match))
  • Rows Removed by Join Filter: 18753
3. 0.292 30,627.424 ↓ 1.3 133 1

Hash Right Join (cost=6,638,941.79..6,638,993.56 rows=100 width=149) (actual time=30,587.488..30,627.424 rows=133 loops=1)

  • Hash Cond: (a_1.ctd_cast_pk = c.pk)
4. 38.236 30,620.640 ↑ 6.9 133 1

GroupAggregate (cost=6,638,602.79..6,638,641.84 rows=919 width=40) (actual time=30,580.979..30,620.640 rows=133 loops=1)

  • Group Key: a_1.ctd_cast_pk
5. 1.276 30,582.404 ↓ 1.2 1,136 1

Subquery Scan on a_1 (cost=6,638,602.79..6,638,614.27 rows=919 width=535) (actual time=30,580.450..30,582.404 rows=1,136 loops=1)

6. 4.935 30,581.128 ↓ 1.2 1,136 1

Sort (cost=6,638,602.79..6,638,605.08 rows=919 width=499) (actual time=30,580.432..30,581.128 rows=1,136 loops=1)

  • Sort Key: c_1.pk, (timezone('America/Vancouver'::text, ctd_drop.drop_start_time))
  • Sort Method: quicksort Memory: 628kB
7. 38.726 30,576.193 ↓ 1.2 1,136 1

Nested Loop Left Join (cost=6,636,004.91..6,638,557.55 rows=919 width=499) (actual time=30,032.341..30,576.193 rows=1,136 loops=1)

  • Join Filter: ((c_1.cruise)::text = ANY (surv_lu_1.match))
  • Rows Removed by Join Filter: 77248
8. 74.960 30,495.435 ↓ 4.1 1,136 1

Nested Loop Left Join (cost=6,636,004.91..6,638,072.21 rows=279 width=505) (actual time=30,032.186..30,495.435 rows=1,136 loops=1)

  • Join Filter: ((c_1.station)::text = ANY (site_lu_1.match))
  • Rows Removed by Join Filter: 160176
9. 14.034 30,346.635 ↓ 11.4 1,136 1

Merge Left Join (cost=6,636,004.91..6,637,715.95 rows=100 width=510) (actual time=30,031.907..30,346.635 rows=1,136 loops=1)

  • Merge Cond: (((timezone('America/Vancouver'::text, c_1.start_dt))::date) = event.event_date)
  • Join Filter: (replace(lower(workarea.name), ' '::text, ''::text) ~~ (('%'::text || (f_1.workarea)::text) || '%'::text))
  • Rows Removed by Join Filter: 946
10. 0.182 5.809 ↓ 1.3 133 1

Sort (cost=341.07..341.32 rows=100 width=30) (actual time=5.718..5.809 rows=133 loops=1)

  • Sort Key: ((timezone('America/Vancouver'::text, c_1.start_dt))::date)
  • Sort Method: quicksort Memory: 35kB
11. 0.327 5.627 ↓ 1.3 133 1

Hash Join (cost=294.32..337.75 rows=100 width=30) (actual time=5.270..5.627 rows=133 loops=1)

  • Hash Cond: (c_1.ctd_file_pk = f_1.pk)
12. 0.100 0.100 ↓ 1.3 133 1

Index Scan using ctd_cast_processing_stage_index on ctd_cast c_1 (cost=0.29..42.34 rows=100 width=27) (actual time=0.010..0.100 rows=133 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
13. 2.458 5.200 ↓ 1.0 4,632 1

Hash (cost=236.24..236.24 rows=4,624 width=11) (actual time=5.200..5.200 rows=4,632 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 261kB
14. 2.742 2.742 ↓ 1.0 4,632 1

Seq Scan on ctd_file f_1 (cost=0.00..236.24 rows=4,624 width=11) (actual time=0.003..2.742 rows=4,632 loops=1)

15. 21.296 30,326.792 ↓ 4.8 12,362 1

Materialize (cost=6,635,663.83..6,637,351.11 rows=2,576 width=487) (actual time=29,876.106..30,326.792 rows=12,362 loops=1)

16. 420.359 30,305.496 ↓ 4.1 10,515 1

Result (cost=6,635,663.83..6,637,318.91 rows=2,576 width=495) (actual time=29,876.101..30,305.496 rows=10,515 loops=1)

17. 70.355 29,885.137 ↓ 4.1 10,515 1

Sort (cost=6,635,663.83..6,635,670.27 rows=2,576 width=577) (actual time=29,875.598..29,885.137 rows=10,515 loops=1)

  • Sort Key: event.event_date, ctd_drop.drop_start_time, site.name
  • Sort Method: external merge Disk: 4224kB
18. 211.078 29,814.782 ↓ 4.1 10,560 1

Hash Left Join (cost=6,626,320.49..6,635,517.89 rows=2,576 width=577) (actual time=29,485.259..29,814.782 rows=10,560 loops=1)

  • Hash Cond: (event.organization_pk = organization.pk)
19. 18.964 29,603.679 ↓ 4.1 10,560 1

Merge Join (cost=6,626,319.36..6,635,172.22 rows=2,576 width=470) (actual time=29,485.165..29,603.679 rows=10,560 loops=1)

  • Merge Cond: (ctd_drop.pk = event_aux.sample_pk)
20. 12.244 29,507.304 ↑ 2.2 10,560 1

Merge Left Join (cost=6,626,318.94..6,626,433.61 rows=22,859 width=400) (actual time=29,485.121..29,507.304 rows=10,560 loops=1)

  • Merge Cond: (ctd_drop.pk = ctd_drop_1.pk)
21. 36.192 337.406 ↑ 2.2 10,560 1

Sort (cost=27,909.16..27,966.30 rows=22,859 width=359) (actual time=327.462..337.406 rows=10,560 loops=1)

  • Sort Key: ctd_drop.pk
  • Sort Method: external sort Disk: 3192kB
22. 19.819 301.214 ↑ 2.2 10,560 1

Hash Join (cost=21,460.23..22,502.11 rows=22,859 width=359) (actual time=234.161..301.214 rows=10,560 loops=1)

  • Hash Cond: (survey_event_actual.event_pk = ctd_drop.event_pk)
23. 36.215 129.836 ↓ 1.0 23,049 1

GroupAggregate (cost=2,533.79..3,004.23 rows=22,857 width=36) (actual time=82.570..129.836 rows=23,049 loops=1)

  • Group Key: survey_event_actual.event_pk
24. 41.867 93.621 ↓ 1.0 24,829 1

Sort (cost=2,533.79..2,595.36 rows=24,630 width=12) (actual time=82.535..93.621 rows=24,829 loops=1)

  • Sort Key: survey_event_actual.event_pk
  • Sort Method: quicksort Memory: 1946kB
25. 27.753 51.754 ↓ 1.0 24,829 1

Hash Join (cost=6.30..737.26 rows=24,630 width=12) (actual time=0.227..51.754 rows=24,829 loops=1)

  • Hash Cond: (survey_event_actual.survey_pk = survey.pk)
26. 23.794 23.794 ↓ 1.0 24,829 1

Seq Scan on survey_event_actual (cost=0.00..392.30 rows=24,630 width=8) (actual time=0.005..23.794 rows=24,829 loops=1)

27. 0.062 0.207 ↓ 1.0 194 1

Hash (cost=3.91..3.91 rows=191 width=12) (actual time=0.207..0.207 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
28. 0.145 0.145 ↓ 1.0 194 1

Seq Scan on survey (cost=0.00..3.91 rows=191 width=12) (actual time=0.004..0.145 rows=194 loops=1)

29. 13.964 151.559 ↓ 1.0 10,586 1

Hash (cost=18,795.35..18,795.35 rows=10,487 width=331) (actual time=151.559..151.559 rows=10,586 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3288kB
30. 28.162 137.595 ↓ 1.0 10,586 1

Hash Left Join (cost=4,856.45..18,795.35 rows=10,487 width=331) (actual time=40.799..137.595 rows=10,586 loops=1)

  • Hash Cond: ((ctd_drop.ctd_unit_pk = ctd_unit_maintenance.ctd_unit_pk) AND ((timezone('America/Vancouver'::text, ctd_drop.drop_start_time))::date = ctd_unit_maintenance.inspection_date))
31. 10.390 106.859 ↓ 1.0 10,586 1

Hash Join (cost=4,758.98..18,354.25 rows=10,487 width=284) (actual time=38.202..106.859 rows=10,586 loops=1)

  • Hash Cond: (ctd_drop.ctd_unit_pk = ctd_unit.pk)
32. 11.116 96.430 ↓ 1.0 10,586 1

Hash Join (cost=4,757.37..18,208.44 rows=10,487 width=277) (actual time=38.153..96.430 rows=10,586 loops=1)

  • Hash Cond: (event.site_pk = site.pk)
33. 22.961 75.953 ↓ 1.0 10,586 1

Hash Join (cost=759.96..14,066.83 rows=10,487 width=231) (actual time=28.759..75.953 rows=10,586 loops=1)

  • Hash Cond: (event.pk = ctd_drop.event_pk)
34. 24.278 24.278 ↓ 1.0 23,659 1

Seq Scan on event (cost=0.00..13,084.67 rows=23,467 width=71) (actual time=0.003..24.278 rows=23,659 loops=1)

35. 15.926 28.714 ↓ 1.0 10,586 1

Hash (cost=628.87..628.87 rows=10,487 width=160) (actual time=28.714..28.714 rows=10,586 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1910kB
36. 12.788 12.788 ↓ 1.0 10,586 1

Seq Scan on ctd_drop (cost=0.00..628.87 rows=10,487 width=160) (actual time=0.008..12.788 rows=10,586 loops=1)

37. 1.117 9.361 ↑ 1.2 1,846 1

Hash (cost=3,970.08..3,970.08 rows=2,187 width=54) (actual time=9.361..9.361 rows=1,846 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
38. 2.043 8.244 ↑ 1.2 1,846 1

Hash Left Join (cost=1.14..3,970.08 rows=2,187 width=54) (actual time=0.039..8.244 rows=1,846 loops=1)

  • Hash Cond: (site.workarea_pk = workarea.pk)
39. 6.182 6.182 ↑ 1.2 1,846 1

Seq Scan on site (cost=0.00..3,938.87 rows=2,187 width=49) (actual time=0.006..6.182 rows=1,846 loops=1)

40. 0.009 0.019 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=13) (actual time=0.019..0.019 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on workarea (cost=0.00..1.06 rows=6 width=13) (actual time=0.004..0.010 rows=6 loops=1)

42. 0.023 0.039 ↓ 1.0 28 1

Hash (cost=1.27..1.27 rows=27 width=11) (actual time=0.039..0.039 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
43. 0.016 0.016 ↓ 1.0 28 1

Seq Scan on ctd_unit (cost=0.00..1.27 rows=27 width=11) (actual time=0.008..0.016 rows=28 loops=1)

44. 1.360 2.574 ↓ 1.0 2,000 1

Hash (cost=67.79..67.79 rows=1,979 width=59) (actual time=2.573..2.574 rows=2,000 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 171kB
45. 1.214 1.214 ↓ 1.0 2,000 1

Seq Scan on ctd_unit_maintenance (cost=0.00..67.79 rows=1,979 width=59) (actual time=0.035..1.214 rows=2,000 loops=1)

46. 0.005 29,157.654 ↓ 0.0 0 1

Materialize (cost=6,598,409.79..6,598,409.92 rows=8 width=45) (actual time=29,157.653..29,157.654 rows=0 loops=1)

47. 0.000 29,157.649 ↓ 0.0 0 1

Unique (cost=6,598,409.79..6,598,409.83 rows=8 width=53) (actual time=29,157.649..29,157.649 rows=0 loops=1)

48. 0.015 29,157.649 ↓ 0.0 0 1

Sort (cost=6,598,409.79..6,598,409.81 rows=8 width=53) (actual time=29,157.648..29,157.649 rows=0 loops=1)

  • Sort Key: ctd_drop_1.pk, (st_distance(st_transform(COALESCE(ctd_drop_1.drop_start_location, ctd_drop_1.drop_bottom_location, ctd_drop_1.drop_end_location), 3005), st_transform(site2.geom, 3005)))
  • Sort Method: quicksort Memory: 25kB
49. 21,395.883 29,157.634 ↓ 0.0 0 1

Nested Loop (cost=0.00..6,598,409.67 rows=8 width=53) (actual time=29,157.633..29,157.634 rows=0 loops=1)

  • Join Filter: ((st_transform(COALESCE(ctd_drop_1.drop_start_location, ctd_drop_1.drop_bottom_location, ctd_drop_1.drop_end_location), 3005) && st_expand(st_transform(site2.geom, 3005), '300'::double precision)) AND (st_transform(site2.geom, 3005) && st_expand(st_transform(COALESCE(ctd_drop_1.drop_start_location, ctd_drop_1.drop_bottom_location, ctd_drop_1.drop_end_location), 3005), '300'::double precision)) AND _st_dwithin(st_transform(COALESCE(ctd_drop_1.drop_start_location, ctd_drop_1.drop_bottom_location, ctd_drop_1.drop_end_location), 3005), st_transform(site2.geom, 3005), '300'::double precision))
  • Rows Removed by Join Filter: 19541756
50. 12.799 12.799 ↓ 1.0 10,586 1

Seq Scan on ctd_drop ctd_drop_1 (cost=0.00..628.87 rows=10,487 width=100) (actual time=0.009..12.799 rows=10,586 loops=1)

51. 7,742.583 7,748.952 ↑ 1.2 1,846 10,586

Materialize (cost=0.00..3,949.80 rows=2,187 width=41) (actual time=0.000..0.732 rows=1,846 loops=10,586)

52. 6.369 6.369 ↑ 1.2 1,846 1

Seq Scan on site site2 (cost=0.00..3,938.87 rows=2,187 width=41) (actual time=0.009..6.369 rows=1,846 loops=1)

53. 77.411 77.411 ↓ 1.0 10,586 1

Index Scan using event_aux_sample_pk_idx on event_aux (cost=0.42..51,866.10 rows=10,330 width=74) (actual time=0.038..77.411 rows=10,586 loops=1)

  • Filter: ((sample_type)::text = 'CTD'::text)
  • Rows Removed by Filter: 103779
54. 0.015 0.025 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=18) (actual time=0.025..0.025 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on organization (cost=0.00..1.06 rows=6 width=18) (actual time=0.008..0.010 rows=6 loops=1)

56. 73.750 73.840 ↑ 1.0 141 1,136

Materialize (cost=0.00..4.12 rows=141 width=47) (actual time=0.000..0.065 rows=141 loops=1,136)

57. 0.090 0.090 ↑ 1.0 141 1

Seq Scan on lookup_site site_lu_1 (cost=0.00..3.41 rows=141 width=47) (actual time=0.012..0.090 rows=141 loops=1)

58. 41.990 42.032 ↑ 1.0 68 1,136

Materialize (cost=0.00..2.02 rows=68 width=58) (actual time=0.000..0.037 rows=68 loops=1,136)

59. 0.042 0.042 ↑ 1.0 68 1

Seq Scan on lookup_survey surv_lu_1 (cost=0.00..1.68 rows=68 width=58) (actual time=0.006..0.042 rows=68 loops=1)

60. 0.122 6.492 ↓ 1.3 133 1

Hash (cost=337.75..337.75 rows=100 width=113) (actual time=6.492..6.492 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
61. 0.183 6.370 ↓ 1.3 133 1

Hash Join (cost=294.32..337.75 rows=100 width=113) (actual time=6.097..6.370 rows=133 loops=1)

  • Hash Cond: (c.ctd_file_pk = f.pk)
62. 0.159 0.159 ↓ 1.3 133 1

Index Scan using ctd_cast_processing_stage_index on ctd_cast c (cost=0.29..42.34 rows=100 width=68) (actual time=0.015..0.159 rows=133 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
63. 2.939 6.028 ↓ 1.0 4,632 1

Hash (cost=236.24..236.24 rows=4,624 width=49) (actual time=6.028..6.028 rows=4,632 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 443kB
64. 3.089 3.089 ↓ 1.0 4,632 1

Seq Scan on ctd_file f (cost=0.00..236.24 rows=4,624 width=49) (actual time=0.006..3.089 rows=4,632 loops=1)

65. 6.036 6.118 ↑ 1.0 141 133

Materialize (cost=0.00..4.12 rows=141 width=47) (actual time=0.001..0.046 rows=141 loops=133)

66. 0.082 0.082 ↑ 1.0 141 1

Seq Scan on lookup_site site_lu (cost=0.00..3.41 rows=141 width=47) (actual time=0.012..0.082 rows=141 loops=1)

67. 2.891 2.926 ↑ 1.0 68 133

Materialize (cost=0.00..2.02 rows=68 width=58) (actual time=0.000..0.022 rows=68 loops=133)

68. 0.035 0.035 ↑ 1.0 68 1

Seq Scan on lookup_survey surv_lu (cost=0.00..1.68 rows=68 width=58) (actual time=0.006..0.035 rows=68 loops=1)

Planning time : 9.016 ms
Execution time : 30,659.703 ms