explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m1wd

Settings
# exclusive inclusive rows x rows loops node
1. 0.954 1,275.557 ↑ 16.2 1,390 1

Hash Left Join (cost=80,447.67..80,858.64 rows=22,463 width=431) (actual time=1,271.219..1,275.557 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=49,110
2. 1.535 1,265.348 ↑ 16.2 1,390 1

Hash Right Join (cost=79,391.38..79,717.95 rows=22,463 width=419) (actual time=1,261.942..1,265.348 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: ((n."KERNEL_SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=49,088
3. 2.833 53.731 ↓ 1.1 1,386 1

WindowAgg (cost=1,947.77..2,023.50 rows=1,317 width=120) (actual time=51.769..53.731 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 ((NOT (bool_and(((NOT s."IS_ (...)
  • Buffers: shared hit=128
4. 16.002 50.898 ↓ 1.1 1,386 1

HashAggregate (cost=1,947.77..1,960.94 rows=1,317 width=83) (actual time=50.387..50.898 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" = 0))), bool_and(((NOT (...)
  • Group Key: n."KERNEL_SP_ID", s."RECORDED_TIME
  • Buffers: shared hit=128
5.          

CTE CLUSTERS

6. 6.311 14.686 ↑ 1.0 7,774 1

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

  • Sort Key: "NEIGHBOURS"."SP_ID", "NEIGHBOURS"."NEIGHBOUR_SP_ID
  • Buffers: shared hit=54
7. 1.901 1.901 ↑ 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.022..1.901 rows=6,352 loops=1)

  • Output: "NEIGHBOURS"."SP_ID", "NEIGHBOURS"."NEIGHBOUR_SP_ID
  • Heap Fetches: 0
  • Buffers: shared hit=36
8. 6.103 6.474 ↑ 1.0 1,422 1

Sort (cost=106.69..110.24 rows=1,422 width=20) (actual time=6.026..6.474 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
9. 0.371 0.371 ↑ 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.371 rows=1,422 loops=1)

  • Output: "DOCKING_STATION"."SP_ID", "DOCKING_STATION"."SP_ID
  • Buffers: shared hit=18
10. 3.275 34.896 ↓ 5.8 7,599 1

Hash Join (cost=467.42..1,428.35 rows=1,317 width=135) (actual time=29.364..34.896 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 = (p_1."SP_ID")::text)
  • Buffers: shared hit=128
11. 1.024 3.452 ↓ 5.8 1,367 1

Hash Join (cost=50.95..997.18 rows=237 width=101) (actual time=1.178..3.452 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
  • Hash Cond: ((s."SP_ID")::text = (l."SP_ID")::text)
  • Buffers: shared hit=52
12. 0.709 1.514 ↓ 5.8 1,367 1

Nested Loop (cost=0.95..943.92 rows=237 width=87) (actual time=0.256..1.514 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=34
13. 0.003 0.223 ↑ 1.0 1 1

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

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

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

  • Output: m."MIN
  • Function Call: sys_config_value('KPI_MIN_DS_SPACE_THRESHOLD'::character varying)
  • Buffers: shared hit=1
15. 0.054 0.054 ↑ 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.052..0.054 rows=1 loops=1)

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

Index Scan using "DOCKING_STATION_STATUS_RECORDED_TIME_IDX" on public."DOCKING_STATION_STATUS" s (cost=0.45..941.02 rows=237 width=23) (actual time=0.025..0.582 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_BIKE_IN_OVERFLOW", s."ELECTRI (...)
  • 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=32
17. 0.463 0.914 ↑ 1.0 1,422 1

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

  • Output: l."CAPACITY", l."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=18
18. 0.451 0.451 ↑ 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.005..0.451 rows=1,422 loops=1)

  • Output: l."CAPACITY", l."SP_ID
  • Buffers: shared hit=18
19. 2.933 28.169 ↓ 1.0 7,742 1

Hash (cost=321.48..321.48 rows=7,600 width=118) (actual time=28.169..28.169 rows=7,742 loops=1)

  • Output: p_1."SP_ID", n."KERNEL_SP_ID", n."NEIGHBOUR_SP_ID
  • Buckets: 8,192 Batches: 1 Memory Usage: 549kB
  • Buffers: shared hit=76
20. 4.972 25.236 ↓ 1.0 7,742 1

Hash Join (cost=60.84..321.48 rows=7,600 width=118) (actual time=7.027..25.236 rows=7,742 loops=1)

  • Output: p_1."SP_ID", n."KERNEL_SP_ID", n."NEIGHBOUR_SP_ID
  • Hash Cond: ((n."NEIGHBOUR_SP_ID")::text = (p_1."SP_ID")::text)
  • Buffers: shared hit=76
21. 19.303 19.303 ↑ 1.0 7,774 1

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

  • Output: n."KERNEL_SP_ID", n."NEIGHBOUR_SP_ID
  • Buffers: shared hit=54
22. 0.454 0.961 ↑ 1.0 1,395 1

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

  • Output: p_1."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 75kB
  • Buffers: shared hit=22
23. 0.507 0.507 ↑ 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.006..0.507 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
24. 0.973 1,210.082 ↑ 16.2 1,390 1

Hash (cost=77,162.82..77,162.82 rows=22,463 width=353) (actual time=1,210.081..1,210.082 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: 505kB
  • Buffers: shared hit=48,960
25. 1.536 1,209.109 ↑ 16.2 1,390 1

Hash Right Join (cost=76,972.35..77,162.82 rows=22,463 width=353) (actual time=1,207.402..1,209.109 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: ((docking_station_recent_traffic_demand."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=48,960
26. 58.292 58.292 ↓ 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=57.984..58.292 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,075
27. 0.896 1,149.281 ↑ 16.2 1,390 1

Hash (cost=76,691.31..76,691.31 rows=22,463 width=289) (actual time=1,149.281..1,149.281 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: 493kB
  • Buffers: shared hit=46,885
28. 1.487 1,148.385 ↑ 16.2 1,390 1

Hash Right Join (cost=76,500.84..76,691.31 rows=22,463 width=289) (actual time=1,146.633..1,148.385 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: ((o."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=46,885
29. 365.668 365.668 ↓ 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=365.359..365.668 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=23,658
30. 1.049 781.230 ↑ 16.2 1,390 1

Hash (cost=76,217.55..76,217.55 rows=22,463 width=285) (actual time=781.230..781.230 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=23,227
31. 0.949 780.181 ↑ 16.2 1,390 1

Hash Left Join (cost=1,228.28..76,217.55 rows=22,463 width=285) (actual time=764.943..780.181 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: ((b."SP_ID")::text = (p."SP_ID")::text)
  • Buffers: shared hit=23,227
32. 1.063 778.336 ↑ 16.2 1,390 1

Hash Join (cost=1,188.29..75,868.69 rows=22,463 width=281) (actual time=764.021..778.336 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" (...)
  • Hash Cond: ((b."SP_ID")::text = (sp."SP_ID")::text)
  • Buffers: shared hit=23,219
33. 0.913 775.904 ↑ 16.2 1,422 1

Hash Right Join (cost=1,113.17..75,482.75 rows=22,980 width=233) (actual time=762.641..775.904 rows=1,422 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", hs."BIKE_HUB_SP_ID", hs."ACTION", d."NUMBER_OF_BIKE", d (...)
  • Hash Cond: ((dst."SP_ID")::text = (b."SP_ID")::text)
  • Buffers: shared hit=23,197
34. 769.029 769.029 ↑ 52.1 441 1

Seq Scan on public."SERVICE_POINT_RUSH_HOUR" dst (cost=0.00..74,053.60 rows=22,980 width=54) (actual time=756.641..769.029 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
  • Filter: (((now())::timestamp without time zone >= dst."START_TIME") AND ((now())::timestamp without time zone <= dst."END_TIME"))
  • Rows Removed by Filter: 2,036,023
  • Buffers: shared hit=23,142
35. 0.705 5.962 ↑ 1.0 1,422 1

Hash (cost=1,095.40..1,095.40 rows=1,422 width=189) (actual time=5.962..5.962 rows=1,422 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", hs."BIKE_HUB_SP_ID", hs."ACTION", d."NUMBER_OF_BI (...)
  • Buckets: 2,048 Batches: 1 Memory Usage: 153kB
  • Buffers: shared hit=55
36. 0.744 5.257 ↑ 1.0 1,422 1

Hash Left Join (cost=134.90..1,095.40 rows=1,422 width=189) (actual time=2.187..5.257 rows=1,422 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", hs."BIKE_HUB_SP_ID", hs."ACTION", d."NUMBER (...)
  • Hash Cond: ((h."BIKE_HUB_SP_ID")::text = (hs."BIKE_HUB_SP_ID")::text)
  • Buffers: shared hit=55
37. 0.770 4.487 ↑ 1.0 1,422 1

Hash Left Join (cost=99.89..1,050.78 rows=1,422 width=131) (actual time=2.150..4.487 rows=1,422 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", d."NUMBER_OF_BIKE (...)
  • Hash Cond: ((b."SP_ID")::text = (a."SP_ID")::text)
  • Buffers: shared hit=54
38. 1.612 3.564 ↑ 1.0 1,422 1

Hash Right Join (cost=92.00..1,035.83 rows=1,422 width=105) (actual time=1.988..3.564 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=50
  • -> Index Scan using "DOCKING_STATION_STATUS_RECORDED_TIME_IDX" on public."DOCKING_STATION_STATUS" d (cost=0.45..941.02 rows=237 width=39) (actual time=0.028..0.507 rows=1,367 (...)
  • 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=32
39. 0.540 1.952 ↑ 1.0 1,422 1

Hash (cost=73.77..73.77 rows=1,422 width=76) (actual time=1.951..1.952 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.386 1.412 ↑ 1.0 1,422 1

Hash Right Join (cost=50.00..73.77 rows=1,422 width=76) (actual time=1.031..1.412 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.004 0.004 ↓ 0.0 0 1

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

  • Output: h."BIKE_HUB_SP_ID", h."DOCK_SP_ID", h."TRANSPORT_CHARACTERISTIC_REF_ID
42. 0.517 1.022 ↑ 1.0 1,422 1

Hash (cost=32.22..32.22 rows=1,422 width=22) (actual time=1.022..1.022 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.505 0.505 ↑ 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.505 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.091 0.153 ↓ 1.0 176 1

Hash (cost=5.73..5.73 rows=173 width=36) (actual time=0.152..0.153 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
45. 0.062 0.062 ↓ 1.0 176 1

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

  • Output: a."RESTRICTION_TYPE", a."START_TIME", a."END_TIME", a."SP_ID
  • Buffers: shared hit=4
46. 0.000 0.026 ↓ 0.0 0 1

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

  • Output: hs."BIKE_HUB_SP_ID", hs."ACTION
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
47. 0.002 0.026 ↓ 0.0 0 1

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

  • Output: hs."BIKE_HUB_SP_ID", hs."ACTION
  • Buffers: shared hit=1
48. 0.007 0.024 ↓ 0.0 0 1

Sort (cost=33.51..33.66 rows=60 width=130) (actual time=0.024..0.024 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=1
49. 0.000 0.017 ↓ 0.0 0 1

Subquery Scan on foo (cost=25.44..31.74 rows=60 width=130) (actual time=0.017..0.017 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
50. 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
51. 0.008 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
52. 0.002 0.008 ↓ 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.008..0.008 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
53. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Index Cond: (h_1."RECORDED_TIME" <= (now())::timestamp without time zone)
  • Buffers: shared hit=1
54. 0.556 1.369 ↑ 1.0 1,395 1

Hash (cost=57.68..57.68 rows=1,395 width=58) (actual time=1.369..1.369 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
55. 0.813 0.813 ↑ 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.013..0.813 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
56. 0.488 0.896 ↑ 1.0 1,422 1

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

  • Output: p."PRIORITY", p."SP_ID
  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=8
57. 0.408 0.408 ↑ 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.011..0.408 rows=1,422 loops=1)

  • Output: p."PRIORITY", p."SP_ID
  • Buffers: shared hit=8
58. 0.495 9.255 ↓ 1,422.0 1,422 1

Hash (cost=1,056.28..1,056.28 rows=1 width=22) (actual time=9.255..9.255 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=22
59. 0.700 8.760 ↓ 1,422.0 1,422 1

Subquery Scan on k (cost=1,047.80..1,056.28 rows=1 width=22) (actual time=6.378..8.760 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=22
60. 1.291 8.060 ↓ 5.4 1,422 1

WindowAgg (cost=1,047.80..1,053.02 rows=261 width=38) (actual time=6.372..8.060 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=22
61. 6.237 6.769 ↓ 5.4 1,422 1

Sort (cost=1,047.80..1,048.45 rows=261 width=30) (actual time=6.363..6.769 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=22
62. 0.380 0.532 ↓ 5.4 1,422 1

Bitmap Heap Scan on public."SIGNAL" d_1 (cost=11.25..1,037.32 rows=261 width=30) (actual time=0.159..0.532 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
  • Recheck Cond: ((d_1."SIGNAL_TIME" > ((now())::timestamp without time zone - '00:03:00'::interval)) AND (d_1."SIGNAL_TIME" <= (now())::timestamp without time zone))
  • Heap Blocks: exact=13
  • Buffers: shared hit=22
63. 0.152 0.152 ↓ 5.4 1,422 1

Bitmap Index Scan on "SIGNAL_SIGNAL_TIME_IDX" (cost=0.00..11.18 rows=261 width=0) (actual time=0.151..0.152 rows=1,422 loops=1)

  • 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=9
Planning time : 6.206 ms
Execution time : 1,276.511 ms