explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k8LR

Settings
# exclusive inclusive rows x rows loops node
1. 43.987 606,388.732 ↑ 27.1 28,298 1

Hash Right Join (cost=11,147,589.35..18,233,215.72 rows=766,065 width=140) (actual time=419,028.737..606,388.732 rows=28,298 loops=1)

  • Hash Cond: (COALESCE(nd.tracker_code, nd_f1.tracker_code) = t.code)
2. 37.080 606,293.253 ↑ 61.0 14,214 1

Merge Full Join (cost=11,142,654.78..18,199,380.43 rows=866,586 width=68) (actual time=418,942.611..606,293.253 rows=14,214 loops=1)

  • Merge Cond: (nd.tracker_code = nd_f1.tracker_code)
3. 11.635 236,144.406 ↓ 1.1 14,214 1

Subquery Scan on nd (cost=6,582,167.50..7,594,730.90 rows=13,165 width=92) (actual time=203,894.094..236,144.406 rows=14,214 loops=1)

4. 14,434.991 236,132.771 ↓ 1.1 14,214 1

GroupAggregate (cost=6,582,167.50..7,594,237.21 rows=13,165 width=152) (actual time=203,894.091..236,132.771 rows=14,214 loops=1)

  • Group Key: point_2019_08_01.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 199
5. 28,792.354 221,697.780 ↑ 1.0 26,971,850 1

Sort (cost=6,582,167.50..6,649,597.12 rows=26,971,850 width=28) (actual time=203,884.367..221,697.780 rows=26,971,850 loops=1)

  • Sort Key: point_2019_08_01.tracker_code
  • Sort Method: external merge Disk: 1025576kB
6. 192,905.426 192,905.426 ↑ 1.0 26,971,850 1

Seq Scan on point_2019_08_01 (cost=0.00..2,607,850.50 rows=26,971,850 width=28) (actual time=0.017..192,905.426 rows=26,971,850 loops=1)

7. 10.615 370,111.767 ↑ 1.3 10,278 1

Subquery Scan on nd_f1 (cost=4,560,487.29..10,569,920.27 rows=13,165 width=28) (actual time=215,048.510..370,111.767 rows=10,278 loops=1)

8. 16,647.320 370,101.152 ↑ 1.3 10,278 1

GroupAggregate (cost=4,560,487.29..10,569,360.76 rows=13,165 width=88) (actual time=215,048.507..370,101.152 rows=10,278 loops=1)

  • Group Key: nd_1.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 400
9. 21,408.485 353,453.832 ↓ 7.0 14,490,292 1

Subquery Scan on nd_1 (cost=4,560,487.29..10,347,580.29 rows=2,060,931 width=90) (actual time=214,951.567..353,453.832 rows=14,490,292 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: 164921
10. 104,676.898 332,045.347 ↓ 1.3 14,655,213 1

WindowAgg (cost=4,560,487.29..7,927,017.35 rows=11,129,025 width=130) (actual time=214,951.004..332,045.347 rows=14,655,213 loops=1)

11. 22,517.917 227,368.449 ↓ 1.3 14,655,213 1

Sort (cost=4,560,487.29..4,588,309.85 rows=11,129,025 width=50) (actual time=214,950.948..227,368.449 rows=14,655,213 loops=1)

  • Sort Key: point_2019_08_01_1.tracker_code, point_2019_08_01_1.navigation_time
  • Sort Method: external merge Disk: 817480kB
12. 204,850.532 204,850.532 ↓ 1.3 14,655,213 1

Seq Scan on point_2019_08_01 point_2019_08_01_1 (cost=0.00..2,877,569.00 rows=11,129,025 width=50) (actual time=0.011..204,850.532 rows=14,655,213 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: 12316637
13. 4.581 51.492 ↓ 1.1 28,298 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Planning time : 9.269 ms