explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M2OE

Settings
# exclusive inclusive rows x rows loops node
1. 1.272 19,369.128 ↓ 92.0 92 1

Group (cost=502,253.83..502,266.41 rows=1 width=474) (actual time=19,366.816..19,369.128 rows=92 loops=1)

  • Group Key: occ."recordDate_hour_1", occ."spaceId", occ.name, occ."maxOccupancy", occ.sumins, occ.sumouts, occ."recordError", occ."minOccupancy", occ."avgOccupancy", occ."sumOccupancy", occ."locationId
2.          

CTE occ

3. 3.181 19,366.630 ↓ 92.0 92 1

GroupAggregate (cost=502,253.74..502,253.80 rows=1 width=279) (actual time=19,363.123..19,366.630 rows=92 loops=1)

  • Group Key: (timezone('America/New_York'::text, date_trunc('hour'::text, timezone('UTC'::text, occupancy."recordDate")))), space_5."spaceId", location."locationId", occupancy."recordError
4. 2.959 19,363.449 ↓ 5,321.0 5,321 1

Sort (cost=502,253.74..502,253.74 rows=1 width=183) (actual time=19,363.066..19,363.449 rows=5,321 loops=1)

  • Sort Key: (timezone('America/New_York'::text, date_trunc('hour'::text, timezone('UTC'::text, occupancy."recordDate")))), space_5."spaceId", location."locationId", occupancy."recordError
  • Sort Method: quicksort Memory: 941kB
5. 3.543 19,360.490 ↓ 5,321.0 5,321 1

Nested Loop (cost=502,245.31..502,253.73 rows=1 width=183) (actual time=19,350.821..19,360.490 rows=5,321 loops=1)

6. 0.842 19,351.626 ↓ 5,321.0 5,321 1

Hash Join (cost=502,245.03..502,249.42 rows=1 width=183) (actual time=19,350.783..19,351.626 rows=5,321 loops=1)

  • Hash Cond: (space_5."spaceId" = occupancy."spaceId")
7. 0.030 0.030 ↓ 1.1 105 1

Seq Scan on space space_5 (cost=0.00..4.00 rows=100 width=31) (actual time=0.006..0.030 rows=105 loops=1)

8. 1.335 19,350.754 ↓ 5,321.0 5,321 1

Hash (cost=502,245.02..502,245.02 rows=1 width=168) (actual time=19,350.754..19,350.754 rows=5,321 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 504kB
9. 1.327 19,349.419 ↓ 5,321.0 5,321 1

Subquery Scan on occupancy (cost=502,235.72..502,245.02 rows=1 width=168) (actual time=19,347.593..19,349.419 rows=5,321 loops=1)

  • Filter: ((occupancy."recordDate" >= '2018-11-29 00:00:00+00'::timestamp with time zone) AND (occupancy."recordDate" < '2018-11-30 00:00:00+00'::timestamp with time zone) AND (occupancy."orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid))
10. 8.755 19,348.092 ↓ 11.4 5,321 1

Sort (cost=502,235.72..502,236.88 rows=465 width=192) (actual time=19,347.584..19,348.092 rows=5,321 loops=1)

  • Sort Key: entity_occupancy."spaceId", entity_occupancy."recordDate
  • Sort Method: quicksort Memory: 941kB
11.          

CTE entity_occupancy

12. 5.746 19,329.097 ↓ 2.3 10,968 1

Sort (cost=502,057.07..502,068.90 rows=4,734 width=216) (actual time=19,328.069..19,329.097 rows=10,968 loops=1)

  • Sort Key: (date_trunc('minute'::text, traffic."recordDate")), traffic."nextRecordDate
  • Sort Method: quicksort Memory: 1927kB
13.          

CTE reset_validity

14. 0.007 1.349 ↑ 6.8 4 1

GroupAggregate (cost=281.12..282.06 rows=27 width=48) (actual time=1.348..1.349 rows=4 loops=1)

  • Group Key: sp."spaceId
15. 0.007 1.342 ↑ 6.8 4 1

Sort (cost=281.12..281.18 rows=27 width=80) (actual time=1.342..1.342 rows=4 loops=1)

  • Sort Key: sp."spaceId
  • Sort Method: quicksort Memory: 25kB
16. 0.066 1.335 ↑ 6.8 4 1

Hash Semi Join (cost=246.35..280.47 rows=27 width=80) (actual time=1.316..1.335 rows=4 loops=1)

  • Hash Cond: (sp."spaceId" = space."spaceId")
17. 0.158 1.247 ↓ 1.1 747 1

Hash Right Join (cost=242.05..274.12 rows=668 width=80) (actual time=0.977..1.247 rows=747 loops=1)

  • Hash Cond: (occupancy_reset."spaceId" = sp."spaceId")
18. 0.121 0.121 ↑ 1.0 664 1

Seq Scan on occupancy_reset (cost=0.00..22.89 rows=668 width=32) (actual time=0.004..0.121 rows=664 loops=1)

  • Filter: ("resetDate" < '2018-11-29 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 115
19. 0.021 0.968 ↓ 1.1 107 1

Hash (cost=240.80..240.80 rows=100 width=64) (actual time=0.968..0.968 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
20. 0.032 0.947 ↓ 1.1 107 1

Nested Loop Left Join (cost=109.86..240.80 rows=100 width=64) (actual time=0.583..0.947 rows=107 loops=1)

21. 0.020 0.705 ↓ 1.1 105 1

Hash Left Join (cost=109.58..129.61 rows=100 width=64) (actual time=0.571..0.705 rows=105 loops=1)

  • Hash Cond: (sp."spaceId" = swhr."spaceId")
22. 0.021 0.602 ↓ 1.1 105 1

Hash Left Join (cost=104.08..122.74 rows=100 width=48) (actual time=0.482..0.602 rows=105 loops=1)

  • Hash Cond: (sp."spaceId" = swtr."spaceId")
23. 0.074 0.471 ↓ 1.1 105 1

Hash Right Join (cost=98.58..115.86 rows=100 width=32) (actual time=0.369..0.471 rows=105 loops=1)

  • Hash Cond: (sch."scheduleId" = lsa."scheduleId")
24. 0.044 0.044 ↓ 1.0 504 1

Seq Scan on schedule sch (cost=0.00..11.95 rows=495 width=16) (actual time=0.003..0.044 rows=504 loops=1)

25. 0.015 0.353 ↓ 1.1 105 1

Hash (cost=97.33..97.33 rows=100 width=32) (actual time=0.353..0.353 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
26. 0.056 0.338 ↓ 1.1 105 1

Hash Right Join (cost=79.75..97.33 rows=100 width=32) (actual time=0.208..0.338 rows=105 loops=1)

  • Hash Cond: (lsa."locationId" = l."locationId")
27. 0.096 0.096 ↓ 1.0 446 1

Seq Scan on location_schedule_assignment lsa (cost=0.00..15.71 rows=440 width=32) (actual time=0.006..0.096 rows=446 loops=1)

  • Filter: (("startDate" <= '2018-11-28 00:00:00'::timestamp without time zone) AND ("endDate" >= '2018-11-29'::date))
  • Rows Removed by Filter: 72
28. 0.020 0.186 ↓ 1.1 105 1

Hash (cost=78.50..78.50 rows=100 width=32) (actual time=0.186..0.186 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
29. 0.047 0.166 ↓ 1.1 105 1

Nested Loop Left Join (cost=0.28..78.50 rows=100 width=32) (actual time=0.010..0.166 rows=105 loops=1)

30. 0.014 0.014 ↓ 1.1 105 1

Seq Scan on space sp (cost=0.00..4.00 rows=100 width=32) (actual time=0.003..0.014 rows=105 loops=1)

31. 0.105 0.105 ↑ 1.0 1 105

Index Only Scan using location_pkey on location l (cost=0.28..0.74 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=105)

  • Index Cond: ("locationId" = sp."locationId")
  • Heap Fetches: 0
32. 0.016 0.110 ↑ 1.1 88 1

Hash (cost=4.25..4.25 rows=100 width=16) (actual time=0.110..0.110 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
33. 0.094 0.094 ↑ 1.1 88 1

Seq Scan on space swtr (cost=0.00..4.25 rows=100 width=16) (actual time=0.012..0.094 rows=88 loops=1)

  • Filter: (("resetConfig" -> 'resetTime'::text) IS NOT NULL)
  • Rows Removed by Filter: 17
34. 0.002 0.083 ↑ 20.0 5 1

Hash (cost=4.25..4.25 rows=100 width=16) (actual time=0.083..0.083 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.081 0.081 ↑ 20.0 5 1

Seq Scan on space swhr (cost=0.00..4.25 rows=100 width=16) (actual time=0.015..0.081 rows=5 loops=1)

  • Filter: (("resetConfig" -> 'resetWithHoursOfOp'::text) IS NOT NULL)
  • Rows Removed by Filter: 100
36. 0.210 0.210 ↓ 0.0 0 105

Index Scan using "schedule_period_scheduleId_idx" on schedule_period schp (cost=0.28..1.10 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=105)

  • Index Cond: ("scheduleId" = sch."scheduleId")
  • Filter: ((weekday)::double precision = '3'::double precision)
  • Rows Removed by Filter: 2
37. 0.006 0.022 ↑ 1.0 4 1

Hash (cost=4.25..4.25 rows=4 width=16) (actual time=0.022..0.022 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on space (cost=0.00..4.25 rows=4 width=16) (actual time=0.007..0.016 rows=4 loops=1)

  • Filter: ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid)
  • Rows Removed by Filter: 101
39.          

CTE resets

40. 0.138 0.761 ↑ 18.3 11 1

Result (cost=398.15..401.17 rows=201 width=48) (actual time=0.658..0.761 rows=11 loops=1)

41. 0.012 0.623 ↑ 18.3 11 1

Sort (cost=398.15..398.65 rows=201 width=32) (actual time=0.622..0.623 rows=11 loops=1)

  • Sort Key: occr."resetDate", occr."spaceId
  • Sort Method: quicksort Memory: 25kB
42. 0.022 0.611 ↑ 18.3 11 1

HashAggregate (cost=388.45..390.46 rows=201 width=32) (actual time=0.591..0.611 rows=11 loops=1)

  • Group Key: occr."resetDate", occr."spaceId
43. 0.010 0.589 ↑ 182.7 11 1

HashAggregate (cost=333.18..353.28 rows=2,010 width=44) (actual time=0.584..0.589 rows=11 loops=1)

  • Group Key: occr."occupancyResetId", occr."spaceId", occr."resetDate", occr.occupants
44. 0.001 0.579 ↑ 182.7 11 1

Append (cost=36.38..313.08 rows=2,010 width=44) (actual time=0.225..0.579 rows=11 loops=1)

45. 0.000 0.071 ↓ 0.0 0 1

Hash Left Join (cost=36.38..46.97 rows=10 width=44) (actual time=0.071..0.071 rows=0 loops=1)

  • Hash Cond: (occr."spaceId" = occupancy_reset_1."spaceId")
  • Filter: ((occr."resetDate" >= (max(occupancy_reset_1."resetDate"))) OR ((max(occupancy_reset_1."resetDate")) IS NULL))
46. 0.002 0.071 ↓ 0.0 0 1

Nested Loop (cost=9.44..19.54 rows=30 width=44) (actual time=0.071..0.071 rows=0 loops=1)

47. 0.004 0.061 ↓ 4.0 4 1

HashAggregate (cost=4.85..4.86 rows=1 width=32) (actual time=0.060..0.061 rows=4 loops=1)

  • Group Key: space_1."spaceId
48. 0.005 0.057 ↓ 4.0 4 1

Nested Loop (cost=0.54..4.85 rows=1 width=32) (actual time=0.016..0.057 rows=4 loops=1)

  • Join Filter: (space_1."spaceId" = reset_validity."spaceId")
  • Rows Removed by Join Filter: 12
49. 0.003 0.004 ↓ 4.0 4 1

HashAggregate (cost=0.54..0.55 rows=1 width=16) (actual time=0.004..0.004 rows=4 loops=1)

  • Group Key: reset_validity."spaceId
50. 0.001 0.001 ↓ 4.0 4 1

CTE Scan on reset_validity (cost=0.00..0.54 rows=1 width=16) (actual time=0.000..0.001 rows=4 loops=1)

  • Filter: ("recordError" IS NULL)
51. 0.048 0.048 ↑ 1.0 4 4

Seq Scan on space space_1 (cost=0.00..4.25 rows=4 width=16) (actual time=0.004..0.012 rows=4 loops=4)

  • Filter: ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid)
  • Rows Removed by Filter: 101
52. 0.004 0.008 ↓ 0.0 0 4

Bitmap Heap Scan on occupancy_reset occr (cost=4.58..14.38 rows=30 width=44) (actual time=0.002..0.002 rows=0 loops=4)

  • Recheck Cond: (("spaceId" = space_1."spaceId") AND ("resetDate" < '2018-11-30 00:00:00+00'::timestamp with time zone))
53. 0.004 0.004 ↓ 0.0 0 4

Bitmap Index Scan on "occupancy_reset_spaceId_resetDate_key" (cost=0.00..4.58 rows=30 width=0) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (("spaceId" = space_1."spaceId") AND ("resetDate" < '2018-11-30 00:00:00+00'::timestamp with time zone))
54. 0.000 0.000 ↓ 0.0 0

Hash (cost=26.67..26.67 rows=22 width=24) (never executed)

55. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=26.23..26.45 rows=22 width=24) (never executed)

  • Group Key: occupancy_reset_1."spaceId
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on occupancy_reset occupancy_reset_1 (cost=0.00..22.89 rows=668 width=24) (never executed)

  • Filter: ("resetDate" < '2018-11-29 00:00:00+00'::timestamp with time zone)
57. 0.007 0.361 ↑ 90.9 11 1

Subquery Scan on *SELECT* 2 (cost=4.86..49.41 rows=1,000 width=44) (actual time=0.154..0.361 rows=11 loops=1)

58. 0.169 0.354 ↑ 90.9 11 1

Nested Loop Left Join (cost=4.86..36.91 rows=1,000 width=44) (actual time=0.150..0.354 rows=11 loops=1)

59. 0.014 0.161 ↓ 4.0 4 1

Hash Semi Join (cost=4.86..9.39 rows=1 width=78) (actual time=0.089..0.161 rows=4 loops=1)

  • Hash Cond: (sp_1."spaceId" = space_2."spaceId")
60. 0.093 0.093 ↑ 1.1 88 1

Seq Scan on space sp_1 (cost=0.00..4.25 rows=100 width=78) (actual time=0.007..0.093 rows=88 loops=1)

  • Filter: (("resetConfig" -> 'resetTime'::text) IS NOT NULL)
  • Rows Removed by Filter: 17
61. 0.005 0.054 ↓ 4.0 4 1

Hash (cost=4.85..4.85 rows=1 width=32) (actual time=0.054..0.054 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.006 0.049 ↓ 4.0 4 1

Nested Loop (cost=0.54..4.85 rows=1 width=32) (actual time=0.011..0.049 rows=4 loops=1)

  • Join Filter: (space_2."spaceId" = reset_validity_1."spaceId")
  • Rows Removed by Join Filter: 12
63. 0.002 0.003 ↓ 4.0 4 1

HashAggregate (cost=0.54..0.55 rows=1 width=16) (actual time=0.003..0.003 rows=4 loops=1)

  • Group Key: reset_validity_1."spaceId
64. 0.001 0.001 ↓ 4.0 4 1

CTE Scan on reset_validity reset_validity_1 (cost=0.00..0.54 rows=1 width=16) (actual time=0.000..0.001 rows=4 loops=1)

  • Filter: ("recordError" IS NULL)
65. 0.040 0.040 ↑ 1.0 4 4

Seq Scan on space space_2 (cost=0.00..4.25 rows=4 width=16) (actual time=0.004..0.010 rows=4 loops=4)

  • Filter: ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid)
  • Rows Removed by Filter: 101
66. 0.024 0.024 ↑ 333.3 3 4

Result (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.005..0.006 rows=3 loops=4)

67. 0.001 0.146 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=50.88..216.59 rows=1,000 width=44) (actual time=0.146..0.146 rows=0 loops=1)

68. 0.006 0.145 ↓ 0.0 0 1

Hash Right Join (cost=50.88..204.09 rows=1,000 width=44) (actual time=0.145..0.145 rows=0 loops=1)

  • Hash Cond: ((schp_1."scheduleId" = sch_1."scheduleId") AND ((schp_1.weekday)::double precision = date_part('dow'::text, (generate_series('2018-11-28 00:00:00'::timestamp without time zone, '2018-11-30 00:00:00'::timestamp without time zone, '24:00:00'::interval)))))
69. 0.000 0.000 ↓ 0.0 0

Seq Scan on schedule_period schp_1 (cost=0.00..96.16 rows=3,116 width=28) (never executed)

70. 0.000 0.139 ↓ 0.0 0 1

Hash (cost=35.88..35.88 rows=1,000 width=40) (actual time=0.139..0.139 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
71. 0.000 0.139 ↓ 0.0 0 1

Nested Loop (cost=5.69..35.88 rows=1,000 width=40) (actual time=0.139..0.139 rows=0 loops=1)

72. 0.000 0.139 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.69..10.88 rows=1 width=32) (actual time=0.139..0.139 rows=0 loops=1)

73. 0.000 0.139 ↓ 0.0 0 1

Nested Loop (cost=5.42..10.47 rows=1 width=32) (actual time=0.139..0.139 rows=0 loops=1)

74. 0.000 0.139 ↓ 0.0 0 1

Nested Loop (cost=5.14..10.13 rows=1 width=48) (actual time=0.139..0.139 rows=0 loops=1)

75. 0.003 0.139 ↓ 0.0 0 1

Hash Semi Join (cost=4.86..9.39 rows=1 width=32) (actual time=0.139..0.139 rows=0 loops=1)

  • Hash Cond: (sp_2."spaceId" = space_3."spaceId")
76. 0.081 0.081 ↑ 20.0 5 1

Seq Scan on space sp_2 (cost=0.00..4.25 rows=100 width=32) (actual time=0.016..0.081 rows=5 loops=1)

  • Filter: (("resetConfig" -> 'resetWithHoursOfOp'::text) IS NOT NULL)
  • Rows Removed by Filter: 100
77. 0.005 0.055 ↓ 4.0 4 1

Hash (cost=4.85..4.85 rows=1 width=32) (actual time=0.055..0.055 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.001 0.050 ↓ 4.0 4 1

Nested Loop (cost=0.54..4.85 rows=1 width=32) (actual time=0.011..0.050 rows=4 loops=1)

  • Join Filter: (space_3."spaceId" = reset_validity_2."spaceId")
  • Rows Removed by Join Filter: 12
79. 0.004 0.005 ↓ 4.0 4 1

HashAggregate (cost=0.54..0.55 rows=1 width=16) (actual time=0.003..0.005 rows=4 loops=1)

  • Group Key: reset_validity_2."spaceId
80. 0.001 0.001 ↓ 4.0 4 1

CTE Scan on reset_validity reset_validity_2 (cost=0.00..0.54 rows=1 width=16) (actual time=0.000..0.001 rows=4 loops=1)

  • Filter: ("recordError" IS NULL)
81. 0.044 0.044 ↑ 1.0 4 4

Seq Scan on space space_3 (cost=0.00..4.25 rows=4 width=16) (actual time=0.003..0.011 rows=4 loops=4)

  • Filter: ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid)
  • Rows Removed by Filter: 101
82. 0.000 0.000 ↓ 0.0 0

Index Only Scan using location_pkey on location l_1 (cost=0.28..0.74 rows=1 width=16) (never executed)

  • Index Cond: ("locationId" = sp_2."locationId")
  • Heap Fetches: 0
83. 0.000 0.000 ↓ 0.0 0

Index Scan using "location_schedule_assignment_locationId_idx" on location_schedule_assignment lsa_1 (cost=0.28..0.32 rows=1 width=32) (never executed)

  • Index Cond: ("locationId" = l_1."locationId")
  • Filter: (("startDate" <= '2018-11-28 00:00:00'::timestamp without time zone) AND ("endDate" >= '2018-11-29'::date))
84. 0.000 0.000 ↓ 0.0 0

Index Only Scan using schedule_pkey on schedule sch_1 (cost=0.27..0.40 rows=1 width=16) (never executed)

  • Index Cond: ("scheduleId" = lsa_1."scheduleId")
  • Heap Fetches: 0
85. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..5.01 rows=1,000 width=8) (never executed)

86.          

CTE traffic

87. 2.805 19,292.947 ↓ 2.3 10,968 1

Nested Loop Left Join (cost=450,140.81..500,535.50 rows=4,734 width=184) (actual time=19,224.983..19,292.947 rows=10,968 loops=1)

88.          

CTE cntdata

89. 4.832 19,226.274 ↓ 2.3 10,968 1

Sort (cost=450,118.40..450,130.24 rows=4,734 width=168) (actual time=19,224.935..19,226.274 rows=10,968 loops=1)

  • Sort Key: cd."recordDate
  • Sort Method: quicksort Memory: 1927kB
90. 4.153 19,221.442 ↓ 2.3 10,968 1

WindowAgg (cost=449,734.74..449,829.42 rows=4,734 width=168) (actual time=19,216.382..19,221.442 rows=10,968 loops=1)

91. 8.999 19,217.289 ↓ 2.3 10,968 1

Sort (cost=449,734.74..449,746.58 rows=4,734 width=160) (actual time=19,216.375..19,217.289 rows=10,968 loops=1)

  • Sort Key: sp_3."spaceId", cd."recordDate
  • Sort Method: quicksort Memory: 1927kB
92. 35.521 19,208.290 ↓ 2.3 10,968 1

GroupAggregate (cost=449,161.72..449,445.76 rows=4,734 width=160) (actual time=19,167.759..19,208.290 rows=10,968 loops=1)

  • Group Key: cd."orgId", cd."recordDate", sp_3."spaceId", l_2."locationId", v."recordError
93. 29.627 19,172.769 ↓ 9.3 44,070 1

Sort (cost=449,161.72..449,173.55 rows=4,734 width=102) (actual time=19,167.731..19,172.769 rows=44,070 loops=1)

  • Sort Key: cd."recordDate", sp_3."spaceId", l_2."locationId", v."recordError
  • Sort Method: quicksort Memory: 7484kB
94. 0.000 19,143.142 ↓ 9.3 44,070 1

Merge Join (cost=31.15..448,872.73 rows=4,734 width=102) (actual time=2.386..19,143.142 rows=44,070 loops=1)

  • Merge Cond: (sp_3."spaceId" = sza."spaceId")
  • Join Filter: (cd."recordDate" >= (SubPlan 3))
  • Rows Removed by Join Filter: 4766964
95. 0.018 1.601 ↑ 1.4 69 1

Nested Loop Left Join (cost=8.78..83.91 rows=100 width=64) (actual time=1.418..1.601 rows=69 loops=1)

96. 0.017 1.445 ↑ 1.4 69 1

Merge Left Join (cost=8.50..9.41 rows=100 width=64) (actual time=1.411..1.445 rows=69 loops=1)

  • Merge Cond: (sp_3."spaceId" = v."spaceId")
97. 0.049 0.066 ↑ 1.4 69 1

Sort (cost=7.32..7.57 rows=100 width=32) (actual time=0.051..0.066 rows=69 loops=1)

  • Sort Key: sp_3."spaceId
  • Sort Method: quicksort Memory: 33kB
98. 0.017 0.017 ↓ 1.1 105 1

Seq Scan on space sp_3 (cost=0.00..4.00 rows=100 width=32) (actual time=0.001..0.017 rows=105 loops=1)

99. 0.009 1.362 ↑ 6.8 4 1

Sort (cost=1.18..1.25 rows=27 width=48) (actual time=1.359..1.362 rows=4 loops=1)

  • Sort Key: v."spaceId
  • Sort Method: quicksort Memory: 25kB
100. 1.353 1.353 ↑ 6.8 4 1

CTE Scan on reset_validity v (cost=0.00..0.54 rows=27 width=48) (actual time=1.350..1.353 rows=4 loops=1)

101. 0.138 0.138 ↑ 1.0 1 69

Index Only Scan using location_pkey on location l_2 (cost=0.28..0.74 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=69)

  • Index Cond: ("locationId" = sp_3."locationId")
  • Heap Fetches: 0
102. 1,456.238 5,148.123 ↓ 338.7 4,811,034 1

Materialize (cost=22.37..377,027.92 rows=14,203 width=70) (actual time=0.162..5,148.123 rows=4,811,034 loops=1)

103. 519.094 3,691.885 ↓ 338.7 4,811,034 1

Nested Loop (cost=22.37..376,992.41 rows=14,203 width=70) (actual time=0.158..3,691.885 rows=4,811,034 loops=1)

104. 0.055 0.375 ↓ 1.6 16 1

Nested Loop (cost=21.80..37.00 rows=10 width=70) (actual time=0.142..0.375 rows=16 loops=1)

105. 0.032 0.208 ↓ 1.6 16 1

Merge Join (cost=21.52..22.87 rows=10 width=49) (actual time=0.137..0.208 rows=16 loops=1)

  • Merge Cond: (space_4."spaceId" = sza."spaceId")
106. 0.012 0.028 ↑ 1.0 4 1

Sort (cost=4.29..4.30 rows=4 width=16) (actual time=0.025..0.028 rows=4 loops=1)

  • Sort Key: space_4."spaceId
  • Sort Method: quicksort Memory: 25kB
107. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on space space_4 (cost=0.00..4.25 rows=4 width=16) (actual time=0.007..0.016 rows=4 loops=1)

  • Filter: ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid)
  • Rows Removed by Filter: 101
108. 0.103 0.148 ↑ 1.5 166 1

Sort (cost=17.23..17.84 rows=246 width=33) (actual time=0.106..0.148 rows=166 loops=1)

  • Sort Key: sza."spaceId
  • Sort Method: quicksort Memory: 45kB
109. 0.045 0.045 ↓ 1.0 257 1

Seq Scan on space_zone_assignment sza (cost=0.00..7.46 rows=246 width=33) (actual time=0.003..0.045 rows=257 loops=1)

110. 0.112 0.112 ↑ 1.0 1 16

Index Scan using zone_pkey on zone z (cost=0.28..1.40 rows=1 width=21) (actual time=0.006..0.007 rows=1 loops=16)

  • Index Cond: ("zoneId" = sza."zoneId")
111. 3,172.416 3,172.416 ↓ 201.0 300,690 16

Index Scan using "countdata_zoneId_idx" on countdata cd (cost=0.57..37,680.58 rows=1,496 width=48) (actual time=0.012..198.276 rows=300,690 loops=16)

  • Index Cond: ("zoneId" = z."zoneId")
  • Filter: (("recordDate" < '2018-11-30 00:00:00'::timestamp without time zone) AND ("orgId" = 'da31d2b8-2073-480c-bf6e-d4cffa673bcd'::uuid))
112.          

SubPlan (forMerge Join)

113. 4,811.034 14,433.102 ↑ 1.0 1 4,811,034

Aggregate (cost=5.03..5.04 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,811,034)

114. 9,622.068 9,622.068 ↑ 1.0 1 4,811,034

CTE Scan on resets (cost=0.00..5.03 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=4,811,034)

  • Filter: (("resetDate" < '2018-11-29 00:00:00'::timestamp without time zone) AND ("spaceId" = sp_3."spaceId"))
  • Rows Removed by Filter: 10
115. 6.502 19,257.238 ↓ 2.3 10,968 1

Nested Loop Left Join (cost=5.54..26,439.39 rows=4,734 width=184) (actual time=19,224.973..19,257.238 rows=10,968 loops=1)

116. 19,228.800 19,228.800 ↓ 2.3 10,968 1

CTE Scan on cntdata (cost=0.00..94.68 rows=4,734 width=160) (actual time=19,224.937..19,228.800 rows=10,968 loops=1)

117. 0.000 21.936 ↓ 0.0 0 10,968

Subquery Scan on r (cost=5.54..5.56 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=10,968)

  • Filter: (cntdata."spaceId" = r."spaceId")
118. 0.000 21.936 ↓ 0.0 0 10,968

Limit (cost=5.54..5.54 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=10,968)

119. 10.968 21.936 ↓ 0.0 0 10,968

Sort (cost=5.54..5.54 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=10,968)

  • Sort Key: resets_1."resetDate
  • Sort Method: quicksort Memory: 25kB
120. 10.968 10.968 ↓ 0.0 0 10,968

CTE Scan on resets resets_1 (cost=0.00..5.53 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=10,968)

  • Filter: ((cntdata."recordDate" <= "resetDate") AND (cntdata."nextRecordDate" > "resetDate") AND ("spaceId" = cntdata."spaceId"))
  • Rows Removed by Filter: 11
121. 0.000 32.904 ↑ 1.0 1 10,968

Subquery Scan on rid (cost=5.04..5.05 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=10,968)

  • Filter: (cntdata."spaceId" = rid."spaceId")
122. 10.968 32.904 ↑ 1.0 1 10,968

Limit (cost=5.04..5.04 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=10,968)

123. 0.000 21.936 ↑ 1.0 1 10,968

Sort (cost=5.04..5.04 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=10,968)

  • Sort Key: resets_2."resetDate" DESC
  • Sort Method: quicksort Memory: 25kB
124. 21.936 21.936 ↑ 1.0 1 10,968

CTE Scan on resets resets_2 (cost=0.00..5.03 rows=1 width=40) (actual time=0.000..0.002 rows=1 loops=10,968)

  • Filter: ((cntdata."recordDate" > "resetDate") AND ("spaceId" = cntdata."spaceId"))
  • Rows Removed by Filter: 10
125. 12.644 19,323.351 ↓ 2.3 10,968 1

WindowAgg (cost=383.66..549.35 rows=4,734 width=216) (actual time=19,309.776..19,323.351 rows=10,968 loops=1)

126. 11.983 19,310.707 ↓ 2.3 10,968 1

Sort (cost=383.66..395.50 rows=4,734 width=184) (actual time=19,309.744..19,310.707 rows=10,968 loops=1)

  • Sort Key: traffic."spaceId", traffic."occupancyResetId", traffic."recordDate", traffic."nextRecordDate
  • Sort Method: quicksort Memory: 1927kB
127. 19,298.724 19,298.724 ↓ 2.3 10,968 1

CTE Scan on traffic (cost=0.00..94.68 rows=4,734 width=184) (actual time=19,224.985..19,298.724 rows=10,968 loops=1)

128. 7.284 19,339.337 ↓ 11.4 5,321 1

HashAggregate (cost=138.07..146.21 rows=465 width=192) (actual time=19,337.199..19,339.337 rows=5,321 loops=1)

  • Group Key: entity_occupancy."spaceId", entity_occupancy."recordDate", entity_occupancy."orgId", entity_occupancy."locationId", entity_occupancy."recordError
129. 19,332.053 19,332.053 ↓ 3.4 5,324 1

CTE Scan on entity_occupancy (cost=0.00..106.52 rows=1,578 width=184) (actual time=19,329.626..19,332.053 rows=5,324 loops=1)

  • Filter: ("recordDate" >= '2018-11-29 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 5644
130. 5.321 5.321 ↑ 1.0 1 5,321

Index Only Scan using location_pkey on location (cost=0.28..4.29 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=5,321)

  • Index Cond: ("locationId" = occupancy."locationId")
  • Heap Fetches: 0
131. 0.051 19,366.752 ↓ 92.0 92 1

Sort (cost=0.03..0.04 rows=1 width=410) (actual time=19,366.744..19,366.752 rows=92 loops=1)

  • Sort Key: occ."recordDate_hour_1", occ."spaceId", occ.name, occ."maxOccupancy", occ.sumins, occ.sumouts, occ."recordError", occ."minOccupancy", occ."avgOccupancy", occ."sumOccupancy", occ."locationId
  • Sort Method: quicksort Memory: 37kB
132. 19,366.701 19,366.701 ↓ 92.0 92 1

CTE Scan on occ (cost=0.00..0.02 rows=1 width=410) (actual time=19,363.127..19,366.701 rows=92 loops=1)

133.          

SubPlan (forGroup)

134. 0.920 0.920 ↑ 1.0 1 92

Seq Scan on space s (cost=0.00..4.25 rows=1 width=32) (actual time=0.004..0.010 rows=1 loops=92)

  • Filter: ("spaceId" = occ."spaceId")
  • Rows Removed by Filter: 104
135. 0.184 0.184 ↑ 1.0 1 92

Index Scan using location_pkey on location l_3 (cost=0.28..8.29 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=92)

  • Index Cond: ("locationId" = occ."locationId")