explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b0Q9

Settings
# exclusive inclusive rows x rows loops node
1. 0.866 213.921 ↑ 16.1 1,390 1

Hash Left Join (cost=7,358.46..7,632.93 rows=22,427 width=431) (actual time=211.367..213.921 rows=1,390 loops=1)

  • Output: b."SP_ID", sp."NAME", sp."LATITUDE", sp."LONGITUDE", d."NUMBER_OF_BIKE", d."NUMBER_OF_SPACE", d."IS_SERVICEABLE", d."NORMAL_BIKE", d."NORMAL_SPACE", b."STATION_NORMAL_CAPACITY", d."ELECTRIC_BIKE", d."ELECTRIC_SPACE", b."STATION_ELECTRIC_CAPACITY" (...)
  • Hash Cond: ((b."SP_ID")::text = (k."SP_ID")::text)
  • Buffers: shared hit=25,321 read=8
2. 1.526 203.908 ↑ 16.1 1,390 1

Hash Right Join (cost=6,988.14..7,178.35 rows=22,427 width=419) (actual time=202.195..203.908 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION", p."PRIORITY", d."NUMBER_OF_ (...)
  • Hash Cond: ((docking_station_recent_traffic_demand."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=25,290 read=8
3. 81.481 81.481 ↓ 1.4 1,367 1

Function Scan on public.docking_station_recent_traffic_demand (cost=0.26..10.26 rows=1,000 width=96) (actual time=81.243..81.481 rows=1,367 loops=1)

  • Output: docking_station_recent_traffic_demand."SP_ID", docking_station_recent_traffic_demand."ESTIMATED_RENTAL_DEMAND", docking_station_recent_traffic_demand."ESTIMATED_PARKING_DEMAND
  • Function Call: docking_station_recent_traffic_demand((now())::timestamp without time zone)
  • Buffers: shared hit=2,388 read=4
4. 1.230 120.901 ↑ 16.1 1,390 1

Hash (cost=6,707.55..6,707.55 rows=22,427 width=355) (actual time=120.901..120.901 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION", p."PRIORITY", d."NUMB (...)
  • Buckets: 32,768 Batches: 1 Memory Usage: 531kB
  • Buffers: shared hit=22,902 read=4
5. 1.745 119.671 ↑ 16.1 1,390 1

Hash Right Join (cost=6,517.34..6,707.55 rows=22,427 width=355) (actual time=117.684..119.671 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION", p."PRIORITY", d (...)
  • Hash Cond: ((o."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=22,902 read=4
6. 29.420 29.420 ↓ 1.4 1,390 1

Function Scan on public.effective_rush_hours_at_time o (cost=2.50..12.50 rows=1,000 width=58) (actual time=29.111..29.420 rows=1,390 loops=1)

  • Output: o."SP_ID", o."START_TIME", o."END_TIME", o."HISTORICAL_TRAFFIC_IN", o."HISTORICAL_TRAFFIC_OUT", o."NETT_HISTORICAL_TRAFFIC", o."REPLENISHMENT_VALUE
  • Function Call: effective_rush_hours_at_time((now())::timestamp without time zone)
  • Buffers: shared hit=1,082 read=4
7. 8.517 88.506 ↑ 16.1 1,390 1

Hash (cost=6,234.50..6,234.50 rows=22,427 width=351) (actual time=88.506..88.506 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION", p."PRIORI (...)
  • Buckets: 32,768 Batches: 1 Memory Usage: 525kB
  • Buffers: shared hit=21,820
8. 3.672 79.989 ↑ 16.1 1,390 1

Hash Right Join (cost=6,128.97..6,234.50 rows=22,427 width=351) (actual time=74.310..79.989 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION", p." (...)
  • Hash Cond: ((n."KERNEL_SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=21,820
9. 2.966 49.794 ↓ 3.3 1,386 1

WindowAgg (cost=1,223.35..1,247.84 rows=426 width=120) (actual time=47.708..49.794 rows=1,386 loops=1)

  • Output: n."KERNEL_SP_ID", ((NOT (bool_and(((NOT s."IS_SERVICEABLE") OR (s."NUMBER_OF_BIKE" = 0))))) AND (NOT (bool_and(((NOT s."IS_SERVICEABLE") OR (s."NUMBER_OF_SPACE" = 0)))))), (('100'::numeric * (sum(CASE WHEN ((NO (...)
  • Buffers: shared hit=126
10. 16.427 46.828 ↓ 3.3 1,386 1

HashAggregate (cost=1,223.35..1,227.61 rows=426 width=83) (actual time=46.281..46.828 rows=1,386 loops=1)

  • Output: n."KERNEL_SP_ID", NULL::bigint, NULL::bigint, s."RECORDED_TIME", NULL::boolean, bool_and(((NOT s."IS_SERVICEABLE") OR (s."NUMBER_OF_BIKE" = 0))), bool_and(((NOT s."IS_SERVICEABLE") OR (s."NUMBER_OF_SPACE" (...)
  • Group Key: n."KERNEL_SP_ID", s."RECORDED_TIME
  • Buffers: shared hit=126
11.          

CTE CLUSTERS

12. 6.103 14.604 ↑ 1.0 7,774 1

Merge Append (cost=106.98..446.99 rows=7,774 width=20) (actual time=6.289..14.604 rows=7,774 loops=1)

  • Sort Key: "NEIGHBOURS"."SP_ID", "NEIGHBOURS"."NEIGHBOUR_SP_ID
  • Buffers: shared hit=54
13. 1.841 1.841 ↑ 1.0 6,352 1

Index Only Scan using "NEIGHBOURS_IDX" on public."NEIGHBOURS" (cost=0.28..239.56 rows=6,352 width=20) (actual time=0.016..1.841 rows=6,352 loops=1)

  • Output: "NEIGHBOURS"."SP_ID", "NEIGHBOURS"."NEIGHBOUR_SP_ID
  • Heap Fetches: 0
  • Buffers: shared hit=36
14. 6.268 6.660 ↑ 1.0 1,422 1

Sort (cost=106.69..110.24 rows=1,422 width=20) (actual time=6.269..6.660 rows=1,422 loops=1)

  • Output: "DOCKING_STATION"."SP_ID", "DOCKING_STATION"."SP_ID
  • Sort Key: "DOCKING_STATION"."SP_ID", "DOCKING_STATION"."SP_ID
  • Sort Method: quicksort Memory: 160kB
  • Buffers: shared hit=18
15. 0.392 0.392 ↑ 1.0 1,422 1

Seq Scan on public."DOCKING_STATION" (cost=0.00..32.22 rows=1,422 width=20) (actual time=0.003..0.392 rows=1,422 loops=1)

  • Output: "DOCKING_STATION"."SP_ID", "DOCKING_STATION"."SP_ID
  • Buffers: shared hit=18
16. 3.102 30.401 ↓ 17.8 7,599 1

Hash Join (cost=364.44..752.93 rows=426 width=135) (actual time=24.142..30.401 rows=7,599 loops=1)

  • Output: n."KERNEL_SP_ID", s."RECORDED_TIME", s."IS_SERVICEABLE", s."NUMBER_OF_BIKE", s."NUMBER_OF_SPACE", l."CAPACITY", m."MIN", n_1."MIN
  • Hash Cond: ((s."SP_ID")::text = (n."NEIGHBOUR_SP_ID")::text)
  • Buffers: shared hit=126
17. 1.046 5.312 ↓ 18.2 1,367 1

Hash Join (cost=111.79..422.98 rows=75 width=111) (actual time=2.102..5.312 rows=1,367 loops=1)

  • Output: m."MIN", n_1."MIN", s."RECORDED_TIME", s."IS_SERVICEABLE", s."NUMBER_OF_BIKE", s."NUMBER_OF_SPACE", s."SP_ID", l."CAPACITY", l."SP_ID", p_1."SP_ID
  • Hash Cond: ((s."SP_ID")::text = (l."SP_ID")::text)
  • Buffers: shared hit=72
18. 0.910 3.363 ↓ 18.2 1,367 1

Hash Join (cost=61.79..371.95 rows=75 width=97) (actual time=1.184..3.363 rows=1,367 loops=1)

  • Output: m."MIN", n_1."MIN", s."RECORDED_TIME", s."IS_SERVICEABLE", s."NUMBER_OF_BIKE", s."NUMBER_OF_SPACE", s."SP_ID", p_1."SP_ID
  • Hash Cond: ((s."SP_ID")::text = (p_1."SP_ID")::text)
  • Buffers: shared hit=54
19. 0.714 1.526 ↓ 17.8 1,367 1

Nested Loop (cost=0.95..310.07 rows=77 width=87) (actual time=0.248..1.526 rows=1,367 loops=1)

  • Output: m."MIN", n_1."MIN", s."RECORDED_TIME", s."IS_SERVICEABLE", s."NUMBER_OF_BIKE", s."NUMBER_OF_SPACE", s."SP_ID
  • Buffers: shared hit=32
20. 0.002 0.218 ↑ 1.0 1 1

Nested Loop (cost=0.50..0.53 rows=1 width=64) (actual time=0.215..0.218 rows=1 loops=1)

  • Output: m."MIN", n_1."MIN
  • Buffers: shared hit=2
21. 0.161 0.161 ↑ 1.0 1 1

Function Scan on public.sys_config_value m (cost=0.25..0.26 rows=1 width=32) (actual time=0.160..0.161 rows=1 loops=1)

  • Output: m."MIN
  • Function Call: sys_config_value('KPI_MIN_DS_SPACE_THRESHOLD'::character varying)
  • Buffers: shared hit=1
22. 0.055 0.055 ↑ 1.0 1 1

Function Scan on public.sys_config_value n_1 (cost=0.25..0.26 rows=1 width=32) (actual time=0.053..0.055 rows=1 loops=1)

  • Output: n_1."MIN
  • Function Call: sys_config_value('KPI_MIN_DS_BIKE_THRESHOLD'::character varying)
  • Buffers: shared hit=1
23. 0.594 0.594 ↓ 17.8 1,367 1

Index Scan using "DOCKING_STATION_STATUS_RECORDED_TIME_IDX" on public."DOCKING_STATION_STATUS" s (cost=0.45..308.77 rows=77 width=23) (actual time=0.022..0.594 rows=1,367 loops=1)

  • Output: s."SP_ID", s."NUMBER_OF_BIKE", s."NUMBER_OF_SPACE", s."RECORDED_TIME", s."IS_SERVICEABLE", s."NORMAL_BIKE", s."NORMAL_SPACE", s."ELECTRIC_BIKE", s."ELECTRIC_SPACE", s."NORMAL (...)
  • Index Cond: ((s."RECORDED_TIME" > ((now())::timestamp without time zone - '00:03:00'::interval)) AND (s."RECORDED_TIME" <= (now())::timestamp without time zone))
  • Buffers: shared hit=30
24. 0.465 0.927 ↑ 1.0 1,395 1

Hash (cost=43.41..43.41 rows=1,395 width=10) (actual time=0.926..0.927 rows=1,395 loops=1)

  • Output: p_1."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 75kB
  • Buffers: shared hit=22
25. 0.462 0.462 ↑ 1.0 1,395 1

Seq Scan on public."SERVICE_POINT" p_1 (cost=0.00..43.41 rows=1,395 width=10) (actual time=0.005..0.462 rows=1,395 loops=1)

  • Output: p_1."SP_ID
  • Filter: ((p_1."REMOVAL_DATE" IS NULL) OR (p_1."REMOVAL_DATE" >= now()))
  • Rows Removed by Filter: 32
  • Buffers: shared hit=22
26. 0.437 0.903 ↑ 1.0 1,422 1

Hash (cost=32.22..32.22 rows=1,422 width=14) (actual time=0.903..0.903 rows=1,422 loops=1)

  • Output: l."CAPACITY", l."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=18
27. 0.466 0.466 ↑ 1.0 1,422 1

Seq Scan on public."DOCKING_STATION" l (cost=0.00..32.22 rows=1,422 width=14) (actual time=0.010..0.466 rows=1,422 loops=1)

  • Output: l."CAPACITY", l."SP_ID
  • Buffers: shared hit=18
28. 2.802 21.987 ↑ 1.0 7,774 1

Hash (cost=155.48..155.48 rows=7,774 width=108) (actual time=21.987..21.987 rows=7,774 loops=1)

  • Output: n."KERNEL_SP_ID", n."NEIGHBOUR_SP_ID
  • Buckets: 8,192 Batches: 1 Memory Usage: 470kB
  • Buffers: shared hit=54
29. 19.185 19.185 ↑ 1.0 7,774 1

CTE Scan on "CLUSTERS" n (cost=0.00..155.48 rows=7,774 width=108) (actual time=6.292..19.185 rows=7,774 loops=1)

  • Output: n."KERNEL_SP_ID", n."NEIGHBOUR_SP_ID
  • Buffers: shared hit=54
30. 0.904 26.523 ↑ 16.1 1,390 1

Hash (cost=4,625.28..4,625.28 rows=22,427 width=285) (actual time=26.523..26.523 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs."ACTION (...)
  • Buckets: 32,768 Batches: 1 Memory Usage: 487kB
  • Buffers: shared hit=21,694
31. 0.857 25.619 ↑ 16.1 1,390 1

Hash Right Join (cost=631.83..4,625.28 rows=22,427 width=285) (actual time=10.305..25.619 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID", hs." (...)
  • Hash Cond: ((dst."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=21,694
32. 14.497 14.497 ↑ 52.0 441 1

Index Scan using test2 on public."SERVICE_POINT_RUSH_HOUR" dst (cost=0.43..3,683.58 rows=22,943 width=54) (actual time=0.022..14.497 rows=441 loops=1)

  • Output: dst."SP_ID", dst."START_TIME", dst."END_TIME", dst."HISTORICAL_TRAFFIC_IN", dst."HISTORICAL_TRAFFIC_OUT", dst."NETT_HISTORICAL_TRAFFIC", dst."REPLENISHMENT_VALUE
  • Index Cond: ((now())::timestamp without time zone <= dst."END_TIME")
  • Filter: ((now())::timestamp without time zone >= dst."START_TIME")
  • Rows Removed by Filter: 23,673
  • Buffers: shared hit=21,608
33. 0.823 10.265 ↑ 1.0 1,390 1

Hash (cost=614.02..614.02 rows=1,390 width=241) (actual time=10.265..10.265 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_SP_ID" (...)
  • Buckets: 2,048 Batches: 1 Memory Usage: 228kB
  • Buffers: shared hit=86
34. 0.882 9.442 ↑ 1.0 1,390 1

Hash Left Join (cost=250.01..614.02 rows=1,390 width=241) (actual time=4.419..9.442 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", sp."NAME", sp."LATITUDE", sp."LONGITUDE", hs."BIKE_HUB_ (...)
  • Hash Cond: ((h."BIKE_HUB_SP_ID")::text = (hs."BIKE_HUB_SP_ID")::text)
  • Buffers: shared hit=86
35. 0.927 8.512 ↑ 1.0 1,390 1

Hash Left Join (cost=215.00..569.62 rows=1,390 width=183) (actual time=4.367..8.512 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", h."BIKE_HUB_SP_ID", sp."NAME", sp."LATITUDE", sp. (...)
  • Hash Cond: ((b."SP_ID")::text = (p."SP_ID")::text)
  • Buffers: shared hit=82
36. 0.780 6.751 ↑ 1.0 1,390 1

Hash Left Join (cost=175.00..510.52 rows=1,390 width=179) (actual time=3.526..6.751 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", h."BIKE_HUB_SP_ID", sp."NAME", sp."LATITUDE (...)
  • Hash Cond: ((b."SP_ID")::text = (a."SP_ID")::text)
  • Buffers: shared hit=74
37. 0.887 5.837 ↑ 1.0 1,390 1

Hash Join (cost=167.11..495.72 rows=1,390 width=153) (actual time=3.388..5.837 rows=1,390 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", h."BIKE_HUB_SP_ID", sp."NAME", sp."LATITUDE", sp."LONGITUDE", d."NUMBER_OF_BIKE", d."NUMB (...)
  • Hash Cond: ((b."SP_ID")::text = (sp."SP_ID")::text)
  • Buffers: shared hit=70
38. 1.614 3.574 ↑ 1.0 1,422 1

Hash Right Join (cost=92.00..401.38 rows=1,422 width=105) (actual time=2.004..3.574 rows=1,422 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", h."BIKE_HUB_SP_ID", d."NUMBER_OF_BIKE", d."NUMBER_OF_SPACE", d."IS_SERVICEABLE", d. (...)
  • Hash Cond: ((d."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=48
  • -> Index Scan using "DOCKING_STATION_STATUS_RECORDED_TIME_IDX" on public."DOCKING_STATION_STATUS" d (cost=0.45..308.77 rows=77 width=39) (actual time=0.035..0.569 rows=1,367 l (...)
  • Output: d."SP_ID", d."NUMBER_OF_BIKE", d."NUMBER_OF_SPACE", d."RECORDED_TIME", d."IS_SERVICEABLE", d."NORMAL_BIKE", d."NORMAL_SPACE", d."ELECTRIC_BIKE", d."ELECTRIC_SPACE (...)
  • Index Cond: ((d."RECORDED_TIME" > ((now())::timestamp without time zone - '00:03:00'::interval)) AND (d."RECORDED_TIME" <= (now())::timestamp without time zone))
  • Buffers: shared hit=30
39. 0.487 1.960 ↑ 1.0 1,422 1

Hash (cost=73.77..73.77 rows=1,422 width=76) (actual time=1.960..1.960 rows=1,422 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", h."BIKE_HUB_SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
  • Buffers: shared hit=18
40. 0.432 1.473 ↑ 1.0 1,422 1

Hash Right Join (cost=50.00..73.77 rows=1,422 width=76) (actual time=1.047..1.473 rows=1,422 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY", h."BIKE_HUB_SP_ID
  • Hash Cond: ((h."DOCK_SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=18
41. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public."BIKE_HUB_STATIONS" h (cost=0.00..15.80 rows=580 width=108) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: h."BIKE_HUB_SP_ID", h."DOCK_SP_ID", h."TRANSPORT_CHARACTERISTIC_REF_ID
42. 0.567 1.040 ↑ 1.0 1,422 1

Hash (cost=32.22..32.22 rows=1,422 width=22) (actual time=1.039..1.040 rows=1,422 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY
  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
  • Buffers: shared hit=18
43. 0.473 0.473 ↑ 1.0 1,422 1

Seq Scan on public."DOCKING_STATION" b (cost=0.00..32.22 rows=1,422 width=22) (actual time=0.003..0.473 rows=1,422 loops=1)

  • Output: b."SP_ID", b."STATION_NORMAL_CAPACITY", b."STATION_ELECTRIC_CAPACITY", b."CAPACITY
  • Buffers: shared hit=18
44. 0.600 1.376 ↑ 1.0 1,395 1

Hash (cost=57.68..57.68 rows=1,395 width=58) (actual time=1.376..1.376 rows=1,395 loops=1)

  • Output: sp."NAME", sp."LATITUDE", sp."LONGITUDE", sp."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 142kB
  • Buffers: shared hit=22
45. 0.776 0.776 ↑ 1.0 1,395 1

Seq Scan on public."SERVICE_POINT" sp (cost=0.00..57.68 rows=1,395 width=58) (actual time=0.012..0.776 rows=1,395 loops=1)

  • Output: sp."NAME", sp."LATITUDE", sp."LONGITUDE", sp."SP_ID
  • Filter: ((sp."INSTALLATION_DATE" <= (now())::timestamp without time zone) AND ((sp."REMOVAL_DATE" IS NULL) OR (sp."REMOVAL_DATE" >= (now())::timestamp without time zone)) (...)
  • Rows Removed by Filter: 32
  • Buffers: shared hit=22
46. 0.068 0.134 ↓ 1.0 176 1

Hash (cost=5.73..5.73 rows=173 width=36) (actual time=0.133..0.134 rows=176 loops=1)

  • Output: a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", a."SP_ID
  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=4
47. 0.066 0.066 ↓ 1.0 176 1

Seq Scan on public."ACCESS_RESTRICTION" a (cost=0.00..5.73 rows=173 width=36) (actual time=0.006..0.066 rows=176 loops=1)

  • Output: a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", a."SP_ID
  • Buffers: shared hit=4
48. 0.445 0.834 ↑ 1.0 1,422 1

Hash (cost=22.22..22.22 rows=1,422 width=14) (actual time=0.834..0.834 rows=1,422 loops=1)

  • Output: p."PRIORITY", p."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=8
49. 0.389 0.389 ↑ 1.0 1,422 1

Seq Scan on public."PRIORITY_STATION" p (cost=0.00..22.22 rows=1,422 width=14) (actual time=0.005..0.389 rows=1,422 loops=1)

  • Output: p."PRIORITY", p."SP_ID
  • Buffers: shared hit=8
50. 0.001 0.048 ↓ 0.0 0 1

Hash (cost=34.26..34.26 rows=60 width=112) (actual time=0.047..0.048 rows=0 loops=1)

  • Output: hs."BIKE_HUB_SP_ID", hs."ACTION
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=4
51. 0.001 0.047 ↓ 0.0 0 1

Subquery Scan on hs (cost=33.51..34.26 rows=60 width=112) (actual time=0.047..0.047 rows=0 loops=1)

  • Output: hs."BIKE_HUB_SP_ID", hs."ACTION
  • Buffers: shared hit=4
52. 0.028 0.046 ↓ 0.0 0 1

Sort (cost=33.51..33.66 rows=60 width=130) (actual time=0.046..0.046 rows=0 loops=1)

  • Output: foo."BIKE_HUB_SP_ID", NULL::smallint, NULL::timestamp without time zone, foo."ACTION", foo."RANK
  • Sort Key: foo."RANK
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
53. 0.001 0.018 ↓ 0.0 0 1

Subquery Scan on foo (cost=25.44..31.74 rows=60 width=130) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: foo."BIKE_HUB_SP_ID", NULL::smallint, NULL::timestamp without time zone, foo."ACTION", foo."RANK
  • Filter: (foo."RANK" <= 1)
  • Buffers: shared hit=1
54. 0.001 0.017 ↓ 0.0 0 1

WindowAgg (cost=25.44..29.49 rows=180 width=130) (actual time=0.017..0.017 rows=0 loops=1)

  • Output: h_1."SP_ID", NULL::smallint, h_1."RECORDED_TIME", h_1."ACTION", rank() OVER (?)
  • Buffers: shared hit=1
55. 0.005 0.016 ↓ 0.0 0 1

Sort (cost=25.44..25.89 rows=180 width=120) (actual time=0.015..0.016 rows=0 loops=1)

  • Output: h_1."SP_ID", h_1."RECORDED_TIME", h_1."ACTION
  • Sort Key: h_1."SP_ID", h_1."RECORDED_TIME" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
56. 0.004 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on public."BIKE_HUB_STATUS" h_1 (cost=5.55..18.70 rows=180 width=120) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: h_1."SP_ID", h_1."RECORDED_TIME", h_1."ACTION
  • Recheck Cond: (h_1."RECORDED_TIME" <= (now())::timestamp without time zone)
  • Buffers: shared hit=1
57. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on "BIKE_HUB_STATUS_RECORDED_TIME" (cost=0.00..5.50 rows=180 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (h_1."RECORDED_TIME" <= (now())::timestamp without time zone)
  • Buffers: shared hit=1
58. 0.509 9.147 ↓ 1,422.0 1,422 1

Hash (cost=370.31..370.31 rows=1 width=22) (actual time=9.147..9.147 rows=1,422 loops=1)

  • Output: k."TOTAL_SIGNAL", k."BASE_SIGNAL", k."NEIGHBOURHOOD_SIGNAL", k."SP_ID
  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 93kB
  • Buffers: shared hit=31
59. 0.686 8.638 ↓ 1,422.0 1,422 1

Subquery Scan on k (cost=367.38..370.31 rows=1 width=22) (actual time=6.308..8.638 rows=1,422 loops=1)

  • Output: k."TOTAL_SIGNAL", k."BASE_SIGNAL", k."NEIGHBOURHOOD_SIGNAL", k."SP_ID
  • Filter: (k."RANK" = 1)
  • Buffers: shared hit=31
60. 1.296 7.952 ↓ 15.8 1,422 1

WindowAgg (cost=367.38..369.18 rows=90 width=38) (actual time=6.304..7.952 rows=1,422 loops=1)

  • Output: d_1."SP_ID", d_1."TOTAL_SIGNAL", d_1."BASE_SIGNAL", d_1."NEIGHBOURHOOD_SIGNAL", rank() OVER (?), d_1."SIGNAL_TIME
  • Buffers: shared hit=31
61. 6.130 6.656 ↓ 15.8 1,422 1

Sort (cost=367.38..367.61 rows=90 width=30) (actual time=6.296..6.656 rows=1,422 loops=1)

  • Output: d_1."SP_ID", d_1."SIGNAL_TIME", d_1."TOTAL_SIGNAL", d_1."BASE_SIGNAL", d_1."NEIGHBOURHOOD_SIGNAL
  • Sort Key: d_1."SP_ID", d_1."SIGNAL_TIME" DESC
  • Sort Method: quicksort Memory: 160kB
  • Buffers: shared hit=31
62. 0.526 0.526 ↓ 15.8 1,422 1

Index Scan using "SIGNAL_SIGNAL_TIME_IDX" on public."SIGNAL" d_1 (cost=0.57..364.46 rows=90 width=30) (actual time=0.055..0.526 rows=1,422 loops=1)

  • Output: d_1."SP_ID", d_1."SIGNAL_TIME", d_1."TOTAL_SIGNAL", d_1."BASE_SIGNAL", d_1."NEIGHBOURHOOD_SIGNAL
  • Index Cond: ((d_1."SIGNAL_TIME" > ((now())::timestamp without time zone - '00:03:00'::interval)) AND (d_1."SIGNAL_TIME" <= (now())::timestamp without time zone))
  • Buffers: shared hit=31
Planning time : 6.078 ms
Execution time : 214.897 ms