explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cou

Settings
# exclusive inclusive rows x rows loops node
1. 261.388 28,566.363 ↓ 4.1 10,579 1

Result (cost=5,607,528.12..5,609,183.20 rows=2,576 width=495) (actual time=28,298.879..28,566.363 rows=10,579 loops=1)

2. 53.708 28,304.975 ↓ 4.1 10,579 1

Sort (cost=5,607,528.12..5,607,534.56 rows=2,576 width=577) (actual time=28,298.547..28,304.975 rows=10,579 loops=1)

  • Sort Key: event.event_date, ctd_drop.drop_start_time, site.name
  • Sort Method: external merge Disk: 4232kB
3. 165.628 28,251.267 ↓ 4.1 10,579 1

Hash Left Join (cost=5,598,183.53..5,607,382.18 rows=2,576 width=577) (actual time=18,913.405..28,251.267 rows=10,579 loops=1)

  • Hash Cond: (event.organization_pk = organization.pk)
4. 16.605 28,085.623 ↓ 4.1 10,579 1

Merge Join (cost=5,598,182.40..5,607,036.50 rows=2,576 width=470) (actual time=18,908.291..28,085.623 rows=10,579 loops=1)

  • Merge Cond: (ctd_drop.pk = event_aux.sample_pk)
5. 9.578 18,920.138 ↑ 2.2 10,579 1

Merge Left Join (cost=5,598,181.98..5,598,296.56 rows=22,859 width=400) (actual time=18,902.418..18,920.138 rows=10,579 loops=1)

  • Merge Cond: (ctd_drop.pk = ctd_drop_1.pk)
6. 33.300 1,625.123 ↑ 2.2 10,579 1

Sort (cost=27,896.79..27,953.94 rows=22,859 width=359) (actual time=1,616.977..1,625.123 rows=10,579 loops=1)

  • Sort Key: ctd_drop.pk
  • Sort Method: external sort Disk: 3200kB
7. 12.049 1,591.823 ↑ 2.2 10,579 1

Hash Join (cost=21,447.87..22,489.75 rows=22,859 width=359) (actual time=1,553.373..1,591.823 rows=10,579 loops=1)

  • Hash Cond: (survey_event_actual.event_pk = ctd_drop.event_pk)
8. 19.962 57.098 ↓ 1.0 23,075 1

GroupAggregate (cost=2,533.79..3,004.23 rows=22,857 width=36) (actual time=30.673..57.098 rows=23,075 loops=1)

  • Group Key: survey_event_actual.event_pk
9. 16.266 37.136 ↓ 1.0 24,855 1

Sort (cost=2,533.79..2,595.36 rows=24,630 width=12) (actual time=30.661..37.136 rows=24,855 loops=1)

  • Sort Key: survey_event_actual.event_pk
  • Sort Method: quicksort Memory: 1947kB
10. 12.929 20.870 ↓ 1.0 24,855 1

Hash Join (cost=6.30..737.26 rows=24,630 width=12) (actual time=0.727..20.870 rows=24,855 loops=1)

  • Hash Cond: (survey_event_actual.survey_pk = survey.pk)
11. 7.807 7.807 ↓ 1.0 24,855 1

Seq Scan on survey_event_actual (cost=0.00..392.30 rows=24,630 width=8) (actual time=0.572..7.807 rows=24,855 loops=1)

12. 0.077 0.134 ↓ 1.0 194 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
13. 0.057 0.057 ↓ 1.0 194 1

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

14. 10.024 1,522.676 ↓ 1.0 10,605 1

Hash (cost=18,782.99..18,782.99 rows=10,487 width=331) (actual time=1,522.676..1,522.676 rows=10,605 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3294kB
15. 16.723 1,512.652 ↓ 1.0 10,605 1

Hash Left Join (cost=4,844.09..18,782.99 rows=10,487 width=331) (actual time=263.955..1,512.652 rows=10,605 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))
16. 6.305 1,490.571 ↓ 1.0 10,605 1

Hash Join (cost=4,746.62..18,341.89 rows=10,487 width=284) (actual time=258.570..1,490.571 rows=10,605 loops=1)

  • Hash Cond: (ctd_drop.ctd_unit_pk = ctd_unit.pk)
17. 7.019 1,484.242 ↓ 1.0 10,605 1

Hash Join (cost=4,745.01..18,196.08 rows=10,487 width=277) (actual time=258.540..1,484.242 rows=10,605 loops=1)

  • Hash Cond: (event.site_pk = site.pk)
18. 15.540 1,260.396 ↓ 1.0 10,605 1

Hash Join (cost=759.96..14,066.83 rows=10,487 width=231) (actual time=41.691..1,260.396 rows=10,605 loops=1)

  • Hash Cond: (event.pk = ctd_drop.event_pk)
19. 1,206.295 1,206.295 ↓ 1.0 23,685 1

Seq Scan on event (cost=0.00..13,084.67 rows=23,467 width=71) (actual time=0.002..1,206.295 rows=23,685 loops=1)

20. 5.161 38.561 ↓ 1.0 10,605 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 1914kB
21. 33.400 33.400 ↓ 1.0 10,605 1

Seq Scan on ctd_drop (cost=0.00..628.87 rows=10,487 width=160) (actual time=0.584..33.400 rows=10,605 loops=1)

22. 0.836 216.827 ↑ 1.0 1,846 1

Hash (cost=3,961.98..3,961.98 rows=1,846 width=54) (actual time=216.827..216.827 rows=1,846 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
23. 1.492 215.991 ↑ 1.0 1,846 1

Hash Left Join (cost=1.14..3,961.98 rows=1,846 width=54) (actual time=0.021..215.991 rows=1,846 loops=1)

  • Hash Cond: (site.workarea_pk = workarea.pk)
24. 214.489 214.489 ↑ 1.0 1,846 1

Seq Scan on site (cost=0.00..3,935.46 rows=1,846 width=49) (actual time=0.004..214.489 rows=1,846 loops=1)

25. 0.007 0.010 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.003 0.003 ↑ 1.0 6 1

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

27. 0.012 0.024 ↓ 1.0 28 1

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

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

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

29. 0.819 5.358 ↓ 1.0 2,004 1

Hash (cost=67.79..67.79 rows=1,979 width=59) (actual time=5.357..5.358 rows=2,004 loops=1)

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

Seq Scan on ctd_unit_maintenance (cost=0.00..67.79 rows=1,979 width=59) (actual time=0.008..4.539 rows=2,004 loops=1)

31. 0.005 17,285.437 ↓ 0.0 0 1

Materialize (cost=5,570,285.19..5,570,285.29 rows=6 width=45) (actual time=17,285.437..17,285.437 rows=0 loops=1)

32. 0.001 17,285.432 ↓ 0.0 0 1

Unique (cost=5,570,285.19..5,570,285.22 rows=6 width=53) (actual time=17,285.432..17,285.432 rows=0 loops=1)

33. 0.013 17,285.431 ↓ 0.0 0 1

Sort (cost=5,570,285.19..5,570,285.20 rows=6 width=53) (actual time=17,285.431..17,285.431 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
34. 12,593.115 17,285.418 ↓ 0.0 0 1

Nested Loop (cost=0.00..5,570,285.11 rows=6 width=53) (actual time=17,285.418..17,285.418 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: 19576830
35. 4.893 4.893 ↓ 1.0 10,605 1

Seq Scan on ctd_drop ctd_drop_1 (cost=0.00..628.87 rows=10,487 width=100) (actual time=0.007..4.893 rows=10,605 loops=1)

36. 4,682.791 4,687.410 ↑ 1.0 1,846 10,605

Materialize (cost=0.00..3,944.69 rows=1,846 width=41) (actual time=0.000..0.442 rows=1,846 loops=10,605)

37. 4.619 4.619 ↑ 1.0 1,846 1

Seq Scan on site site2 (cost=0.00..3,935.46 rows=1,846 width=41) (actual time=0.005..4.619 rows=1,846 loops=1)

38. 9,148.880 9,148.880 ↓ 1.0 10,605 1

Index Scan using event_aux_sample_pk_idx on event_aux (cost=0.42..51,874.10 rows=10,330 width=74) (actual time=5.866..9,148.880 rows=10,605 loops=1)

  • Filter: ((sample_type)::text = 'CTD'::text)
  • Rows Removed by Filter: 103889
39. 0.007 0.016 ↑ 1.0 6 1

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

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

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

Planning time : 101.852 ms
Execution time : 28,574.253 ms