explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vIW

Settings
# exclusive inclusive rows x rows loops node
1. 50.140 368,689.451 ↑ 34,249.5 28,295 1

Hash Left Join (cost=15,958,268.38..49,946,532.17 rows=969,090,704 width=140) (actual time=99,565.187..368,689.451 rows=28,295 loops=1)

  • Hash Cond: (dc1.tracker_code = da.tracker_code)
2. 21.004 342,238.648 ↑ 524.8 28,295 1

Merge Left Join (cost=11,181,312.59..23,178,217.17 rows=14,849,689 width=88) (actual time=73,164.497..342,238.648 rows=28,295 loops=1)

  • Merge Cond: (t.code = dc1.tracker_code)
3. 14.367 277,357.020 ↓ 1.1 28,295 1

Merge Left Join (cost=5,010,535.89..14,353,391.80 rows=25,201 width=40) (actual time=28,686.917..277,357.020 rows=28,295 loops=1)

  • Merge Cond: (t.code = tca1.tracker_code)
4. 16.787 109,916.628 ↓ 1.1 28,295 1

Merge Left Join (cost=2,476,145.20..5,821,161.87 rows=25,201 width=32) (actual time=13,247.643..109,916.628 rows=28,295 loops=1)

  • Merge Cond: (t.code = tcd1.tracker_code)
5. 15.656 65.611 ↓ 1.1 28,295 1

Sort (cost=6,451.19..6,514.19 rows=25,201 width=24) (actual time=56.868..65.611 rows=28,295 loops=1)

  • Sort Key: t.code
  • Sort Method: quicksort Memory: 2979kB
6. 7.137 49.955 ↓ 1.1 28,295 1

Hash Join (cost=2,766.69..4,608.84 rows=25,201 width=24) (actual time=25.372..49.955 rows=28,295 loops=1)

  • Hash Cond: (vt.tracker_id = t.id)
7. 4.420 31.772 ↓ 1.1 28,298 1

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

  • Hash Cond: (v.contractor_id = c.id)
8. 6.795 27.070 ↓ 1.0 28,997 1

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

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

Seq Scan on vehicle_tracker vt (cost=0.00..811.54 rows=28,372 width=8) (actual time=0.008..6.851 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
10. 5.235 13.424 ↑ 1.0 35,022 1

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

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

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 815
12. 0.117 0.282 ↑ 1.0 940 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
13. 0.165 0.165 ↑ 1.0 940 1

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

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 80
14. 4.871 11.046 ↑ 1.0 36,246 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2070kB
15. 6.175 6.175 ↑ 1.0 36,246 1

Seq Scan on tracker t (cost=0.00..750.51 rows=36,246 width=12) (actual time=0.028..6.175 rows=36,246 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 505
16. 7.052 109,834.230 ↑ 1.3 10,338 1

Subquery Scan on tcd1 (cost=2,469,694.01..5,814,438.26 rows=13,052 width=16) (actual time=13,186.305..109,834.230 rows=10,338 loops=1)

17. 6,384.697 109,827.178 ↑ 1.3 10,338 1

GroupAggregate (cost=2,469,694.01..5,814,111.96 rows=13,052 width=48) (actual time=13,186.304..109,827.178 rows=10,338 loops=1)

  • Group Key: point_v.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 542
18. 76,439.973 103,442.481 ↓ 1.3 14,650,077 1

WindowAgg (cost=2,469,694.01..5,563,129.70 rows=11,147,516 width=48) (actual time=13,129.743..103,442.481 rows=14,650,077 loops=1)

19. 22,412.180 27,002.508 ↓ 1.3 14,650,077 1

Sort (cost=2,469,694.01..2,497,562.80 rows=11,147,516 width=48) (actual time=13,129.713..27,002.508 rows=14,650,077 loops=1)

  • Sort Key: point_v.tracker_code, point_v.navigation_time
  • Sort Method: external merge Disk: 788536kB
20. 4,590.328 4,590.328 ↓ 1.3 14,650,077 1

Seq Scan on point_v (cost=0.00..821,945.56 rows=11,147,516 width=48) (actual time=0.019..4,590.328 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
21. 5.741 167,426.025 ↑ 1.3 10,278 1

Subquery Scan on tca1 (cost=2,534,390.69..8,532,020.50 rows=13,052 width=16) (actual time=15,431.130..167,426.025 rows=10,278 loops=1)

22. 14,817.958 167,420.284 ↑ 1.3 10,278 1

GroupAggregate (cost=2,534,390.69..8,531,694.20 rows=13,052 width=48) (actual time=15,431.128..167,420.284 rows=10,278 loops=1)

  • Group Key: tca5.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 398
23. 22,449.576 152,602.326 ↓ 7.0 14,490,065 1

Subquery Scan on tca5 (cost=2,534,390.69..8,325,302.05 rows=2,062,290 width=88) (actual time=15,330.069..152,602.326 rows=14,490,065 loops=1)

  • Filter: (((('2'::double precision * sqrt((power((tca5.lat2 - tca5.lat1), '2'::double precision) + power((tca5.lng2 - tca5.lng1), '2'::double precision)))) * sqrt((power((tca5.lat2 - tca5.lat3), '2'::double precision) + power((tca5.lng2 - tca5.lng3), '2'::double precision)))) > '1e-11'::double precision) AND (('1.00001'::double precision < abs(((((((power((tca5.lat2 - tca5.lat1), '2'::double precision) + power((tca5.lng2 - tca5.lng1), '2'::double precision)) + power((tca5.lat2 - tca5.lat3), '2'::double precision)) + power((tca5.lng2 - tca5.lng3), '2'::double precision)) - power((tca5.lat1 - tca5.lat3), '2'::double precision)) - power((tca5.lng1 - tca5.lng3), '2'::double precision)) / (('2'::double precision * sqrt((power((tca5.lat2 - tca5.lat1), '2'::double precision) + power((tca5.lng2 - tca5.lng1), '2'::double precision)))) * sqrt((power((tca5.lat2 - tca5.lat3), '2'::double precision) + power((tca5.lng2 - tca5.lng3), '2'::double precision))))))) OR (abs(((((((power((tca5.lat2 - tca5.lat1), '2'::double precision) + power((tca5.lng2 - tca5.lng1), '2'::double precision)) + power((tca5.lat2 - tca5.lat3), '2'::double precision)) + power((tca5.lng2 - tca5.lng3), '2'::double precision)) - power((tca5.lat1 - tca5.lat3), '2'::double precision)) - power((tca5.lng1 - tca5.lng3), '2'::double precision)) / (('2'::double precision * sqrt((power((tca5.lat2 - tca5.lat1), '2'::double precision) + power((tca5.lng2 - tca5.lng1), '2'::double precision)))) * sqrt((power((tca5.lat2 - tca5.lat3), '2'::double precision) + power((tca5.lng2 - tca5.lng3), '2'::double precision)))))) < '1'::double precision)))
  • Rows Removed by Filter: 160012
24. 107,642.690 130,152.750 ↓ 1.3 14,650,077 1

WindowAgg (cost=2,534,390.69..5,903,142.01 rows=11,136,368 width=96) (actual time=15,329.829..130,152.750 rows=14,650,077 loops=1)

25. 15,575.579 22,510.060 ↓ 1.3 14,650,077 1

Sort (cost=2,534,390.69..2,562,231.61 rows=11,136,368 width=48) (actual time=15,329.799..22,510.060 rows=14,650,077 loops=1)

  • Sort Key: point_v_1.tracker_code, point_v_1.navigation_time
  • Sort Method: external merge Disk: 788536kB
26. 6,934.481 6,934.481 ↓ 1.3 14,650,077 1

Seq Scan on point_v point_v_1 (cost=0.00..888,370.51 rows=11,136,368 width=48) (actual time=0.022..6,934.481 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) AND (NOT (geom ~= '0101000020E610000000000000000000000000000000000000'::geometry)))
  • Rows Removed by Filter: 11918935
27. 8.198 64,860.624 ↑ 1,048.5 16,245 1

Materialize (cost=6,170,776.69..8,633,683.32 rows=17,032,860 width=48) (actual time=44,475.782..64,860.624 rows=16,245 loops=1)

28. 20.146 64,852.426 ↑ 1,208.3 14,096 1

Subquery Scan on dc1 (cost=6,170,776.69..8,591,101.17 rows=17,032,860 width=48) (actual time=44,475.779..64,852.426 rows=14,096 loops=1)

29. 10,823.816 64,832.280 ↑ 1,208.3 14,096 1

GroupAggregate (cost=6,170,776.69..7,909,786.77 rows=17,032,860 width=185) (actual time=44,475.774..64,832.280 rows=14,096 loops=1)

  • Group Key: point_v_2.tracker_code, md.speed_avg, md.mileage
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
30. 24,485.594 54,008.464 ↑ 1.0 26,569,012 1

Sort (cost=6,170,776.69..6,237,201.64 rows=26,569,978 width=60) (actual time=44,465.033..54,008.464 rows=26,569,012 loops=1)

  • Sort Key: point_v_2.tracker_code, md.speed_avg, md.mileage
  • Sort Method: external merge Disk: 1059640kB
31. 5,724.285 29,522.870 ↑ 1.0 26,569,012 1

Hash Left Join (cost=1,003,617.16..1,895,290.84 rows=26,569,978 width=60) (actual time=20,923.461..29,522.870 rows=26,569,012 loops=1)

  • Hash Cond: (point_v_2.tracker_code = md.tracker_code)
32. 2,875.154 2,875.154 ↑ 1.0 26,569,012 1

Seq Scan on point_v point_v_2 (cost=0.00..556,245.78 rows=26,569,978 width=20) (actual time=0.012..2,875.154 rows=26,569,012 loops=1)

33. 6.287 20,923.431 ↑ 1.3 10,338 1

Hash (cost=1,003,454.01..1,003,454.01 rows=13,052 width=48) (actual time=20,923.431..20,923.431 rows=10,338 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 694kB
34. 3.968 20,917.144 ↑ 1.3 10,338 1

Subquery Scan on md (cost=999,734.19..1,003,454.01 rows=13,052 width=48) (actual time=12,526.454..20,917.144 rows=10,338 loops=1)

35. 13,651.812 20,913.176 ↑ 1.3 10,338 1

HashAggregate (cost=999,734.19..1,003,323.49 rows=13,052 width=48) (actual time=12,526.453..20,913.176 rows=10,338 loops=1)

  • Group Key: point_v_3.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 542
36. 7,261.364 7,261.364 ↓ 1.3 14,650,077 1

Seq Scan on point_v point_v_3 (cost=0.00..888,370.51 rows=11,136,368 width=42) (actual time=0.006..7,261.364 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) AND (NOT (geom ~= '0101000020E610000000000000000000000000000000000000'::geometry)))
  • Rows Removed by Filter: 11918935
37. 7.799 26,400.663 ↓ 1.1 14,096 1

Hash (cost=4,776,792.64..4,776,792.64 rows=13,052 width=12) (actual time=26,400.663..26,400.663 rows=14,096 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 734kB
38. 5.058 26,392.864 ↓ 1.1 14,096 1

Subquery Scan on da (cost=4,377,655.63..4,776,792.64 rows=13,052 width=12) (actual time=11,849.481..26,392.864 rows=14,096 loops=1)

39. 10,724.149 26,387.806 ↓ 1.1 14,096 1

GroupAggregate (cost=4,377,655.63..4,776,368.45 rows=13,052 width=16) (actual time=11,849.480..26,387.806 rows=14,096 loops=1)

  • Group Key: point_v_4.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
40. 12,452.471 15,663.657 ↑ 1.0 26,569,012 1

Sort (cost=4,377,655.63..4,444,080.58 rows=26,569,978 width=24) (actual time=11,842.406..15,663.657 rows=26,569,012 loops=1)

  • Sort Key: point_v_4.tracker_code
  • Sort Method: external merge Disk: 884080kB
41. 3,211.186 3,211.186 ↑ 1.0 26,569,012 1

Seq Scan on point_v point_v_4 (cost=0.00..556,245.78 rows=26,569,978 width=24) (actual time=0.016..3,211.186 rows=26,569,012 loops=1)

Planning time : 15.905 ms