explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ni1k : test

Settings
# exclusive inclusive rows x rows loops node
1. 28.039 17,171.089 ↓ 0.0 0 1

Update on public.dailysummary ds (cost=2,053,814.18..2,071,301.06 rows=157,545 width=901) (actual time=17,171.089..17,171.089 rows=0 loops=1)

  • Buffers: shared hit=28667 read=57886 dirtied=275 written=1, temp read=57640 written=58276
2.          

CTE dates

3. 0.042 0.042 ↑ 41.7 24 1

Function Scan on pg_catalog.generate_series d_1 (cost=0.01..12.51 rows=1,000 width=8) (actual time=0.036..0.042 rows=24 loops=1)

  • Output: (d_1.d)::date
  • Function Call: generate_series(('2019-08-01'::date)::timestamp with time zone, ('2019-08-24'::date)::timestamp with time zone, '1 day'::interval)
4.          

CTE vehicles

5. 0.189 0.189 ↑ 1.0 32 1

Index Scan using vehicle_pkey on public.vehicle (cost=0.28..100.65 rows=32 width=34) (actual time=0.029..0.189 rows=32 loops=1)

  • Output: vehicle.id, vehicle.code, (vehicle.status = 'A'::bpchar), vehicle.devicecode
  • Index Cond: (vehicle.id = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[]))
  • Buffers: shared hit=81
6.          

CTE dailysummaries

7. 1.265 113.063 ↓ 16.4 720 1

HashAggregate (cost=2,850.45..2,850.89 rows=44 width=56) (actual time=112.743..113.063 rows=720 loops=1)

  • Output: dailysummary.vehicleid, dailysummary.summarydate, sum(dailysummary.distance), count(*), sum(dailysummary.ignitionon), sum(dailysummary.ignitionoff), sum(dailysummary.pointcount), sum(dailysummary.powerfailcount), sum(dailysummary.paniccount), sum(dailysummary.gpsfixcount), sum(dailysummary.movingtime), sum(dailysummary.stoppedtime), sum(dailysummary.stoppedandignition)
  • Group Key: dailysummary.summarydate, dailysummary.vehicleid
  • Buffers: shared hit=104 read=255
8. 0.513 111.798 ↓ 1.7 720 1

Bitmap Heap Scan on public.dailysummary (cost=1,212.68..2,836.28 rows=436 width=56) (actual time=111.334..111.798 rows=720 loops=1)

  • Output: dailysummary.id, dailysummary.ownerid, dailysummary.summarydate, dailysummary.vehiclecode, dailysummary.vehicleid, dailysummary.disconnectedtime, dailysummary.distance, dailysummary.endodometer, dailysummary.endoperation, dailysummary.firstpoint, dailysummary.gpsfixcount, dailysummary.ignitionoff, dailysummary.ignitionon, dailysummary.lastpoint, dailysummary.maxspeed, dailysummary.meanspeed, dailysummary.movingtime, dailysummary.paniccount, dailysummary.pointcount, dailysummary.startodometer, dailysummary.startoperation, dailysummary.stoppedandignition, dailysummary.stoppedtime, dailysummary.avgoffinterval, dailysummary.avgoninterval, dailysummary.gpserrorcount, dailysummary.gpsfixignon, dailysummary.ignitionoffcount, dailysummary.ignitiononcount, dailysummary.maxpointinterval, dailysummary.offdistance, dailysummary.powerfailcount, dailysummary.speedrange_1, dailysummary.speedrange_2, dailysummary.speedrange_3, dailysummary.speedrange_4, dailysummary.speedrange_5, dailysummary.plannedtrips, dailysummary.plannedstops, dailysummary.executedstops, dailysummary.executedtrip, dailysummary.quickstops, dailysummary.mediumstops, dailysummary.longstops, dailysummary.tripscount, dailysummary.poivisits, dailysummary.notifications, dailysummary.input1, dailysummary.input2, dailysummary.poibasevisits, dailysummary.poinotbasevisits, dailysummary.poibaseduration, dailysummary.poinotbaseduration
  • Recheck Cond: ((dailysummary.vehicleid = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[])) AND (dailysummary.summarydate >= '2019-08-01'::date) AND (dailysummary.summarydate <= '2019-08-24'::date))
  • Heap Blocks: exact=61
  • Buffers: shared hit=104 read=255
9. 1.612 111.285 ↓ 0.0 0 1

BitmapAnd (cost=1,212.68..1,212.68 rows=436 width=0) (actual time=111.285..111.285 rows=0 loops=1)

  • Buffers: shared hit=74 read=224
10. 90.239 90.239 ↓ 1.4 16,774 1

Bitmap Index Scan on dailysummaryvhcidx (cost=0.00..358.48 rows=11,828 width=0) (actual time=90.239..90.239 rows=16,774 loops=1)

  • Index Cond: (dailysummary.vehicleid = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[]))
  • Buffers: shared hit=73 read=78
11. 19.434 19.434 ↓ 1.1 43,520 1

Bitmap Index Scan on dailysummarysummarydateidx (cost=0.00..853.73 rows=40,530 width=0) (actual time=19.434..19.434 rows=43,520 loops=1)

  • Index Cond: ((dailysummary.summarydate >= '2019-08-01'::date) AND (dailysummary.summarydate <= '2019-08-24'::date))
  • Buffers: shared hit=1 read=146
12.          

CTE changes

13. 186.368 7,599.301 ↑ 1,146.8 536 1

GroupAggregate (cost=671,998.17..847,178.27 rows=614,667 width=48) (actual time=7,413.171..7,599.301 rows=536 loops=1)

  • Output: vp.vehicle_id, ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)::date), sum(date_part('epoch'::text, (CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)) ELSE (((d_2.d)::date + 1))::timestamp without time zone END - CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END))), count(*), sum(CASE WHEN (p.headquarter IS TRUE) THEN 1 ELSE 0 END), sum(CASE WHEN (p.headquarter IS TRUE) THEN 0 ELSE 1 END), sum(CASE WHEN (p.headquarter IS TRUE) THEN date_part('epoch'::text, (CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)) ELSE (((d_2.d)::date + 1))::timestamp without time zone END - CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)) ELSE '0'::double precision END), sum(CASE WHEN (p.headquarter IS FALSE) THEN date_part('epoch'::text, (CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)) ELSE (((d_2.d)::date + 1))::timestamp without time zone END - CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)) ELSE '0'::double precision END)
  • Group Key: ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)::date), vp.vehicle_id
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
14. 7.485 7,412.933 ↑ 165.4 3,717 1

Sort (cost=671,998.17..673,534.84 rows=614,667 width=48) (actual time=7,411.908..7,412.933 rows=3,717 loops=1)

  • Output: vp.vehicle_id, ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)::date), vo.timezone, vp.leavetime, d_2.d, vp.entertime, p.headquarter
  • Sort Key: ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)::date), vp.vehicle_id
  • Sort Method: quicksort Memory: 619kB
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
15. 64.177 7,405.448 ↑ 165.4 3,717 1

Nested Loop (cost=1,306.84..593,989.14 rows=614,667 width=48) (actual time=1,845.611..7,405.448 rows=3,717 loops=1)

  • Output: vp.vehicle_id, (CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END)::date, vo.timezone, vp.leavetime, d_2.d, vp.entertime, p.headquarter
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
16. 9.892 7,089.407 ↑ 1.7 3,314 1

Nested Loop Left Join (cost=1,306.81..82,285.25 rows=5,532 width=40) (actual time=1,844.845..7,089.407 rows=3,314 loops=1)

  • Output: vp.vehicle_id, vp.entertime, vp.leavetime, vo.timezone, p.headquarter
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
17. 200.904 7,023.177 ↑ 1.7 3,314 1

Hash Join (cost=1,306.39..75,876.08 rows=5,532 width=47) (actual time=1,844.346..7,023.177 rows=3,314 loops=1)

  • Output: vp.vehicle_id, vp.entertime, vp.leavetime, vp.poiid, vo.timezone
  • Hash Cond: (vp.owner_id = vo.id)
  • Join Filter: (((date_part('epoch'::text, timezone((vo.timezone)::text, ('2019-08-01'::date)::timestamp with time zone)) * '1000'::double precision) <= (vp.leavetime)::double precision) AND ((date_part('epoch'::text, (timezone((vo.timezone)::text, ('2019-08-24'::date)::timestamp with time zone) + '1 day'::interval)) * '1000'::double precision) >= (vp.entertime)::double precision))
  • Rows Removed by Join Filter: 32290
  • Buffers: shared hit=86 read=15730 dirtied=1 written=1
18. 6,748.243 6,821.968 ↑ 1.4 35,604 1

Bitmap Heap Scan on public.vehiclepoichange vp (cost=1,291.73..73,558.59 rows=49,791 width=40) (actual time=77.880..6,821.968 rows=35,604 loops=1)

  • Output: vp.id, vp.duration, vp.entertime, vp.ignitiontime, vp.leavetime, vp.owner_id, vp.poiid, vp.stoppedtime, vp.vehicle_id, vp.driverid
  • Recheck Cond: (vp.vehicle_id = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[]))
  • Heap Blocks: exact=15585
  • Buffers: shared hit=76 read=15730 dirtied=1 written=1
19. 73.725 73.725 ↑ 1.4 35,604 1

Bitmap Index Scan on vehiclepoichange_vehicle_idx (cost=0.00..1,279.28 rows=49,791 width=0) (actual time=73.725..73.725 rows=35,604 loops=1)

  • Index Cond: (vp.vehicle_id = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[]))
  • Buffers: shared hit=71 read=150
20. 0.048 0.305 ↑ 1.0 207 1

Hash (cost=12.07..12.07 rows=207 width=23) (actual time=0.305..0.305 rows=207 loops=1)

  • Output: vo.timezone, vo.id
  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=10
21. 0.257 0.257 ↑ 1.0 207 1

Seq Scan on public.vehicleowner vo (cost=0.00..12.07 rows=207 width=23) (actual time=0.007..0.257 rows=207 loops=1)

  • Output: vo.timezone, vo.id
  • Buffers: shared hit=10
22. 56.338 56.338 ↑ 1.0 1 3,314

Index Scan using poi_pkey on public.poi p (cost=0.42..1.15 rows=1 width=9) (actual time=0.015..0.017 rows=1 loops=3,314)

  • Output: p.id, p.active, p.address, p.category, p.color, p.confirmed, p.location, p.name, p.owner_id, p.phone, p.shape, p.stoptime, p.poigroup_id, p.otherinfo, p.code, p.workorderevent_id, p.residuesids, p.customid, p.headquarter
  • Index Cond: (vp.poiid = p.id)
  • Buffers: shared hit=13200 read=56
23. 251.864 251.864 ↑ 111.0 1 3,314

Function Scan on pg_catalog.generate_series d_2 (cost=0.03..87.53 rows=111 width=8) (actual time=0.073..0.076 rows=1 loops=3,314)

  • Output: d_2.d
  • Function Call: generate_series(((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date)::timestamp with time zone, ((timezone((vo.timezone)::text, to_timestamp(((vp.leavetime / 1000))::double precision)))::date)::timestamp with time zone, '1 day'::interval)
  • Filter: ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END >= '2019-08-01'::date) AND (CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d_2.d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d_2.d)::date)::timestamp without time zone END < (timezone((vo.timezone)::text, ('2019-08-24'::date)::timestamp with time zone) + '1 day'::interval)))
  • Rows Removed by Filter: 0
24.          

CTE tracksummaries

25. 8.545 1,426.859 ↓ 2.5 544 1

HashAggregate (cost=14,341.45..14,343.65 rows=220 width=12) (actual time=1,426.656..1,426.859 rows=544 loops=1)

  • Output: tracksummary.summarydate, tracksummary.vehicleid, count(*)
  • Group Key: tracksummary.summarydate, tracksummary.vehicleid
  • Buffers: shared hit=71 read=2874
26. 923.671 1,418.314 ↓ 1.7 3,800 1

Bitmap Heap Scan on public.tracksummary (cost=6,451.29..14,324.98 rows=2,196 width=12) (actual time=495.541..1,418.314 rows=3,800 loops=1)

  • Output: tracksummary.id, tracksummary.ownerid, tracksummary.summarydate, tracksummary.vehiclecode, tracksummary.vehicleid, tracksummary.distance, tracksummary.finalodometer, tracksummary.meanspeed, tracksummary.trackend, tracksummary.endtz, tracksummary.trackpath, tracksummary.trackstart, tracksummary.starttz
  • Recheck Cond: ((tracksummary.vehicleid = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[])) AND (tracksummary.summarydate >= '2019-08-01'::date) AND (tracksummary.summarydate <= '2019-08-24'::date))
  • Heap Blocks: exact=2013
  • Buffers: shared hit=71 read=2874
27. 5.838 494.643 ↓ 0.0 0 1

BitmapAnd (cost=6,451.29..6,451.29 rows=2,196 width=0) (actual time=494.643..494.643 rows=0 loops=1)

  • Buffers: shared hit=71 read=861
28. 140.720 140.720 ↓ 1.2 58,172 1

Bitmap Index Scan on tracksummary_vehicle_trackstart_idx (cost=0.00..1,266.24 rows=48,060 width=0) (actual time=140.720..140.720 rows=58,172 loops=1)

  • Index Cond: (tracksummary.vehicleid = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[]))
  • Buffers: shared hit=70 read=263
29. 348.085 348.085 ↓ 1.1 169,641 1

Bitmap Index Scan on tracksummarysummarydateidx (cost=0.00..5,183.70 rows=159,527 width=0) (actual time=348.085..348.085 rows=169,641 loops=1)

  • Index Cond: ((tracksummary.summarydate >= '2019-08-01'::date) AND (tracksummary.summarydate <= '2019-08-24'::date))
  • Buffers: shared hit=1 read=598
30.          

CTE notifications

31. 6.525 1,598.329 ↑ 2.7 484 1

HashAggregate (cost=14,168.46..14,185.01 rows=1,324 width=16) (actual time=1,598.180..1,598.329 rows=484 loops=1)

  • Output: vs.vehicle_id, ((vs.eventdate)::date), count(*)
  • Group Key: (vs.eventdate)::date, vs.vehicle_id
  • Buffers: shared hit=83 read=3260 dirtied=1
32. 33.518 1,591.804 ↓ 2.4 3,132 1

Hash Join (cost=197.09..14,158.53 rows=1,324 width=16) (actual time=34.495..1,591.804 rows=3,132 loops=1)

  • Output: vs.vehicle_id, (vs.eventdate)::date
  • Hash Cond: (vs.owner_id = vo_1.id)
  • Join Filter: (vs.eventdate <= (timezone((vo_1.timezone)::text, ('2019-08-24'::date)::timestamp with time zone) + '1 day'::interval))
  • Rows Removed by Join Filter: 1304
  • Buffers: shared hit=83 read=3260 dirtied=1
33. 1,525.340 1,558.115 ↓ 1.1 4,436 1

Bitmap Heap Scan on public.vehiclestatechange vs (cost=182.43..14,046.26 rows=3,971 width=24) (actual time=33.929..1,558.115 rows=4,436 loops=1)

  • Output: vs.id, vs.eventdate, vs.eventtz, vs.location, vs.owner_id, vs.vehicle_id, vs.address, vs.creationtime, vs.description, vs.duration, vs.fenceid, vs.newstate, vs.newvalue, vs.property, vs.rule_id, vs.ruleslist, vs.driver_id, vs.keyid
  • Recheck Cond: ((vs.vehicle_id = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[])) AND (vs.eventdate >= '2019-08-01'::date))
  • Heap Blocks: exact=3216
  • Buffers: shared hit=73 read=3260 dirtied=1
34. 32.775 32.775 ↓ 1.1 4,436 1

Bitmap Index Scan on vsctestindex (cost=0.00..181.44 rows=3,971 width=0) (actual time=32.775..32.775 rows=4,436 loops=1)

  • Index Cond: ((vs.vehicle_id = ANY ('{45356812,45357299,46359965,9020362,46369195,46360298,46369493,52395005,7920124,46235590,46360621,52403040,8053160,8762238,8762739,8762828,8749514,8749178,8762991,8762822,8758867,8761383,8758882,8758781,8759477,8749247,8969621,8969321,9002743,8969412,9002111,8992101}'::bigint[])) AND (vs.eventdate >= '2019-08-01'::date))
  • Buffers: shared hit=67 read=50
35. 0.050 0.171 ↑ 1.0 207 1

Hash (cost=12.07..12.07 rows=207 width=23) (actual time=0.171..0.171 rows=207 loops=1)

  • Output: vo_1.id, vo_1.timezone
  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=10
36. 0.121 0.121 ↑ 1.0 207 1

Seq Scan on public.vehicleowner vo_1 (cost=0.00..12.07 rows=207 width=23) (actual time=0.009..0.121 rows=207 loops=1)

  • Output: vo_1.id, vo_1.timezone
  • Buffers: shared hit=10
37. 171.085 17,143.050 ↑ 218.8 720 1

Merge Join (cost=1,175,143.20..1,192,630.08 rows=157,545 width=901) (actual time=17,076.730..17,143.050 rows=720 loops=1)

  • Output: ds.id, ds.ownerid, ds.summarydate, ds.vehiclecode, ds.vehicleid, ds.disconnectedtime, ds.distance, ds.endodometer, ds.endoperation, ds.firstpoint, ds.gpsfixcount, ds.ignitionoff, ds.ignitionon, ds.lastpoint, ds.maxspeed, ds.meanspeed, ds.movingtime, ds.paniccount, ds.pointcount, ds.startodometer, ds.startoperation, ds.stoppedandignition, ds.stoppedtime, ds.avgoffinterval, ds.avgoninterval, ds.gpserrorcount, ds.gpsfixignon, ds.ignitionoffcount, ds.ignitiononcount, ds.maxpointinterval, ds.offdistance, ds.powerfailcount, ds.speedrange_1, ds.speedrange_2, ds.speedrange_3, ds.speedrange_4, ds.speedrange_5, ds.plannedtrips, ds.plannedstops, ds.executedstops, ds.executedtrip, ds.quickstops, ds.mediumstops, ds.longstops, agg."tripCount", agg."changeCount", agg."notificationCount", ds.input1, ds.input2, agg."headCount", agg."notHeadCount", agg."headDuration", agg."notHeadDuration", ds.ctid, agg.*
  • Merge Cond: ((ds.summarydate = agg."summaryDate") AND (ds.vehicleid = agg."vehicleId"))
  • Buffers: shared hit=17779 read=57585 dirtied=3 written=1, temp read=57640 written=58276
38. 5,136.449 6,223.919 ↑ 1.0 1,072,712 1

Sort (cost=400,991.88..403,737.56 rows=1,098,273 width=229) (actual time=5,222.690..6,223.919 rows=1,072,712 loops=1)

  • Output: ds.id, ds.ownerid, ds.summarydate, ds.vehiclecode, ds.vehicleid, ds.disconnectedtime, ds.distance, ds.endodometer, ds.endoperation, ds.firstpoint, ds.gpsfixcount, ds.ignitionoff, ds.ignitionon, ds.lastpoint, ds.maxspeed, ds.meanspeed, ds.movingtime, ds.paniccount, ds.pointcount, ds.startodometer, ds.startoperation, ds.stoppedandignition, ds.stoppedtime, ds.avgoffinterval, ds.avgoninterval, ds.gpserrorcount, ds.gpsfixignon, ds.ignitionoffcount, ds.ignitiononcount, ds.maxpointinterval, ds.offdistance, ds.powerfailcount, ds.speedrange_1, ds.speedrange_2, ds.speedrange_3, ds.speedrange_4, ds.speedrange_5, ds.plannedtrips, ds.plannedstops, ds.executedstops, ds.executedtrip, ds.quickstops, ds.mediumstops, ds.longstops, ds.input1, ds.input2, ds.ctid
  • Sort Key: ds.summarydate, ds.vehicleid
  • Sort Method: external merge Disk: 276488kB
  • Buffers: shared hit=4151 read=35410 dirtied=1, temp read=57640 written=58276
39. 1,087.470 1,087.470 ↑ 1.0 1,094,311 1

Seq Scan on public.dailysummary ds (cost=0.00..50,543.73 rows=1,098,273 width=229) (actual time=0.014..1,087.470 rows=1,094,311 loops=1)

  • Output: ds.id, ds.ownerid, ds.summarydate, ds.vehiclecode, ds.vehicleid, ds.disconnectedtime, ds.distance, ds.endodometer, ds.endoperation, ds.firstpoint, ds.gpsfixcount, ds.ignitionoff, ds.ignitionon, ds.lastpoint, ds.maxspeed, ds.meanspeed, ds.movingtime, ds.paniccount, ds.pointcount, ds.startodometer, ds.startoperation, ds.stoppedandignition, ds.stoppedtime, ds.avgoffinterval, ds.avgoninterval, ds.gpserrorcount, ds.gpsfixignon, ds.ignitionoffcount, ds.ignitiononcount, ds.maxpointinterval, ds.offdistance, ds.powerfailcount, ds.speedrange_1, ds.speedrange_2, ds.speedrange_3, ds.speedrange_4, ds.speedrange_5, ds.plannedtrips, ds.plannedstops, ds.executedstops, ds.executedtrip, ds.quickstops, ds.mediumstops, ds.longstops, ds.input1, ds.input2, ds.ctid
  • Buffers: shared hit=4151 read=35410 dirtied=1
40. 0.353 10,748.046 ↑ 640.3 768 1

Materialize (cost=774,151.32..776,609.99 rows=491,734 width=684) (actual time=10,747.464..10,748.046 rows=768 loops=1)

  • Output: agg."tripCount", agg."changeCount", agg."notificationCount", agg."headCount", agg."notHeadCount", agg."headDuration", agg."notHeadDuration", agg.*, agg."summaryDate", agg."vehicleId
  • Buffers: shared hit=13628 read=22175 dirtied=2 written=1
41. 1.355 10,747.693 ↑ 640.3 768 1

Sort (cost=774,151.32..775,380.66 rows=491,734 width=684) (actual time=10,747.443..10,747.693 rows=768 loops=1)

  • Output: agg."tripCount", agg."changeCount", agg."notificationCount", agg."headCount", agg."notHeadCount", agg."headDuration", agg."notHeadDuration", agg.*, agg."summaryDate", agg."vehicleId
  • Sort Key: agg."summaryDate", agg."vehicleId
  • Sort Method: quicksort Memory: 200kB
  • Buffers: shared hit=13628 read=22175 dirtied=2 written=1
42. 0.284 10,746.338 ↑ 640.3 768 1

Subquery Scan on agg (cost=422,344.30..428,490.97 rows=491,734 width=684) (actual time=10,746.032..10,746.338 rows=768 loops=1)

  • Output: agg."tripCount", agg."changeCount", agg."notificationCount", agg."headCount", agg."notHeadCount", agg."headDuration", agg."notHeadDuration", agg.*, agg."summaryDate", agg."vehicleId
  • Buffers: shared hit=13628 read=22175 dirtied=2 written=1
43. 1.298 10,746.054 ↑ 640.3 768 1

Sort (cost=422,344.30..423,573.63 rows=491,734 width=592) (actual time=10,746.000..10,746.054 rows=768 loops=1)

  • Output: v."vehicleId", v."vehicleCode", d."summaryDate", notification."notificationCount", vpc."changeCount", vpc.duration, vpc."headCount", vpc."headDuration", vpc."notHeadCount", vpc."notHeadDuration", ts.tripcount
  • Sort Key: v."vehicleCode
  • Sort Method: quicksort Memory: 118kB
  • Buffers: shared hit=13628 read=22175 dirtied=2 written=1
44. 0.425 10,744.756 ↑ 640.3 768 1

Merge Left Join (cost=105,207.21..117,017.95 rows=491,734 width=592) (actual time=10,742.853..10,744.756 rows=768 loops=1)

  • Output: v."vehicleId", v."vehicleCode", d."summaryDate", notification."notificationCount", vpc."changeCount", vpc.duration, vpc."headCount", vpc."headDuration", vpc."notHeadCount", vpc."notHeadDuration", ts.tripcount
  • Merge Cond: ((v."vehicleId" = vpc."vehicleId") AND (d."summaryDate" = vpc."changeDate"))
  • Buffers: shared hit=13625 read=22175 dirtied=2 written=1
45. 0.318 3,143.511 ↑ 41.7 768 1

Merge Left Join (cost=10,704.84..11,291.57 rows=32,000 width=544) (actual time=3,142.136..3,143.511 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode", notification."notificationCount", ts.tripcount
  • Merge Cond: ((v."vehicleId" = ts."vehicleId") AND (d."summaryDate" = ts.summarydate))
  • Buffers: shared hit=339 read=6389 dirtied=1
46. 0.294 1,715.800 ↑ 41.7 768 1

Merge Left Join (cost=10,691.88..11,115.20 rows=32,000 width=536) (actual time=1,714.811..1,715.800 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode", notification."notificationCount
  • Merge Cond: ((v."vehicleId" = notification."vehicleId") AND (d."summaryDate" = notification."eventDate"))
  • Buffers: shared hit=268 read=3515 dirtied=1
47. 0.396 115.801 ↑ 41.7 768 1

Merge Left Join (cost=10,596.75..10,839.55 rows=32,000 width=528) (actual time=115.194..115.801 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode
  • Merge Cond: ((v."vehicleId" = ds_1."vehicleId") AND (d."summaryDate" = ds_1."summaryDate"))
  • Buffers: shared hit=185 read=255
48. 0.282 0.750 ↑ 41.7 768 1

Sort (cost=10,594.67..10,674.67 rows=32,000 width=528) (actual time=0.686..0.750 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode
  • Sort Key: v."vehicleId", d."summaryDate
  • Sort Method: quicksort Memory: 85kB
  • Buffers: shared hit=81
49. 0.130 0.468 ↑ 41.7 768 1

Nested Loop (cost=0.00..650.64 rows=32,000 width=528) (actual time=0.076..0.468 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode
  • Buffers: shared hit=81
50. 0.210 0.210 ↑ 1.0 32 1

CTE Scan on vehicles v (cost=0.00..0.64 rows=32 width=524) (actual time=0.034..0.210 rows=32 loops=1)

  • Output: v."vehicleId", v."vehicleCode", v.active, v."deviceCode
  • Buffers: shared hit=81
51. 0.128 0.128 ↑ 41.7 24 32

CTE Scan on dates d (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.004 rows=24 loops=32)

  • Output: d."summaryDate
52. 1.133 114.655 ↓ 16.4 720 1

Sort (cost=2.08..2.19 rows=44 width=12) (actual time=114.502..114.655 rows=720 loops=1)

  • Output: ds_1."vehicleId", ds_1."summaryDate
  • Sort Key: ds_1."vehicleId", ds_1."summaryDate
  • Sort Method: quicksort Memory: 58kB
  • Buffers: shared hit=104 read=255
53. 113.522 113.522 ↓ 16.4 720 1

CTE Scan on dailysummaries ds_1 (cost=0.00..0.88 rows=44 width=12) (actual time=112.747..113.522 rows=720 loops=1)

  • Output: ds_1."vehicleId", ds_1."summaryDate
  • Buffers: shared hit=104 read=255
54. 0.947 1,599.705 ↑ 2.7 484 1

Sort (cost=95.13..98.44 rows=1,324 width=20) (actual time=1,599.612..1,599.705 rows=484 loops=1)

  • Output: notification."notificationCount", notification."vehicleId", notification."eventDate
  • Sort Key: notification."vehicleId", notification."eventDate
  • Sort Method: quicksort Memory: 62kB
  • Buffers: shared hit=83 read=3260 dirtied=1
55. 1,598.758 1,598.758 ↑ 2.7 484 1

CTE Scan on notifications notification (cost=0.00..26.48 rows=1,324 width=20) (actual time=1,598.186..1,598.758 rows=484 loops=1)

  • Output: notification."notificationCount", notification."vehicleId", notification."eventDate
  • Buffers: shared hit=83 read=3260 dirtied=1
56. 0.308 1,427.393 ↓ 2.5 544 1

Sort (cost=12.96..13.51 rows=220 width=20) (actual time=1,427.319..1,427.393 rows=544 loops=1)

  • Output: ts.tripcount, ts."vehicleId", ts.summarydate
  • Sort Key: ts."vehicleId", ts.summarydate
  • Sort Method: quicksort Memory: 67kB
  • Buffers: shared hit=71 read=2874
57. 1,427.085 1,427.085 ↓ 2.5 544 1

CTE Scan on tracksummaries ts (cost=0.00..4.40 rows=220 width=20) (actual time=1,426.669..1,427.085 rows=544 loops=1)

  • Output: ts.tripcount, ts."vehicleId", ts.summarydate
  • Buffers: shared hit=71 read=2874
58. 0.067 7,600.820 ↑ 1,146.8 536 1

Materialize (cost=94,502.37..97,575.70 rows=614,667 width=60) (actual time=7,600.712..7,600.820 rows=536 loops=1)

  • Output: vpc."changeCount", vpc.duration, vpc."headCount", vpc."headDuration", vpc."notHeadCount", vpc."notHeadDuration", vpc."vehicleId", vpc."changeDate
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
59. 0.748 7,600.753 ↑ 1,146.8 536 1

Sort (cost=94,502.37..96,039.04 rows=614,667 width=60) (actual time=7,600.709..7,600.753 rows=536 loops=1)

  • Output: vpc."changeCount", vpc.duration, vpc."headCount", vpc."headDuration", vpc."notHeadCount", vpc."notHeadDuration", vpc."vehicleId", vpc."changeDate
  • Sort Key: vpc."vehicleId", vpc."changeDate
  • Sort Method: quicksort Memory: 100kB
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1
60. 7,600.005 7,600.005 ↑ 1,146.8 536 1

CTE Scan on changes vpc (cost=0.00..12,293.34 rows=614,667 width=60) (actual time=7,413.175..7,600.005 rows=536 loops=1)

  • Output: vpc."changeCount", vpc.duration, vpc."headCount", vpc."headDuration", vpc."notHeadCount", vpc."notHeadDuration", vpc."vehicleId", vpc."changeDate
  • Buffers: shared hit=13286 read=15786 dirtied=1 written=1