explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iYVG

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 21,969.004 ↑ 1.0 10 1

Limit (cost=28,302.62..28,303.32 rows=10 width=206) (actual time=21,968.992..21,969.004 rows=10 loops=1)

2. 0.011 21,969.001 ↑ 5,061.8 10 1

Unique (cost=28,302.62..31,845.88 rows=50,618 width=206) (actual time=21,968.991..21,969.001 rows=10 loops=1)

3. 0.375 21,968.990 ↑ 5,061.8 10 1

Sort (cost=28,302.62..28,429.17 rows=50,618 width=206) (actual time=21,968.990..21,968.990 rows=10 loops=1)

  • Sort Key: cloudsort_outboundload.load_time, cloudsort_outboundload.id, cloudsort_outboundload.name, cloudsort_outboundload.route_id, cloudsort_outboundload.dockdoor_id, cloudsort_outboundload.load_status, cloudsort_outboundload.dispatched_time, cloudsort_outboundload.expected_containers_at_dispatch, cloudsort_outboundload.expected_packages_at_dispatch, cloudsort_outboundload.capacity, cloudsort_outboundload.weight_limit, cloudsort_outboundload.trailer_id, cloudsort_outboundload.created_on, cloudsort_outboundload.modified_on, (COALESCE((count(DISTINCT cloudsort_package.id) + count(DISTINCT t8.id)), '0'::bigint)), (count(DISTINCT t15.id) FILTER (WHERE ((cloudsort_event.station_id = cloudsort_route.station_id) AND ((cloudsort_event.event_type <> ALL ('{60,80,90}'::integer[])) OR (cloudsort_event.event_type IS NULL))))), cloudsort_route.name, cloudsort_route.station_id, cloudsort_route.created_on, cloudsort_route.modified_on, cloudsort_dockdoor.id, ((cloudsort_dockdoor.geo_shape)::bytea), cloudsort_dockdoor.name, cloudsort_dockdoor.station_id, cloudsort_dockdoor.outbound, cloudsort_dockdoor.created_on, cloudsort_dockdoor.modified_on
  • Sort Method: quicksort Memory: 141kB
4. 1,727.985 21,968.615 ↑ 115.6 438 1

GroupAggregate (cost=17,367.04..19,328.49 rows=50,618 width=206) (actual time=19,452.654..21,968.615 rows=438 loops=1)

  • Group Key: cloudsort_outboundload.id, cloudsort_route.id, cloudsort_dockdoor.id
5. 6,002.547 20,240.630 ↓ 132.2 6,690,355 1

Sort (cost=17,367.04..17,493.59 rows=50,618 width=306) (actual time=19,418.427..20,240.630 rows=6,690,355 loops=1)

  • Sort Key: cloudsort_outboundload.id, cloudsort_route.id, cloudsort_dockdoor.id
  • Sort Method: external merge Disk: 1,262,784kB
6. 1,850.877 14,238.083 ↓ 132.2 6,690,355 1

Hash Left Join (cost=706.75..6,142.41 rows=50,618 width=306) (actual time=7.909..14,238.083 rows=6,690,355 loops=1)

  • Hash Cond: (cloudsort_outboundload.dockdoor_id = cloudsort_dockdoor.id)
7. 1,272.299 12,387.104 ↓ 132.2 6,690,355 1

Hash Left Join (cost=696.93..5,997.87 rows=50,618 width=143) (actual time=7.794..12,387.104 rows=6,690,355 loops=1)

  • Hash Cond: (t15.last_process_step_id = cloudsort_event.id)
8. 1,115.580 11,112.381 ↓ 132.2 6,690,355 1

Nested Loop Left Join (cost=403.22..5,571.23 rows=50,618 width=139) (actual time=5.316..11,112.381 rows=6,690,355 loops=1)

9. 0.916 7.935 ↓ 46.8 2,619 1

Nested Loop Left Join (cost=4.22..26.46 rows=56 width=135) (actual time=0.098..7.935 rows=2,619 loops=1)

10. 0.442 3.953 ↓ 48.7 438 1

Nested Loop Left Join (cost=3.94..15.55 rows=9 width=131) (actual time=0.085..3.953 rows=438 loops=1)

11. 0.481 3.511 ↓ 48.7 438 1

Nested Loop Left Join (cost=3.66..10.06 rows=9 width=131) (actual time=0.078..3.511 rows=438 loops=1)

12. 0.193 2.592 ↓ 109.5 438 1

Nested Loop (cost=3.51..8.46 rows=4 width=127) (actual time=0.069..2.592 rows=438 loops=1)

13. 0.244 1.187 ↓ 101.0 303 1

Nested Loop Left Join (cost=3.23..7.08 rows=3 width=38) (actual time=0.060..1.187 rows=303 loops=1)

14. 0.085 0.337 ↓ 101.0 303 1

Nested Loop (cost=3.09..6.36 rows=3 width=34) (actual time=0.053..0.337 rows=303 loops=1)

15. 0.011 0.054 ↓ 3.0 3 1

Merge Join (cost=2.94..3.24 rows=1 width=8) (actual time=0.042..0.054 rows=3 loops=1)

  • Merge Cond: (cloudsort_station.id = cloudsort_stationstaff.station_id)
16. 0.014 0.014 ↑ 26.0 4 1

Index Only Scan using cloudsort_station_pkey on cloudsort_station (cost=0.14..14.70 rows=104 width=4) (actual time=0.011..0.014 rows=4 loops=1)

  • Heap Fetches: 4
17. 0.014 0.029 ↓ 3.0 3 1

Sort (cost=2.80..2.80 rows=1 width=4) (actual time=0.027..0.029 rows=3 loops=1)

  • Sort Key: cloudsort_stationstaff.station_id
  • Sort Method: quicksort Memory: 25kB
18. 0.015 0.015 ↓ 3.0 3 1

Seq Scan on cloudsort_stationstaff (cost=0.00..2.79 rows=1 width=4) (actual time=0.011..0.015 rows=3 loops=1)

  • Filter: ((user_id = 102) AND ((permissions & 7) > 0))
  • Rows Removed by Filter: 42
19. 0.198 0.198 ↑ 1.0 101 3

Index Scan using cloudsort_route_station_id_45ef7c9d on cloudsort_route (cost=0.15..2.11 rows=101 width=34) (actual time=0.005..0.066 rows=101 loops=3)

  • Index Cond: (station_id = cloudsort_station.id)
20. 0.606 0.606 ↑ 1.0 1 303

Index Scan using cloudsort_route2station_route_id_aaacc422 on cloudsort_route2station (cost=0.15..0.23 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=303)

  • Index Cond: (route_id = cloudsort_route.id)
21. 1.212 1.212 ↑ 1.0 1 303

Index Scan using cloudsort_outboundload_route_id_55d97965 on cloudsort_outboundload (cost=0.27..0.45 rows=1 width=89) (actual time=0.002..0.004 rows=1 loops=303)

  • Index Cond: (route_id = cloudsort_route.id)
22. 0.438 0.438 ↓ 0.0 0 438

Index Scan using cloudsort_container_outbound_load_id_7d44c719 on cloudsort_container (cost=0.15..0.35 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=438)

  • Index Cond: (outbound_load_id = cloudsort_outboundload.id)
23. 0.000 0.000 ↓ 0.0 0 438

Index Scan using cloudsort_package_container_id_b3d95427 on cloudsort_package t8 (cost=0.28..0.60 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=438)

  • Index Cond: (container_id = cloudsort_container.id)
24. 3.066 3.066 ↑ 1.3 6 438

Index Scan using cloudsort_package_outbound_load_id_ee810aed on cloudsort_package (cost=0.28..1.13 rows=8 width=8) (actual time=0.002..0.007 rows=6 loops=438)

  • Index Cond: (outbound_load_id = cloudsort_outboundload.id)
25. 932.364 9,988.866 ↓ 2.8 2,555 2,619

Hash Right Join (cost=399.00..481.85 rows=904 width=12) (actual time=3.128..3.814 rows=2,555 loops=2,619)

  • Hash Cond: (t15.zipcode_id = cloudsort_zipcode.id)
26. 961.173 961.173 ↑ 1.0 2,661 2,619

Seq Scan on cloudsort_package t15 (cost=0.00..72.61 rows=2,661 width=12) (actual time=0.001..0.367 rows=2,661 loops=2,619)

27. 738.558 8,095.329 ↓ 2.8 2,554 2,619

Hash (cost=387.70..387.70 rows=904 width=8) (actual time=3.091..3.091 rows=2,554 loops=2,619)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.000 7,356.771 ↓ 2.8 2,554 2,619

Nested Loop Left Join (cost=1.14..387.70 rows=904 width=8) (actual time=0.014..2.809 rows=2,554 loops=2,619)

29. 565.704 1,327.833 ↓ 2.8 2,554 2,619

Nested Loop Left Join (cost=0.84..77.84 rows=904 width=8) (actual time=0.012..0.507 rows=2,554 loops=2,619)

30. 2.619 15.714 ↑ 1.0 1 2,619

Nested Loop Left Join (cost=0.43..0.78 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=2,619)

31. 2.619 10.476 ↑ 1.0 1 2,619

Nested Loop Left Join (cost=0.29..0.50 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,619)

32. 5.238 5.238 ↑ 1.0 1 2,619

Index Only Scan using cloudsort_station_pkey on cloudsort_station t10 (cost=0.14..0.21 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,619)

  • Index Cond: (id = cloudsort_route2station.station_id)
  • Heap Fetches: 2,619
33. 2.619 2.619 ↑ 1.0 1 2,619

Index Scan using cloudsort_station2scheme_station_id_9f5116de on cloudsort_station2scheme (cost=0.14..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,619)

  • Index Cond: (station_id = t10.id)
34. 2.619 2.619 ↑ 1.0 1 2,619

Index Only Scan using cloudsort_scheme_pkey on cloudsort_scheme (cost=0.14..0.28 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,619)

  • Index Cond: (id = cloudsort_station2scheme.scheme_id)
  • Heap Fetches: 2,619
35. 746.415 746.415 ↓ 2.6 2,554 2,619

Index Scan using cloudsort_scheme2zipcode_scheme_id_0d46100e on cloudsort_scheme2zipcode (cost=0.42..67.24 rows=982 width=8) (actual time=0.006..0.285 rows=2,554 loops=2,619)

  • Index Cond: (scheme_id = cloudsort_scheme.id)
36. 6,687.838 6,687.838 ↑ 1.0 1 6,687,838

Index Only Scan using cloudsort_zipcode_pkey on cloudsort_zipcode (cost=0.29..0.34 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=6,687,838)

  • Index Cond: (id = cloudsort_scheme2zipcode.zipcode_id)
  • Heap Fetches: 3,014,894
37. 1.131 2.424 ↑ 1.0 9,498 1

Hash (cost=174.98..174.98 rows=9,498 width=12) (actual time=2.423..2.424 rows=9,498 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 537kB
38. 1.293 1.293 ↑ 1.0 9,498 1

Seq Scan on cloudsort_event (cost=0.00..174.98 rows=9,498 width=12) (actual time=0.005..1.293 rows=9,498 loops=1)

39. 0.052 0.102 ↑ 1.0 303 1

Hash (cost=6.03..6.03 rows=303 width=163) (actual time=0.102..0.102 rows=303 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
40. 0.050 0.050 ↑ 1.0 303 1

Seq Scan on cloudsort_dockdoor (cost=0.00..6.03 rows=303 width=163) (actual time=0.006..0.050 rows=303 loops=1)

Planning time : 17.699 ms
Execution time : 22,132.906 ms