explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oDZD

Settings
# exclusive inclusive rows x rows loops node
1. 41.756 190,647.814 ↑ 26.6 28,298 1

Hash Right Join (cost=6,981,199.71..14,058,506.43 rows=752,971 width=140) (actual time=26,534.369..190,647.814 rows=28,298 loops=1)

  • Hash Cond: (COALESCE(nd.tracker_code, nd_f1.tracker_code) = t.code)
2. 34.031 190,548.339 ↑ 60.4 14,096 1

Merge Full Join (cost=6,976,265.15..14,025,165.13 rows=851,774 width=68) (actual time=26,442.233..190,548.339 rows=14,096 loops=1)

  • Merge Cond: (nd.tracker_code = nd_f1.tracker_code)
3. 10.731 30,865.851 ↓ 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,938.711..30,865.851 rows=14,096 loops=1)

4. 13,641.763 30,855.120 ↓ 1.1 14,096 1

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

  • Group Key: point_v.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
5. 13,106.237 17,213.357 ↑ 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,929.780..17,213.357 rows=26,569,012 loops=1)

  • Sort Key: point_v.tracker_code
  • Sort Method: external merge Disk: 1010256kB
6. 4,107.120 4,107.120 ↑ 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.010..4,107.120 rows=26,569,012 loops=1)

7. 10.568 159,648.457 ↑ 1.3 10,278 1

Subquery Scan on nd_f1 (cost=2,507,795.01..8,527,204.61 rows=13,052 width=28) (actual time=13,503.513..159,648.457 rows=10,278 loops=1)

8. 16,186.540 159,637.889 ↑ 1.3 10,278 1

GroupAggregate (cost=2,507,795.01..8,526,649.90 rows=13,052 width=88) (actual time=13,503.510..159,637.889 rows=10,278 loops=1)

  • Group Key: nd_1.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 398
9. 21,327.667 143,451.349 ↓ 7.0 14,490,065 1

Subquery Scan on nd_1 (cost=2,507,795.01..8,304,503.33 rows=2,064,355 width=90) (actual time=13,404.001..143,451.349 rows=14,490,065 loops=1)

  • Filter: (((('2'::double precision * sqrt((power((nd_1.lat2 - nd_1.lat1), '2'::double precision) + power((nd_1.lng2 - nd_1.lng1), '2'::double precision)))) * sqrt((power((nd_1.lat2 - nd_1.lat3), '2'::double precision) + power((nd_1.lng2 - nd_1.lng3), '2'::double precision)))) > '1e-11'::double precision) AND (('1.00001'::double precision < abs(((((((power((nd_1.lat2 - nd_1.lat1), '2'::double precision) + power((nd_1.lng2 - nd_1.lng1), '2'::double precision)) + power((nd_1.lat2 - nd_1.lat3), '2'::double precision)) + power((nd_1.lng2 - nd_1.lng3), '2'::double precision)) - power((nd_1.lat1 - nd_1.lat3), '2'::double precision)) - power((nd_1.lng1 - nd_1.lng3), '2'::double precision)) / (('2'::double precision * sqrt((power((nd_1.lat2 - nd_1.lat1), '2'::double precision) + power((nd_1.lng2 - nd_1.lng1), '2'::double precision)))) * sqrt((power((nd_1.lat2 - nd_1.lat3), '2'::double precision) + power((nd_1.lng2 - nd_1.lng3), '2'::double precision))))))) OR (abs(((((((power((nd_1.lat2 - nd_1.lat1), '2'::double precision) + power((nd_1.lng2 - nd_1.lng1), '2'::double precision)) + power((nd_1.lat2 - nd_1.lat3), '2'::double precision)) + power((nd_1.lng2 - nd_1.lng3), '2'::double precision)) - power((nd_1.lat1 - nd_1.lat3), '2'::double precision)) - power((nd_1.lng1 - nd_1.lng3), '2'::double precision)) / (('2'::double precision * sqrt((power((nd_1.lat2 - nd_1.lat1), '2'::double precision) + power((nd_1.lng2 - nd_1.lng1), '2'::double precision)))) * sqrt((power((nd_1.lat2 - nd_1.lat3), '2'::double precision) + power((nd_1.lng2 - nd_1.lng3), '2'::double precision)))))) < '1'::double precision)))
  • Rows Removed by Filter: 160012
10. 102,976.319 122,123.682 ↓ 1.3 14,650,077 1

WindowAgg (cost=2,507,795.01..5,879,918.60 rows=11,147,516 width=130) (actual time=13,403.445..122,123.682 rows=14,650,077 loops=1)

11. 14,527.235 19,147.363 ↓ 1.3 14,650,077 1

Sort (cost=2,507,795.01..2,535,663.80 rows=11,147,516 width=50) (actual time=13,403.408..19,147.363 rows=14,650,077 loops=1)

  • Sort Key: point_v_1.tracker_code, point_v_1.navigation_time
  • Sort Method: external merge Disk: 817192kB
12. 4,620.128 4,620.128 ↓ 1.3 14,650,077 1

Seq Scan on point_v point_v_1 (cost=0.00..821,945.56 rows=11,147,516 width=50) (actual time=0.013..4,620.128 rows=14,650,077 loops=1)

  • Filter: (((nsat > 3) OR (nsat IS NULL)) AND ((pdop < 8) OR (pdop IS NULL)) AND (speed > 5) AND (speed < 150))
  • Rows Removed by Filter: 11918935
13. 7.484 57.719 ↓ 1.1 28,298 1

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

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

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

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

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

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

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

  • Hash Cond: (vt.vehicle_id = v.id)
17. 6.895 6.895 ↓ 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.895 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. 4.970 12.762 ↑ 1.0 35,022 1

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

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

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

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

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

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

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

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

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

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

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

Planning time : 1.976 ms