explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mepG

Settings
# exclusive inclusive rows x rows loops node
1. 44.048 463,581.421 ↓ 1.1 28,298 1

Hash Right Join (cost=7,998,669.26..9,017,771.91 rows=25,553 width=140) (actual time=272,553.655..463,581.421 rows=28,298 loops=1)

  • Hash Cond: (COALESCE(nd.tracker_code, nd_f1.tracker_code) = t.code)
2.          

CTE point_w

3. 222,675.098 222,675.098 ↑ 1.0 26,569,012 1

Seq Scan on point_2019_08_01 (cost=0.00..2,675,280.12 rows=26,944,878 width=62) (actual time=0.021..222,675.098 rows=26,569,012 loops=1)

  • Filter: (NOT (geom ~= '0101000020E610000000000000000000000000000000000000'::geometry))
  • Rows Removed by Filter: 402838
4. 38.530 463,447.443 ↓ 70.5 14,096 1

Merge Full Join (cost=5,318,454.56..6,337,169.90 rows=200 width=68) (actual time=272,427.891..463,447.443 rows=14,096 loops=1)

  • Merge Cond: (nd.tracker_code = nd_f1.tracker_code)
5. 11.349 281,359.051 ↓ 70.5 14,096 1

Subquery Scan on nd (cost=4,509,047.08..5,519,497.01 rows=200 width=92) (actual time=247,431.266..281,359.051 rows=14,096 loops=1)

6. 14,193.634 281,347.702 ↓ 70.5 14,096 1

GroupAggregate (cost=4,509,047.08..5,519,489.51 rows=200 width=152) (actual time=247,431.264..281,347.702 rows=14,096 loops=1)

  • Group Key: point_w.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
7. 29,936.528 267,154.068 ↑ 1.0 26,569,012 1

Sort (cost=4,509,047.08..4,576,409.28 rows=26,944,878 width=28) (actual time=247,422.095..267,154.068 rows=26,569,012 loops=1)

  • Sort Key: point_w.tracker_code
  • Sort Method: external merge Disk: 1010280kB
8. 237,217.540 237,217.540 ↑ 1.0 26,569,012 1

CTE Scan on point_w (cost=0.00..538,897.56 rows=26,944,878 width=28) (actual time=0.023..237,217.540 rows=26,569,012 loops=1)

9. 10.376 182,049.862 ↓ 51.4 10,278 1

Subquery Scan on nd_f1 (cost=809,407.48..817,663.89 rows=200 width=28) (actual time=24,996.617..182,049.862 rows=10,278 loops=1)

10. 17,052.410 182,039.486 ↓ 51.4 10,278 1

GroupAggregate (cost=809,407.48..817,655.39 rows=200 width=88) (actual time=24,996.615..182,039.486 rows=10,278 loops=1)

  • Group Key: nd_1.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 398
11. 23,071.795 164,987.076 ↓ 5,123.8 14,490,065 1

Subquery Scan on nd_1 (cost=809,407.48..817,347.88 rows=2,828 width=90) (actual time=24,893.720..164,987.076 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
12. 111,189.661 141,915.281 ↓ 959.4 14,650,077 1

WindowAgg (cost=809,407.48..814,026.66 rows=15,270 width=130) (actual time=24,893.148..141,915.281 rows=14,650,077 loops=1)

13. 15,143.812 30,725.620 ↓ 959.4 14,650,077 1

Sort (cost=809,407.48..809,445.66 rows=15,270 width=50) (actual time=24,893.112..30,725.620 rows=14,650,077 loops=1)

  • Sort Key: point_w_1.tracker_code, point_w_1.navigation_time
  • Sort Method: external merge Disk: 817192kB
14. 15,581.808 15,581.808 ↓ 959.4 14,650,077 1

CTE Scan on point_w point_w_1 (cost=0.00..808,346.34 rows=15,270 width=50) (actual time=11.702..15,581.808 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
15. 9.204 89.930 ↓ 1.1 28,298 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1844kB
16. 13.806 80.726 ↓ 1.1 28,298 1

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

  • Hash Cond: (vt.tracker_id = t.id)
17. 7.645 54.988 ↓ 1.1 28,298 1

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

  • Hash Cond: (v.contractor_id = c.id)
18. 14.109 47.020 ↓ 1.0 28,997 1

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

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

Seq Scan on vehicle_tracker vt (cost=0.00..811.54 rows=28,372 width=8) (actual time=0.009..13.148 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
20. 7.682 19.763 ↑ 1.0 35,022 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2337kB
21. 12.081 12.081 ↑ 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..12.081 rows=35,022 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 815
22. 0.109 0.323 ↑ 1.0 940 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
23. 0.214 0.214 ↑ 1.0 940 1

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

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 80
24. 6.464 11.932 ↑ 1.0 36,751 1

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

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

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

Planning time : 2.108 ms