explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BbVC : Test

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Update on dailysummary ds (cost=2,053,314.24..2,070,785.84 rows=157,353 width=901) (actual rows= loops=)

2.          

CTE dates

3. 0.000 0.000 ↓ 0.0

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

4.          

CTE vehicles

5. 0.000 0.000 ↓ 0.0

Index Scan using vehicle_pkey on vehicle (cost=0.28..100.65 rows=32 width=34) (actual rows= loops=)

  • Index Cond: (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[]))
6.          

CTE dailysummaries

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,849.64..2,850.08 rows=44 width=56) (actual rows= loops=)

  • Group Key: dailysummary.summarydate, dailysummary.vehicleid
8. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on dailysummary (cost=1,211.95..2,835.47 rows=436 width=56) (actual rows= loops=)

  • Recheck Cond: ((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 (summarydate >= '2019-08-01'::date) AND (summarydate <= '2019-08-24'::date))
9. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=1,211.95..1,211.95 rows=436 width=0) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on dailysummaryvhcidx (cost=0.00..358.24 rows=11,814 width=0) (actual rows= loops=)

  • Index Cond: (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[]))
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on dailysummarysummarydateidx (cost=0.00..853.24 rows=40,481 width=0) (actual rows= loops=)

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

CTE changes

13. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=671,997.73..847,177.82 rows=614,667 width=48) (actual rows= loops=)

  • 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
14. 0.000 0.000 ↓ 0.0

Sort (cost=671,997.73..673,534.39 rows=614,667 width=48) (actual rows= loops=)

  • 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
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,306.84..593,988.70 rows=614,667 width=48) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,306.81..82,284.80 rows=5,532 width=40) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,306.39..75,876.08 rows=5,532 width=47) (actual rows= loops=)

  • 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))
18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on vehiclepoichange vp (cost=1,291.73..73,558.59 rows=49,791 width=40) (actual rows= loops=)

  • Recheck 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[]))
19. 0.000 0.000 ↓ 0.0

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

  • 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[]))
20. 0.000 0.000 ↓ 0.0

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
21. 0.000 0.000 ↓ 0.0

Seq Scan on vehicleowner vo (cost=0.00..12.07 rows=207 width=23) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using poi_pkey on poi p (cost=0.42..1.15 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (vp.poiid = id)
23. 0.000 0.000 ↓ 0.0

Function Scan on generate_series d_2 (cost=0.03..87.53 rows=111 width=8) (actual rows= loops=)

  • Filter: ((CASE WHEN ((timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)))::date = (d)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((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)::date) THEN timezone((vo.timezone)::text, to_timestamp(((vp.entertime / 1000))::double precision)) ELSE ((d)::date)::timestamp without time zone END < (timezone((vo.timezone)::text, ('2019-08-24'::date)::timestamp with time zone) + '1 day'::interval)))
24.          

CTE tracksummaries

25. 0.000 0.000 ↓ 0.0

HashAggregate (cost=14,341.45..14,343.65 rows=220 width=12) (actual rows= loops=)

  • Group Key: tracksummary.summarydate, tracksummary.vehicleid
26. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tracksummary (cost=6,451.29..14,324.98 rows=2,196 width=12) (actual rows= loops=)

  • Recheck Cond: ((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 (summarydate >= '2019-08-01'::date) AND (summarydate <= '2019-08-24'::date))
27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (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[]))
29. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((summarydate >= '2019-08-01'::date) AND (summarydate <= '2019-08-24'::date))
30.          

CTE notifications

31. 0.000 0.000 ↓ 0.0

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

  • Group Key: (vs.eventdate)::date, vs.vehicle_id
32. 0.000 0.000 ↓ 0.0

Hash Join (cost=197.09..14,158.53 rows=1,324 width=16) (actual rows= loops=)

  • 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))
33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on vehiclestatechange vs (cost=182.43..14,046.26 rows=3,971 width=24) (actual rows= loops=)

  • Recheck 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[])) AND (eventdate >= '2019-08-01'::date))
34. 0.000 0.000 ↓ 0.0

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

  • 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[])) AND (eventdate >= '2019-08-01'::date))
35. 0.000 0.000 ↓ 0.0

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
36. 0.000 0.000 ↓ 0.0

Seq Scan on vehicleowner vo_1 (cost=0.00..12.07 rows=207 width=23) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,174,644.52..1,192,116.12 rows=157,353 width=901) (actual rows= loops=)

  • Merge Cond: ((ds.summarydate = agg."summaryDate") AND (ds.vehicleid = agg."vehicleId"))
38. 0.000 0.000 ↓ 0.0

Sort (cost=400,493.20..403,235.55 rows=1,096,940 width=229) (actual rows= loops=)

  • Sort Key: ds.summarydate, ds.vehicleid
39. 0.000 0.000 ↓ 0.0

Seq Scan on dailysummary ds (cost=0.00..50,482.40 rows=1,096,940 width=229) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Materialize (cost=774,151.32..776,609.99 rows=491,734 width=684) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=774,151.32..775,380.66 rows=491,734 width=684) (actual rows= loops=)

  • Sort Key: agg."summaryDate", agg."vehicleId
42. 0.000 0.000 ↓ 0.0

Subquery Scan on agg (cost=422,344.30..428,490.97 rows=491,734 width=684) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Sort (cost=422,344.30..423,573.63 rows=491,734 width=592) (actual rows= loops=)

  • Sort Key: v."vehicleCode
44. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=105,207.21..117,017.95 rows=491,734 width=592) (actual rows= loops=)

  • Merge Cond: ((v."vehicleId" = vpc."vehicleId") AND (d."summaryDate" = vpc."changeDate"))
45. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,704.84..11,291.57 rows=32,000 width=544) (actual rows= loops=)

  • Merge Cond: ((v."vehicleId" = ts."vehicleId") AND (d."summaryDate" = ts.summarydate))
46. 0.000 0.000 ↓ 0.0

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

  • Merge Cond: ((v."vehicleId" = notification."vehicleId") AND (d."summaryDate" = notification."eventDate"))
47. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,596.75..10,839.55 rows=32,000 width=528) (actual rows= loops=)

  • Merge Cond: ((v."vehicleId" = ds_1."vehicleId") AND (d."summaryDate" = ds_1."summaryDate"))
48. 0.000 0.000 ↓ 0.0

Sort (cost=10,594.67..10,674.67 rows=32,000 width=528) (actual rows= loops=)

  • Sort Key: v."vehicleId", d."summaryDate
49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..650.64 rows=32,000 width=528) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

Sort (cost=2.08..2.19 rows=44 width=12) (actual rows= loops=)

  • Sort Key: ds_1."vehicleId", ds_1."summaryDate
53. 0.000 0.000 ↓ 0.0

CTE Scan on dailysummaries ds_1 (cost=0.00..0.88 rows=44 width=12) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

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

  • Sort Key: notification."vehicleId", notification."eventDate
55. 0.000 0.000 ↓ 0.0

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

56. 0.000 0.000 ↓ 0.0

Sort (cost=12.96..13.51 rows=220 width=20) (actual rows= loops=)

  • Sort Key: ts."vehicleId", ts.summarydate
57. 0.000 0.000 ↓ 0.0

CTE Scan on tracksummaries ts (cost=0.00..4.40 rows=220 width=20) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Materialize (cost=94,502.37..97,575.70 rows=614,667 width=60) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=94,502.37..96,039.04 rows=614,667 width=60) (actual rows= loops=)

  • Sort Key: vpc."vehicleId", vpc."changeDate
60. 0.000 0.000 ↓ 0.0

CTE Scan on changes vpc (cost=0.00..12,293.34 rows=614,667 width=60) (actual rows= loops=)