explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RK7O

Settings
# exclusive inclusive rows x rows loops node
1. 34.463 32,771.240 ↓ 1.1 28,298 1

Hash Right Join (cost=4,737,097.91..5,735,802.23 rows=25,553 width=140) (actual time=14,669.152..32,771.240 rows=28,298 loops=1)

  • Hash Cond: (COALESCE(nd.tracker_code, nd_f1.tracker_code) = t.code)
2. 22.947 32,684.560 ↓ 1.1 14,096 1

Merge Full Join (cost=4,732,163.34..5,730,222.15 rows=13,052 width=68) (actual time=14,612.628..32,684.560 rows=14,096 loops=1)

  • Merge Cond: (nd.tracker_code = nd_f1.tracker_code)
3. 7.277 30,268.469 ↓ 1.1 14,096 1

Subquery Scan on nd (cost=4,468,470.13..5,465,953.73 rows=13,052 width=92) (actual time=12,278.226..30,268.469 rows=14,096 loops=1)

4. 13,748.663 30,261.192 ↓ 1.1 14,096 1

GroupAggregate (cost=4,468,470.13..5,465,464.28 rows=13,052 width=152) (actual time=12,278.223..30,261.192 rows=14,096 loops=1)

  • Group Key: point_v.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
5. 12,634.473 16,512.529 ↑ 1.0 26,569,012 1

Sort (cost=4,468,470.13..4,534,895.08 rows=26,569,978 width=28) (actual time=12,268.628..16,512.529 rows=26,569,012 loops=1)

  • Sort Key: point_v.tracker_code
  • Sort Method: external merge Disk: 1010256kB
6. 3,878.056 3,878.056 ↑ 1.0 26,569,012 1

Seq Scan on point_v (cost=0.00..556,245.78 rows=26,569,978 width=28) (actual time=0.021..3,878.056 rows=26,569,012 loops=1)

7. 4.988 2,393.144 ↓ 51.4 10,278 1

Subquery Scan on nd_f1 (cost=263,693.21..263,713.21 rows=200 width=28) (actual time=2,334.392..2,393.144 rows=10,278 loops=1)

8. 44.804 2,388.156 ↓ 51.4 10,278 1

Finalize GroupAggregate (cost=263,693.21..263,704.71 rows=200 width=88) (actual time=2,334.388..2,388.156 rows=10,278 loops=1)

  • Group Key: point_v_d.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 398
9. 19.007 2,343.352 ↓ 73.0 29,185 1

Sort (cost=263,693.21..263,694.21 rows=400 width=96) (actual time=2,334.364..2,343.352 rows=29,185 loops=1)

  • Sort Key: point_v_d.tracker_code
  • Sort Method: quicksort Memory: 7826kB
10. 13.899 2,324.345 ↓ 73.0 29,185 1

Gather (cost=263,633.42..263,675.92 rows=400 width=96) (actual time=2,310.750..2,324.345 rows=29,185 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 1,416.538 2,310.446 ↓ 48.6 9,728 3

Partial HashAggregate (cost=262,633.42..262,635.92 rows=200 width=96) (actual time=2,303.131..2,310.446 rows=9,728 loops=3)

  • Group Key: point_v_d.tracker_code
12. 893.908 893.908 ↓ 1.0 4,830,022 3

Parallel Seq Scan on point_v_d (cost=0.00..168,045.14 rows=4,729,414 width=50) (actual time=0.046..893.908 rows=4,830,022 loops=3)

13. 4.460 52.217 ↓ 1.1 28,298 1

Hash (cost=4,615.16..4,615.16 rows=25,553 width=24) (actual time=52.217..52.217 rows=28,298 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1844kB
14. 7.196 47.757 ↓ 1.1 28,298 1

Hash Join (cost=2,773.01..4,615.16 rows=25,553 width=24) (actual time=22.836..47.757 rows=28,298 loops=1)

  • Hash Cond: (vt.tracker_id = t.id)
15. 4.481 31.578 ↓ 1.1 28,298 1

Hash Join (cost=1,563.11..3,082.66 rows=25,553 width=20) (actual time=13.652..31.578 rows=28,298 loops=1)

  • Hash Cond: (v.contractor_id = c.id)
16. 6.665 26.825 ↓ 1.0 28,997 1

Hash Join (cost=1,523.16..2,692.89 rows=27,728 width=20) (actual time=13.369..26.825 rows=28,997 loops=1)

  • Hash Cond: (vt.vehicle_id = v.id)
17. 6.992 6.992 ↓ 1.0 29,011 1

Seq Scan on vehicle_tracker vt (cost=0.00..811.54 rows=28,372 width=8) (actual time=0.007..6.992 rows=29,011 loops=1)

  • Filter: ((deleted IS NULL) AND (date_start <= '2019-08-01 00:00:00'::timestamp without time zone) AND ((date_finish >= '2019-08-02 00:00:00'::timestamp without time zone) OR (date_finish IS NULL)))
  • Rows Removed by Filter: 8626
18. 5.133 13.168 ↑ 1.0 35,022 1

Hash (cost=1,085.37..1,085.37 rows=35,023 width=20) (actual time=13.168..13.168 rows=35,022 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2337kB
19. 8.035 8.035 ↑ 1.0 35,022 1

Seq Scan on vehicle v (cost=0.00..1,085.37 rows=35,023 width=20) (actual time=0.007..8.035 rows=35,022 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 815
20. 0.106 0.272 ↑ 1.0 940 1

Hash (cost=28.20..28.20 rows=940 width=4) (actual time=0.272..0.272 rows=940 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
21. 0.166 0.166 ↑ 1.0 940 1

Seq Scan on contractor c (cost=0.00..28.20 rows=940 width=4) (actual time=0.014..0.166 rows=940 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 80
22. 4.573 8.983 ↑ 1.0 36,751 1

Hash (cost=750.51..750.51 rows=36,751 width=12) (actual time=8.983..8.983 rows=36,751 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2092kB
23. 4.410 4.410 ↑ 1.0 36,751 1

Seq Scan on tracker t (cost=0.00..750.51 rows=36,751 width=12) (actual time=0.013..4.410 rows=36,751 loops=1)

Planning time : 3.446 ms