explain.depesz.com

PostgreSQL's explain analyze made readable

Result: liwU : using current ctd drops view

Settings
# exclusive inclusive rows x rows loops node
1. 3.708 30,684.262 ↑ 8.0 101 1

Nested Loop Left Join (cost=5,630,045.23..5,630,888.84 rows=812 width=143) (actual time=30,621.150..30,684.262 rows=101 loops=1)

  • Join Filter: ((c.cruise)::text = ANY (surv_lu.match))
  • Rows Removed by Join Filter: 6868
2. 6.934 30,678.130 ↑ 2.5 101 1

Nested Loop Left Join (cost=5,630,045.23..5,630,461.33 rows=248 width=149) (actual time=30,621.008..30,678.130 rows=101 loops=1)

  • Join Filter: ((c.station)::text = ANY (site_lu.match))
  • Rows Removed by Join Filter: 14241
3. 0.212 30,666.146 ↑ 1.0 101 1

Hash Right Join (cost=5,630,045.23..5,630,090.97 rows=104 width=154) (actual time=30,620.734..30,666.146 rows=101 loops=1)

  • Hash Cond: (a.ctd_cast_pk = c.pk)
4. 44.733 30,658.582 ↑ 8.0 101 1

GroupAggregate (cost=5,629,639.57..5,629,674.08 rows=812 width=40) (actual time=30,613.363..30,658.582 rows=101 loops=1)

  • Group Key: a.ctd_cast_pk
5. 1.277 30,613.849 ↓ 1.5 1,211 1

Subquery Scan on a (cost=5,629,639.57..5,629,649.72 rows=812 width=528) (actual time=30,611.948..30,613.849 rows=1,211 loops=1)

6. 5.427 30,612.572 ↓ 1.5 1,211 1

Sort (cost=5,629,639.57..5,629,641.60 rows=812 width=492) (actual time=30,611.923..30,612.572 rows=1,211 loops=1)

  • Sort Key: c_1.pk, (timezone('America/Vancouver'::text, ctd_drop.drop_start_time))
  • Sort Method: quicksort Memory: 669kB
7. 44.198 30,607.145 ↓ 1.5 1,211 1

Nested Loop Left Join (cost=5,627,061.98..5,629,600.32 rows=812 width=492) (actual time=30,085.454..30,607.145 rows=1,211 loops=1)

  • Join Filter: ((c_1.cruise)::text = ANY (surv_lu_1.match))
  • Rows Removed by Join Filter: 82348
8. 85.572 30,533.883 ↓ 4.9 1,211 1

Nested Loop Left Join (cost=5,627,061.98..5,629,168.75 rows=248 width=498) (actual time=30,085.299..30,533.883 rows=1,211 loops=1)

  • Join Filter: ((c_1.station)::text = ANY (site_lu_1.match))
  • Rows Removed by Join Filter: 170751
9. 11.994 30,384.128 ↓ 11.6 1,211 1

Merge Left Join (cost=5,627,061.98..5,628,798.39 rows=104 width=503) (actual time=30,085.011..30,384.128 rows=1,211 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: 517
10. 0.166 6.374 ↑ 1.0 101 1

Sort (cost=407.84..408.10 rows=104 width=30) (actual time=6.301..6.374 rows=101 loops=1)

  • Sort Key: ((timezone('America/Vancouver'::text, c_1.start_dt))::date)
  • Sort Method: quicksort Memory: 32kB
11. 0.978 6.208 ↑ 1.0 101 1

Hash Join (cost=320.09..404.36 rows=104 width=30) (actual time=5.948..6.208 rows=101 loops=1)

  • Hash Cond: (c_1.ctd_file_pk = f_1.pk)
12. 0.098 0.098 ↑ 1.0 101 1

Index Scan using ctd_cast_processing_stage_index on ctd_cast c_1 (cost=0.29..83.13 rows=104 width=27) (actual time=0.007..0.098 rows=101 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
13. 2.469 5.132 ↑ 1.0 4,867 1

Hash (cost=258.80..258.80 rows=4,880 width=11) (actual time=5.132..5.132 rows=4,867 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 271kB
14. 2.663 2.663 ↑ 1.0 4,867 1

Seq Scan on ctd_file f_1 (cost=0.00..258.80 rows=4,880 width=11) (actual time=0.004..2.663 rows=4,867 loops=1)

15. 20.640 30,365.760 ↓ 4.6 12,032 1

Materialize (cost=5,626,654.13..5,628,366.30 rows=2,614 width=480) (actual time=29,937.125..30,365.760 rows=12,032 loops=1)

16. 399.037 30,345.120 ↓ 4.0 10,516 1

Result (cost=5,626,654.13..5,628,333.63 rows=2,614 width=488) (actual time=29,937.113..30,345.120 rows=10,516 loops=1)

17. 68.335 29,946.083 ↓ 4.0 10,516 1

Sort (cost=5,626,654.13..5,626,660.67 rows=2,614 width=570) (actual time=29,936.538..29,946.083 rows=10,516 loops=1)

  • Sort Key: event.event_date, ctd_drop.drop_start_time, site.name
  • Sort Method: external merge Disk: 4232kB
18. 202.531 29,877.748 ↓ 4.0 10,580 1

Hash Left Join (cost=5,617,256.00..5,626,505.76 rows=2,614 width=570) (actual time=29,542.164..29,877.748 rows=10,580 loops=1)

  • Hash Cond: (event.organization_pk = organization.pk)
19. 18.119 29,675.193 ↓ 4.0 10,580 1

Merge Join (cost=5,617,254.93..5,626,155.07 rows=2,614 width=470) (actual time=29,542.037..29,675.193 rows=10,580 loops=1)

  • Merge Cond: (ctd_drop.pk = event_aux.sample_pk)
20. 13.045 29,563.870 ↑ 2.2 10,580 1

Merge Left Join (cost=5,617,254.51..5,617,369.72 rows=22,984 width=400) (actual time=29,541.996..29,563.870 rows=10,580 loops=1)

  • Merge Cond: (ctd_drop.pk = ctd_drop_1.pk)
21. 35.756 345.586 ↑ 2.2 10,580 1

Sort (cost=28,014.78..28,072.24 rows=22,984 width=359) (actual time=336.752..345.586 rows=10,580 loops=1)

  • Sort Key: ctd_drop.pk
  • Sort Method: external sort Disk: 3200kB
22. 20.805 309.830 ↑ 2.2 10,580 1

Hash Join (cost=21,529.32..22,576.78 rows=22,984 width=359) (actual time=241.634..309.830 rows=10,580 loops=1)

  • Hash Cond: (survey_event_actual.event_pk = ctd_drop.event_pk)
23. 36.278 97.632 ↓ 1.0 23,059 1

GroupAggregate (cost=2,546.76..3,019.67 rows=22,980 width=36) (actual time=50.214..97.632 rows=23,059 loops=1)

  • Group Key: survey_event_actual.event_pk
24. 27.391 61.354 ↓ 1.0 24,834 1

Sort (cost=2,546.76..2,608.64 rows=24,755 width=12) (actual time=50.195..61.354 rows=24,834 loops=1)

  • Sort Key: survey_event_actual.event_pk
  • Sort Method: quicksort Memory: 1946kB
25. 23.968 33.963 ↓ 1.0 24,834 1

Hash Join (cost=6.28..740.21 rows=24,755 width=12) (actual time=0.234..33.963 rows=24,834 loops=1)

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

Seq Scan on survey_event_actual (cost=0.00..393.55 rows=24,755 width=8) (actual time=0.007..9.789 rows=24,834 loops=1)

27. 0.122 0.206 ↑ 1.0 190 1

Hash (cost=3.90..3.90 rows=190 width=12) (actual time=0.206..0.206 rows=190 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
28. 0.084 0.084 ↑ 1.0 190 1

Seq Scan on survey (cost=0.00..3.90 rows=190 width=12) (actual time=0.004..0.084 rows=190 loops=1)

29. 14.297 191.393 ↓ 1.0 10,606 1

Hash (cost=18,850.60..18,850.60 rows=10,557 width=331) (actual time=191.393..191.393 rows=10,606 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3295kB
30. 28.598 177.096 ↓ 1.0 10,606 1

Hash Left Join (cost=4,859.82..18,850.60 rows=10,557 width=331) (actual time=37.603..177.096 rows=10,606 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.645 145.871 ↓ 1.0 10,606 1

Hash Join (cost=4,762.00..18,406.92 rows=10,557 width=284) (actual time=34.938..145.871 rows=10,606 loops=1)

  • Hash Cond: (ctd_drop.ctd_unit_pk = ctd_unit.pk)
32. 30.065 135.185 ↓ 1.0 10,606 1

Hash Join (cost=4,760.37..18,260.13 rows=10,557 width=277) (actual time=34.885..135.185 rows=10,606 loops=1)

  • Hash Cond: (event.site_pk = site.pk)
33. 26.237 88.703 ↓ 1.0 10,606 1

Hash Join (cost=765.53..14,120.14 rows=10,557 width=231) (actual time=18.456..88.703 rows=10,606 loops=1)

  • Hash Cond: (event.pk = ctd_drop.event_pk)
34. 44.047 44.047 ↑ 1.0 23,669 1

Seq Scan on event (cost=0.00..13,130.69 rows=23,669 width=71) (actual time=0.011..44.047 rows=23,669 loops=1)

35. 8.687 18.419 ↓ 1.0 10,606 1

Hash (cost=633.57..633.57 rows=10,557 width=160) (actual time=18.419..18.419 rows=10,606 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1914kB
36. 9.732 9.732 ↓ 1.0 10,606 1

Seq Scan on ctd_drop (cost=0.00..633.57 rows=10,557 width=160) (actual time=0.009..9.732 rows=10,606 loops=1)

37. 1.122 16.417 ↑ 1.0 1,840 1

Hash (cost=3,971.84..3,971.84 rows=1,840 width=54) (actual time=16.416..16.417 rows=1,840 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
38. 2.319 15.295 ↑ 1.0 1,840 1

Hash Left Join (cost=1.14..3,971.84 rows=1,840 width=54) (actual time=0.039..15.295 rows=1,840 loops=1)

  • Hash Cond: (site.workarea_pk = workarea.pk)
39. 12.961 12.961 ↑ 1.0 1,840 1

Seq Scan on site (cost=0.00..3,945.40 rows=1,840 width=49) (actual time=0.014..12.961 rows=1,840 loops=1)

40. 0.006 0.015 ↑ 1.0 6 1

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

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

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

42. 0.026 0.041 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=11) (actual time=0.041..0.041 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
43. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on ctd_unit (cost=0.00..1.28 rows=28 width=11) (actual time=0.009..0.015 rows=28 loops=1)

44. 1.323 2.627 ↓ 1.0 2,004 1

Hash (cost=67.93..67.93 rows=1,993 width=59) (actual time=2.626..2.627 rows=2,004 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 172kB
45. 1.304 1.304 ↓ 1.0 2,004 1

Seq Scan on ctd_unit_maintenance (cost=0.00..67.93 rows=1,993 width=59) (actual time=0.007..1.304 rows=2,004 loops=1)

46. 0.007 29,205.239 ↓ 0.0 0 1

Materialize (cost=5,589,239.74..5,589,239.84 rows=6 width=45) (actual time=29,205.238..29,205.239 rows=0 loops=1)

47. 0.002 29,205.232 ↓ 0.0 0 1

Unique (cost=5,589,239.74..5,589,239.77 rows=6 width=53) (actual time=29,205.232..29,205.232 rows=0 loops=1)

48. 0.045 29,205.230 ↓ 0.0 0 1

Sort (cost=5,589,239.74..5,589,239.75 rows=6 width=53) (actual time=29,205.230..29,205.230 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,506.885 29,205.185 ↓ 0.0 0 1

Nested Loop (cost=0.00..5,589,239.66 rows=6 width=53) (actual time=29,205.185..29,205.185 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: 19515040
50. 8.950 8.950 ↓ 1.0 10,606 1

Seq Scan on ctd_drop ctd_drop_1 (cost=0.00..633.57 rows=10,557 width=100) (actual time=0.009..8.950 rows=10,606 loops=1)

51. 7,683.474 7,689.350 ↑ 1.0 1,840 10,606

Materialize (cost=0.00..3,954.60 rows=1,840 width=41) (actual time=0.000..0.725 rows=1,840 loops=10,606)

52. 5.876 5.876 ↑ 1.0 1,840 1

Seq Scan on site site2 (cost=0.00..3,945.40 rows=1,840 width=41) (actual time=0.008..5.876 rows=1,840 loops=1)

53. 93.204 93.204 ↓ 1.0 10,606 1

Index Scan using event_aux_sample_pk_idx on event_aux (cost=0.42..52,167.17 rows=10,587 width=74) (actual time=0.035..93.204 rows=10,606 loops=1)

  • Filter: ((sample_type)::text = 'CTD'::text)
  • Rows Removed by Filter: 103970
54. 0.015 0.024 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=11) (actual time=0.023..0.024 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.009 0.009 ↑ 1.0 3 1

Seq Scan on organization (cost=0.00..1.03 rows=3 width=11) (actual time=0.007..0.009 rows=3 loops=1)

56. 64.093 64.183 ↑ 1.0 141 1,211

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

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.013..0.090 rows=141 loops=1)

58. 29.026 29.064 ↑ 1.0 68 1,211

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

59. 0.038 0.038 ↑ 1.0 68 1

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

60. 0.098 7.352 ↑ 1.0 101 1

Hash (cost=404.36..404.36 rows=104 width=118) (actual time=7.352..7.352 rows=101 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
61. 0.154 7.254 ↑ 1.0 101 1

Hash Join (cost=320.09..404.36 rows=104 width=118) (actual time=6.905..7.254 rows=101 loops=1)

  • Hash Cond: (c.ctd_file_pk = f.pk)
62. 0.283 0.283 ↑ 1.0 101 1

Index Scan using ctd_cast_processing_stage_index on ctd_cast c (cost=0.29..83.13 rows=104 width=73) (actual time=0.030..0.283 rows=101 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
63. 3.078 6.817 ↑ 1.0 4,867 1

Hash (cost=258.80..258.80 rows=4,880 width=49) (actual time=6.817..6.817 rows=4,867 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 462kB
64. 3.739 3.739 ↑ 1.0 4,867 1

Seq Scan on ctd_file f (cost=0.00..258.80 rows=4,880 width=49) (actual time=0.011..3.739 rows=4,867 loops=1)

65. 4.967 5.050 ↑ 1.0 141 101

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

66. 0.083 0.083 ↑ 1.0 141 1

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

67. 2.383 2.424 ↑ 1.0 68 101

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

68. 0.041 0.041 ↑ 1.0 68 1

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

Planning time : 339.471 ms
Execution time : 30,689.988 ms