explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DhSq

Settings
# exclusive inclusive rows x rows loops node
1. 50.818 378,864.601 ↑ 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,647.602..378,864.601 rows=28,295 loops=1)

  • Hash Cond: (dc1.tracker_code = da.tracker_code)
2. 20.482 350,558.589 ↑ 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=71,392.380..350,558.589 rows=28,295 loops=1)

  • Merge Cond: (t.code = dc1.tracker_code)
3. 15.934 281,419.650 ↓ 1.1 28,295 1

Merge Left Join (cost=5,010,535.89..14,353,391.80 rows=25,201 width=40) (actual time=29,136.164..281,419.650 rows=28,295 loops=1)

  • Merge Cond: (t.code = tca1.tracker_code)
4. 18.219 110,852.929 ↓ 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,902.847..110,852.929 rows=28,295 loops=1)

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

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

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

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

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

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

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

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

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

Seq Scan on vehicle_tracker vt (cost=0.00..811.54 rows=28,372 width=8) (actual time=0.013..7.292 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. 6.531 16.217 ↑ 1.0 35,022 1

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

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

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

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

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

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

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

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

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

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

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

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 505
16. 6.575 110,757.367 ↑ 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,830.094..110,757.367 rows=10,338 loops=1)

17. 6,391.541 110,750.792 ↑ 1.3 10,338 1

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

  • Group Key: point_v.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 542
18. 76,356.129 104,359.251 ↓ 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,771.258..104,359.251 rows=14,650,077 loops=1)

19. 23,028.152 28,003.122 ↓ 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,771.226..28,003.122 rows=14,650,077 loops=1)

  • Sort Key: point_v.tracker_code, point_v.navigation_time
  • Sort Method: external merge Disk: 788536kB
20. 4,974.970 4,974.970 ↓ 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.011..4,974.970 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.479 170,550.787 ↑ 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,225.420..170,550.787 rows=10,278 loops=1)

22. 14,616.856 170,545.308 ↑ 1.3 10,278 1

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

  • Group Key: tca5.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 398
23. 21,506.852 155,928.452 ↓ 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,126.954..155,928.452 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. 105,215.389 134,421.600 ↓ 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,126.715..134,421.600 rows=14,650,077 loops=1)

25. 22,365.160 29,206.211 ↓ 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,126.688..29,206.211 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,841.051 6,841.051 ↓ 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.016..6,841.051 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.646 69,118.457 ↑ 1,048.5 16,245 1

Materialize (cost=6,170,776.69..8,633,683.32 rows=17,032,860 width=48) (actual time=42,254.474..69,118.457 rows=16,245 loops=1)

28. 19.546 69,109.811 ↑ 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=42,254.472..69,109.811 rows=14,096 loops=1)

29. 10,762.467 69,090.265 ↑ 1,208.3 14,096 1

GroupAggregate (cost=6,170,776.69..7,909,786.77 rows=17,032,860 width=185) (actual time=42,254.468..69,090.265 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. 30,411.640 58,327.798 ↑ 1.0 26,569,012 1

Sort (cost=6,170,776.69..6,237,201.64 rows=26,569,978 width=60) (actual time=42,244.055..58,327.798 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,248.841 27,916.158 ↑ 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,022.549..27,916.158 rows=26,569,012 loops=1)

  • Hash Cond: (point_v_2.tracker_code = md.tracker_code)
32. 2,644.807 2,644.807 ↑ 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.021..2,644.807 rows=26,569,012 loops=1)

33. 5.180 20,022.510 ↑ 1.3 10,338 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 694kB
34. 3.129 20,017.330 ↑ 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,047.128..20,017.330 rows=10,338 loops=1)

35. 12,916.015 20,014.201 ↑ 1.3 10,338 1

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

  • Group Key: point_v_3.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 542
36. 7,098.186 7,098.186 ↓ 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.007..7,098.186 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. 9.312 28,255.194 ↓ 1.1 14,096 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 734kB
38. 6.507 28,245.882 ↓ 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=12,906.943..28,245.882 rows=14,096 loops=1)

39. 11,259.858 28,239.375 ↓ 1.1 14,096 1

GroupAggregate (cost=4,377,655.63..4,776,368.45 rows=13,052 width=16) (actual time=12,906.941..28,239.375 rows=14,096 loops=1)

  • Group Key: point_v_4.tracker_code
  • Filter: (count(*) > 12)
  • Rows Removed by Filter: 212
40. 13,424.580 16,979.517 ↑ 1.0 26,569,012 1

Sort (cost=4,377,655.63..4,444,080.58 rows=26,569,978 width=24) (actual time=12,899.952..16,979.517 rows=26,569,012 loops=1)

  • Sort Key: point_v_4.tracker_code
  • Sort Method: external merge Disk: 884080kB
41. 3,554.937 3,554.937 ↑ 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.018..3,554.937 rows=26,569,012 loops=1)

Planning time : 15.441 ms