explain.depesz.com

PostgreSQL's explain analyze made readable

Result: unsC : test

Settings
# exclusive inclusive rows x rows loops node
1. 28.004 3,852.757 ↓ 0.0 0 1

Update on public.dailysummary ds (cost=156,439.57..333,723.81 rows=10,283 width=827) (actual time=3,852.757..3,852.757 rows=0 loops=1)

  • Buffers: shared hit=17634 read=39495 dirtied=104, temp read=38180 written=37912
2.          

CTE dates

3. 0.037 0.037 ↑ 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.030..0.037 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.090 0.090 ↑ 1.0 32 1

Index Scan using vehicle_pkey on public.vehicle (cost=0.28..100.65 rows=32 width=34) (actual time=0.014..0.090 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.261 11.375 ↓ 16.4 720 1

HashAggregate (cost=2,859.33..2,859.77 rows=44 width=56) (actual time=11.024..11.375 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=485
8. 0.735 10.114 ↓ 1.6 720 1

Bitmap Heap Scan on public.dailysummary (cost=1,214.12..2,845.09 rows=438 width=56) (actual time=9.473..10.114 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=178
  • Buffers: shared hit=485
9. 1.229 9.379 ↓ 0.0 0 1

BitmapAnd (cost=1,214.12..1,214.12 rows=438 width=0) (actual time=9.379..9.379 rows=0 loops=1)

  • Buffers: shared hit=307
10. 3.541 3.541 ↓ 1.7 20,026 1

Bitmap Index Scan on dailysummaryvhcidx (cost=0.00..358.72 rows=11,863 width=0) (actual time=3.541..3.541 rows=20,026 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=158
11. 4.609 4.609 ↓ 1.2 46,772 1

Bitmap Index Scan on dailysummarysummarydateidx (cost=0.00..854.93 rows=40,650 width=0) (actual time=4.609..4.609 rows=46,772 loops=1)

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

CTE tracksummaries

13. 1.443 33.979 ↓ 2.5 544 1

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

  • Output: tracksummary.summarydate, tracksummary.vehicleid, count(*)
  • Group Key: tracksummary.summarydate, tracksummary.vehicleid
  • Buffers: shared hit=2945
14. 3.169 32.536 ↓ 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=29.726..32.536 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=2945
15. 4.959 29.367 ↓ 0.0 0 1

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

  • Buffers: shared hit=932
16. 8.132 8.132 ↓ 1.2 58,176 1

Bitmap Index Scan on tracksummary_vehicle_trackstart_idx (cost=0.00..1,266.24 rows=48,060 width=0) (actual time=8.132..8.132 rows=58,176 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=333
17. 16.276 16.276 ↓ 1.1 169,641 1

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

  • Index Cond: ((tracksummary.summarydate >= '2019-08-01'::date) AND (tracksummary.summarydate <= '2019-08-24'::date))
  • Buffers: shared hit=599
18.          

CTE notifications

19. 1.324 15.592 ↑ 2.7 484 1

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

  • Output: vs.vehicle_id, ((vs.eventdate)::date), count(*)
  • Group Key: (vs.eventdate)::date, vs.vehicle_id
  • Buffers: shared hit=3343
20. 6.668 14.268 ↓ 2.4 3,132 1

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

  • Output: vs.vehicle_id, (vs.eventdate)::date
  • Hash Cond: (vs.owner_id = vo.id)
  • Join Filter: (vs.eventdate <= (timezone((vo.timezone)::text, ('2019-08-24'::date)::timestamp with time zone) + '1 day'::interval))
  • Rows Removed by Join Filter: 1307
  • Buffers: shared hit=3343
21. 5.372 7.458 ↓ 1.1 4,439 1

Bitmap Heap Scan on public.vehiclestatechange vs (cost=182.43..14,046.26 rows=3,971 width=24) (actual time=2.572..7.458 rows=4,439 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=3219
  • Buffers: shared hit=3333
22. 2.086 2.086 ↓ 1.1 4,439 1

Bitmap Index Scan on vsctestindex (cost=0.00..181.44 rows=3,971 width=0) (actual time=2.086..2.086 rows=4,439 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=114
23. 0.043 0.142 ↑ 1.0 207 1

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

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

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

  • Output: vo.id, vo.timezone
  • Buffers: shared hit=10
25. 471.699 3,824.753 ↑ 14.3 720 1

Hash Join (cost=124,937.98..302,222.23 rows=10,283 width=827) (actual time=3,354.520..3,824.753 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", ds.poivisits, agg."notificationCount", ds.input1, ds.input2, ds.poibasevisits, ds.poinotbasevisits, ds.poibaseduration, ds.poinotbaseduration, ds.ctid, agg.*
  • Hash Cond: ((agg."summaryDate" = ds.summarydate) AND (agg."vehicleId" = ds.vehicleid))
  • Buffers: shared hit=7068 read=39464, temp read=38180 written=37912
26. 0.296 66.664 ↑ 41.7 768 1

Subquery Scan on agg (cost=21,147.96..21,547.96 rows=32,000 width=596) (actual time=66.235..66.664 rows=768 loops=1)

  • Output: agg."tripCount", agg."notificationCount", agg.*, agg."summaryDate", agg."vehicleId
  • Buffers: shared hit=6854, temp read=28 written=14
27. 0.996 66.368 ↑ 41.7 768 1

Sort (cost=21,147.96..21,227.96 rows=32,000 width=544) (actual time=66.223..66.368 rows=768 loops=1)

  • Output: v."vehicleId", v."vehicleCode", d."summaryDate", notification."notificationCount", ts.tripcount
  • Sort Key: v."vehicleCode
  • Sort Method: quicksort Memory: 85kB
  • Buffers: shared hit=6854, temp read=28 written=14
28. 0.322 65.372 ↑ 41.7 768 1

Hash Left Join (cost=3,296.88..10,986.93 rows=32,000 width=544) (actual time=62.667..65.372 rows=768 loops=1)

  • Output: v."vehicleId", v."vehicleCode", d."summaryDate", notification."notificationCount", ts.tripcount
  • Hash Cond: ((v."vehicleId" = ts."vehicleId") AND (d."summaryDate" = ts.summarydate))
  • Buffers: shared hit=6854, temp read=28 written=14
29. 0.381 30.727 ↑ 41.7 768 1

Hash Left Join (cost=3,289.18..9,057.47 rows=32,000 width=536) (actual time=28.320..30.727 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode", notification."notificationCount
  • Hash Cond: ((v."vehicleId" = ds_1."vehicleId") AND (d."summaryDate" = ds_1."summaryDate"))
  • Buffers: shared hit=3909, temp read=28 written=14
30. 1.854 18.371 ↑ 41.7 768 1

Hash Right Join (cost=3,287.64..8,814.33 rows=32,000 width=536) (actual time=16.319..18.371 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode", notification."notificationCount
  • Hash Cond: ((notification."vehicleId" = v."vehicleId") AND (notification."eventDate" = d."summaryDate"))
  • Buffers: shared hit=3424, temp read=28 written=14
31. 15.790 15.790 ↑ 2.7 484 1

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

  • Output: notification."notificationCount", notification."vehicleId", notification."eventDate
  • Buffers: shared hit=3343
32. 0.331 0.727 ↑ 41.7 768 1

Hash (cost=650.64..650.64 rows=32,000 width=528) (actual time=0.727..0.727 rows=768 loops=1)

  • Output: d."summaryDate", v."vehicleId", v."vehicleCode
  • Buckets: 8192 Batches: 8 Memory Usage: 71kB
  • Buffers: shared hit=81
33. 0.155 0.396 ↑ 41.7 768 1

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

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

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

  • Output: v."vehicleId", v."vehicleCode", v.active, v."deviceCode
  • Buffers: shared hit=81
35. 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
36. 0.157 11.975 ↓ 16.4 720 1

Hash (cost=0.88..0.88 rows=44 width=12) (actual time=11.975..11.975 rows=720 loops=1)

  • Output: ds_1."vehicleId", ds_1."summaryDate
  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
  • Buffers: shared hit=485
37. 11.818 11.818 ↓ 16.4 720 1

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

  • Output: ds_1."vehicleId", ds_1."summaryDate
  • Buffers: shared hit=485
38. 0.145 34.323 ↓ 2.5 544 1

Hash (cost=4.40..4.40 rows=220 width=20) (actual time=34.323..34.323 rows=544 loops=1)

  • Output: ts.tripcount, ts."vehicleId", ts.summarydate
  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=2945
39. 34.178 34.178 ↓ 2.5 544 1

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

  • Output: ts.tripcount, ts."vehicleId", ts.summarydate
  • Buffers: shared hit=2945
40. 1,933.426 3,286.390 ↑ 1.0 1,094,311 1

Hash (cost=50,693.21..50,693.21 rows=1,101,521 width=243) (actual time=3,286.390..3,286.390 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.poivisits, ds.input1, ds.input2, ds.poibasevisits, ds.poinotbasevisits, ds.poibaseduration, ds.poinotbaseduration, ds.ctid
  • Buckets: 16384 Batches: 128 Memory Usage: 2626kB
  • Buffers: shared hit=214 read=39464, temp written=37644
41. 1,352.964 1,352.964 ↑ 1.0 1,094,311 1

Seq Scan on public.dailysummary ds (cost=0.00..50,693.21 rows=1,101,521 width=243) (actual time=0.106..1,352.964 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.poivisits, ds.input1, ds.input2, ds.poibasevisits, ds.poinotbasevisits, ds.poibaseduration, ds.poinotbaseduration, ds.ctid
  • Buffers: shared hit=214 read=39464