explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C0f : using simplified output view

Settings
# exclusive inclusive rows x rows loops node
1. 1.937 756.277 ↑ 8.0 101 1

Nested Loop Left Join (cost=40,310.35..41,155.40 rows=812 width=143) (actual time=723.724..756.277 rows=101 loops=1)

  • Join Filter: ((c.cruise)::text = ANY (surv_lu.match))
  • Rows Removed by Join Filter: 6868
2. 3.672 752.926 ↑ 2.5 101 1

Nested Loop Left Join (cost=40,310.35..40,727.89 rows=248 width=149) (actual time=723.642..752.926 rows=101 loops=1)

  • Join Filter: ((c.station)::text = ANY (site_lu.match))
  • Rows Removed by Join Filter: 14241
3. 0.129 746.426 ↑ 1.0 101 1

Hash Join (cost=40,310.35..40,357.52 rows=104 width=154) (actual time=723.484..746.426 rows=101 loops=1)

  • Hash Cond: (c.ctd_file_pk = f.pk)
4. 0.119 742.617 ↑ 1.0 101 1

Hash Right Join (cost=39,990.55..40,036.29 rows=104 width=109) (actual time=719.763..742.617 rows=101 loops=1)

  • Hash Cond: (a.ctd_cast_pk = c.pk)
5. 22.002 742.365 ↑ 8.0 101 1

GroupAggregate (cost=39,906.12..39,940.63 rows=812 width=40) (actual time=719.617..742.365 rows=101 loops=1)

  • Group Key: a.ctd_cast_pk
6. 0.730 720.363 ↓ 1.5 1,211 1

Subquery Scan on a (cost=39,906.12..39,916.27 rows=812 width=455) (actual time=719.262..720.363 rows=1,211 loops=1)

7. 2.879 719.633 ↓ 1.5 1,211 1

Sort (cost=39,906.12..39,908.15 rows=812 width=419) (actual time=719.244..719.633 rows=1,211 loops=1)

  • Sort Key: c_1.pk, (timezone('America/Vancouver'::text, ctd_drop.drop_start_time))
  • Sort Method: quicksort Memory: 669kB
8. 23.591 716.754 ↓ 1.5 1,211 1

Nested Loop Left Join (cost=38,008.17..39,866.88 rows=812 width=419) (actual time=454.377..716.754 rows=1,211 loops=1)

  • Join Filter: ((c_1.cruise)::text = ANY (surv_lu_1.match))
  • Rows Removed by Join Filter: 82348
9. 45.415 677.420 ↓ 4.9 1,211 1

Nested Loop Left Join (cost=38,008.17..39,435.31 rows=248 width=425) (actual time=454.287..677.420 rows=1,211 loops=1)

  • Join Filter: ((c_1.station)::text = ANY (site_lu_1.match))
  • Rows Removed by Join Filter: 170751
10. 6.438 599.308 ↓ 11.6 1,211 1

Merge Left Join (cost=38,008.17..39,064.95 rows=104 width=430) (actual time=454.130..599.308 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
11. 0.086 3.411 ↑ 1.0 101 1

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

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

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

  • Hash Cond: (c_1.ctd_file_pk = f_1.pk)
13. 0.054 0.054 ↑ 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.004..0.054 rows=101 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
14. 1.504 3.119 ↑ 1.0 4,867 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 271kB
15. 1.615 1.615 ↑ 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.003..1.615 rows=4,867 loops=1)

16. 11.337 589.459 ↓ 4.6 12,032 1

Materialize (cost=37,600.33..38,632.86 rows=2,614 width=407) (actual time=378.040..589.459 rows=12,032 loops=1)

17. 195.454 578.122 ↓ 4.0 10,516 1

Result (cost=37,600.33..38,600.18 rows=2,614 width=415) (actual time=378.036..578.122 rows=10,516 loops=1)

18. 35.787 382.668 ↓ 4.0 10,516 1

Sort (cost=37,600.33..37,606.86 rows=2,614 width=401) (actual time=377.697..382.668 rows=10,516 loops=1)

  • Sort Key: event.event_date, ctd_drop.drop_start_time, site.name
  • Sort Method: external merge Disk: 3520kB
19. 122.992 346.881 ↓ 4.0 10,580 1

Merge Join (cost=28,238.42..37,451.95 rows=2,614 width=401) (actual time=170.220..346.881 rows=10,580 loops=1)

  • Merge Cond: (event_aux.sample_pk = ctd_drop.pk)
20. 43.940 43.940 ↓ 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.014..43.940 rows=10,606 loops=1)

  • Filter: ((sample_type)::text = 'CTD'::text)
  • Rows Removed by Filter: 104034
21. 4.798 179.949 ↑ 2.2 10,580 1

Materialize (cost=28,238.00..28,352.92 rows=22,984 width=362) (actual time=170.161..179.949 rows=10,580 loops=1)

22. 21.227 175.151 ↑ 2.2 10,580 1

Sort (cost=28,238.00..28,295.46 rows=22,984 width=362) (actual time=170.159..175.151 rows=10,580 loops=1)

  • Sort Key: ctd_drop.pk
  • Sort Method: external sort Disk: 3192kB
23. 11.640 153.924 ↑ 2.2 10,580 1

Hash Join (cost=21,675.55..22,723.00 rows=22,984 width=362) (actual time=115.992..153.924 rows=10,580 loops=1)

  • Hash Cond: (survey_event_actual.event_pk = ctd_drop.event_pk)
24. 19.671 54.940 ↓ 1.0 23,061 1

GroupAggregate (cost=2,546.76..3,019.67 rows=22,980 width=36) (actual time=28.574..54.940 rows=23,061 loops=1)

  • Group Key: survey_event_actual.event_pk
25. 16.479 35.269 ↓ 1.0 24,836 1

Sort (cost=2,546.76..2,608.64 rows=24,755 width=12) (actual time=28.556..35.269 rows=24,836 loops=1)

  • Sort Key: survey_event_actual.event_pk
  • Sort Method: quicksort Memory: 1946kB
26. 13.211 18.790 ↓ 1.0 24,836 1

Hash Join (cost=6.28..740.21 rows=24,755 width=12) (actual time=0.132..18.790 rows=24,836 loops=1)

  • Hash Cond: (survey_event_actual.survey_pk = survey.pk)
27. 5.459 5.459 ↓ 1.0 24,836 1

Seq Scan on survey_event_actual (cost=0.00..393.55 rows=24,755 width=8) (actual time=0.004..5.459 rows=24,836 loops=1)

28. 0.063 0.120 ↑ 1.0 190 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
29. 0.057 0.057 ↑ 1.0 190 1

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

30. 8.623 87.344 ↓ 1.0 10,606 1

Hash (cost=18,996.83..18,996.83 rows=10,557 width=334) (actual time=87.343..87.344 rows=10,606 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3284kB
31. 6.380 78.721 ↓ 1.0 10,606 1

Hash Left Join (cost=4,860.89..18,996.83 rows=10,557 width=334) (actual time=16.602..78.721 rows=10,606 loops=1)

  • Hash Cond: (event.organization_pk = organization.pk)
32. 15.272 72.332 ↓ 1.0 10,606 1

Hash Left Join (cost=4,859.82..18,850.60 rows=10,557 width=331) (actual time=16.586..72.332 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))
33. 6.152 55.623 ↓ 1.0 10,606 1

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

  • Hash Cond: (ctd_drop.ctd_unit_pk = ctd_unit.pk)
34. 6.428 49.446 ↓ 1.0 10,606 1

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

  • Hash Cond: (event.site_pk = site.pk)
35. 13.517 37.570 ↓ 1.0 10,606 1

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

  • Hash Cond: (event.pk = ctd_drop.event_pk)
36. 14.468 14.468 ↓ 1.0 23,671 1

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

37. 5.096 9.585 ↓ 1.0 10,606 1

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

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

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

39. 0.647 5.448 ↑ 1.0 1,840 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
40. 1.166 4.801 ↑ 1.0 1,840 1

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

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

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

42. 0.006 0.011 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.005 0.005 ↑ 1.0 6 1

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

44. 0.013 0.025 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
45. 0.012 0.012 ↑ 1.0 28 1

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

46. 0.755 1.437 ↓ 1.0 2,004 1

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

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

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

48. 0.006 0.009 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.003 0.003 ↑ 1.0 3 1

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

50. 32.653 32.697 ↑ 1.0 141 1,211

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

51. 0.044 0.044 ↑ 1.0 141 1

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

52. 15.707 15.743 ↑ 1.0 68 1,211

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

53. 0.036 0.036 ↑ 1.0 68 1

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

54. 0.051 0.133 ↑ 1.0 101 1

Hash (cost=83.13..83.13 rows=104 width=73) (actual time=0.133..0.133 rows=101 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
55. 0.082 0.082 ↑ 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.010..0.082 rows=101 loops=1)

  • Index Cond: (processing_stage = 'raw'::ctd.processing_stage)
56. 1.786 3.680 ↑ 1.0 4,867 1

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

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

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

58. 2.777 2.828 ↑ 1.0 141 101

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

59. 0.051 0.051 ↑ 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.051 rows=141 loops=1)

60. 1.399 1.414 ↑ 1.0 68 101

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

61. 0.015 0.015 ↑ 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.015 rows=68 loops=1)

Planning time : 5.144 ms
Execution time : 759.736 ms