Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE location
(
  location_id INTEGER NOT NULL PRIMARY KEY,
  l_desc      TEXT    NOT NULL
);

CREATE TABLE


INSERT INTO location VALUES (16, 'Farm');

INSERT 0 1


CREATE INDEX l_lid_ix ON location (location_id);

CREATE INDEX


CREATE TABLE sensor
(
  sensor_id	   SMALLINT NOT NULL PRIMARY KEY,
  location_id SMALLINT NOT NULL,
  CONSTRAINT loc_FK FOREIGN KEY (location_id) REFERENCES location (location_id)
);

CREATE TABLE


CREATE INDEX sensor_l_ix ON sensor(location_id);

CREATE INDEX


INSERT INTO sensor VALUES (5, 16);

INSERT 0 1


CREATE TABLE telemetry
(
  t_ts      TIMESTAMPTZ NOT NULL,
  sensor_id INTEGER     NOT NULL,
  t_val     INTEGER     NOT NULL,
  CONSTRAINT tel_pk PRIMARY KEY (sensor_id, t_ts),  -- can only have one (set of) measurements 
                                                    -- at the same time - only t_val
  CONSTRAINT tel_sens_fk FOREIGN KEY (sensor_id) REFERENCES sensor (sensor_id)
);

CREATE TABLE


CREATE INDEX telemetry_s_ix ON telemetry(sensor_id);
CREATE INDEX telemetry_t_ix ON telemetry(t_ts);

CREATE INDEX



CREATE INDEX


INSERT INTO telemetry
SELECT
  ('2022-01-01 00:00:00'::TIMESTAMPTZ + (INTERVAL '1 SECOND' * (i - 1))), 5, i
FROM
(
  SELECT
    GENERATE_SERIES(1, 21700) AS i
) AS t;

INSERT 0 21700


SELECT * FROM telemetry LIMIT 5;


t_ts
sensor_id
t_val


2022-01-01 00:00:00+00
5
1

2022-01-01 00:00:01+00
5
2

2022-01-01 00:00:02+00
5
3

2022-01-01 00:00:03+00
5
4

2022-01-01 00:00:04+00
5
5



SELECT 5


CREATE TABLE status
(
  start_tz  TIMESTAMPTZ NOT NULL,
  end_tz    TIMESTAMPTZ NOT NULL,
  ms        TEXT        NOT NULL,
  sensor_id SMALLINT    NOT NULL,
  CONSTRAINT status_pk PRIMARY KEY (sensor_id, start_tz),
  CONSTRAINT stat_sens_fk FOREIGN KEY (sensor_id) REFERENCES sensor (sensor_id)
);

CREATE TABLE


CREATE INDEX status_s_ix ON status (sensor_id);
CREATE INDEX status_t_ix ON status (start_tz);

CREATE INDEX



CREATE INDEX


INSERT INTO status
SELECT
  GENERATE_SERIES('2022-01-01 00:00:00', '2022-01-01 06:20:00', INTERVAL '20 MINUTES'),
  GENERATE_SERIES('2022-01-01 00:20:00', '2022-01-01 06:40:00', INTERVAL '20 MINUTES'),
  CASE
     WHEN random() > 0.5 THEN 'in_motion)'
    ELSE                     'stationary'
  END,
  5;    -- <<== only one sensor - could have put in another GENERATE_SERIES()

INSERT 0 20


SELECT * FROM status LIMIT 5;


start_tz
end_tz
ms
sensor_id


2022-01-01 00:00:00+00
2022-01-01 00:20:00+00
stationary
5

2022-01-01 00:20:00+00
2022-01-01 00:40:00+00
in_motion)
5

2022-01-01 00:40:00+00
2022-01-01 01:00:00+00
stationary
5

2022-01-01 01:00:00+00
2022-01-01 01:20:00+00
stationary
5

2022-01-01 01:20:00+00
2022-01-01 01:40:00+00
stationary
5



SELECT 5


SELECT MAX(start_tz) FROM status;


max


2022-01-01 06:20:00+00



SELECT 1


CREATE TABLE shift
(
  shift_id     TEXT        NOT NULL,
  location_id  SMALLINT    NOT NULL,
  occurence_id SMALLINT    NOT NULL, 
  start_tz     TIMESTAMPTZ NOT NULL,
  end_tz       TIMESTAMPTZ NOT NULL,
  CONSTRAINT shift_pk PRIMARY KEY (start_tz, location_id),
  CONSTRAINT shift_fk FOREIGN KEY (location_id) REFERENCES location (location_id)
);

CREATE TABLE


WITH cte AS
(
  SELECT
    16 AS loc,
    GENERATE_SERIES(1, 15, 1) AS occur,
    GENERATE_SERIES('2022-01-01 00:00:00', '2022-01-01 07:00:00', INTERVAL '30 MINUTE')
      AS start_tz,
    GENERATE_SERIES('2022-01-01 00:30:00', '2022-01-01 07:30:00', INTERVAL '30 MINUTE')
      AS end_tz
)
INSERT INTO shift
  SELECT
    CASE 
      WHEN EXTRACT (MINUTE FROM start_tz) = 0 THEN 'Shift A'
      ELSE 'Shift B'
    END,
    loc,
    occur,
    start_tz,
    end_tz
  FROM cte;


INSERT 0 15


CREATE INDEX shift_stz_ix ON shift (start_tz);
CREATE INDEX shift_loc_ix ON shift (location_id);

CREATE INDEX



CREATE INDEX


SELECT * FROM shift LIMIT 5;


shift_id
location_id
occurence_id
start_tz
end_tz


Shift A
16
1
2022-01-01 00:00:00+00
2022-01-01 00:30:00+00

Shift B
16
2
2022-01-01 00:30:00+00
2022-01-01 01:00:00+00

Shift A
16
3
2022-01-01 01:00:00+00
2022-01-01 01:30:00+00

Shift B
16
4
2022-01-01 01:30:00+00
2022-01-01 02:00:00+00

Shift A
16
5
2022-01-01 02:00:00+00
2022-01-01 02:30:00+00



SELECT 5


SELECT 
  ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
                              ORDER BY st.start_tz, sh.start_tz) AS rn,

  ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
                              ORDER BY st.start_tz, sh.start_tz) % 4 AS mod,  
  CASE 
    ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
                              ORDER BY st.start_tz, sh.start_tz) % 4
      WHEN 1 THEN  TSTZRANGE(st.start_tz, st.end_tz)
      WHEN 2 THEN  TSTZRANGE(st.start_tz, sh.end_tz)  -- alternates - see TIMEs
      WHEN 3 THEN  TSTZRANGE(sh.start_tz, st.end_tz)  -- alternates - see TIMEs
      WHEN 0 THEN  TSTZRANGE(st.start_tz, st.end_tz)
  END AS range_date,      
  sh.occurence_id,
  st.start_tz::TIME AS st_start, st.end_tz::TIME AS st_end,  -- ::TIME for legibility
  sh.start_tz::TIME AS sh_start, sh.end_tz::TIME AS sh_end
  
FROM sensor s
  JOIN status st USING (sensor_id)
  JOIN shift  sh USING (location_id)
WHERE (st.start_tz, st.end_tz) OVERLAPS (sh.start_tz, sh.end_tz)
ORDER BY rn, mod, range_date;


rn
mod
range_date
occurence_id
st_start
st_end
sh_start
sh_end


1
1
["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00")
1
00:00:00
00:20:00
00:00:00
00:30:00

2
2
["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00")
1
00:20:00
00:40:00
00:00:00
00:30:00

3
3
["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00")
2
00:20:00
00:40:00
00:30:00
01:00:00

4
0
["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00")
2
00:40:00
01:00:00
00:30:00
01:00:00

5
1
["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00")
3
01:00:00
01:20:00
01:00:00
01:30:00

6
2
["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00")
3
01:20:00
01:40:00
01:00:00
01:30:00

7
3
["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00")
4
01:20:00
01:40:00
01:30:00
02:00:00

8
0
["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00")
4
01:40:00
02:00:00
01:30:00
02:00:00

9
1
["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00")
5
02:00:00
02:20:00
02:00:00
02:30:00

10
2
["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00")
5
02:20:00
02:40:00
02:00:00
02:30:00

11
3
["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00")
6
02:20:00
02:40:00
02:30:00
03:00:00

12
0
["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00")
6
02:40:00
03:00:00
02:30:00
03:00:00

13
1
["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00")
7
03:00:00
03:20:00
03:00:00
03:30:00

14
2
["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00")
7
03:20:00
03:40:00
03:00:00
03:30:00

15
3
["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00")
8
03:20:00
03:40:00
03:30:00
04:00:00

16
0
["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00")
8
03:40:00
04:00:00
03:30:00
04:00:00

17
1
["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00")
9
04:00:00
04:20:00
04:00:00
04:30:00

18
2
["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00")
9
04:20:00
04:40:00
04:00:00
04:30:00

19
3
["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00")
10
04:20:00
04:40:00
04:30:00
05:00:00

20
0
["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00")
10
04:40:00
05:00:00
04:30:00
05:00:00

21
1
["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00")
11
05:00:00
05:20:00
05:00:00
05:30:00

22
2
["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00")
11
05:20:00
05:40:00
05:00:00
05:30:00

23
3
["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00")
12
05:20:00
05:40:00
05:30:00
06:00:00

24
0
["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00")
12
05:40:00
06:00:00
05:30:00
06:00:00

25
1
["2022-01-01 06:00:00+00","2022-01-01 06:20:00+00")
13
06:00:00
06:20:00
06:00:00
06:30:00

26
2
["2022-01-01 06:20:00+00","2022-01-01 06:30:00+00")
13
06:20:00
06:40:00
06:00:00
06:30:00

27
3
["2022-01-01 06:30:00+00","2022-01-01 06:40:00+00")
14
06:20:00
06:40:00
06:30:00
07:00:00



SELECT 27


SELECT
  t.sensor_id AS s_id, sub.location_id AS loc_id, 
  sub.t_val AS "S_val", t.t_val AS "E_val", 
  sub.range_date, sub.occurence_id AS oc_id, sub.ms   -- can have other fields...
FROM
  telemetry t
JOIN
(  
  SELECT 
    t.sensor_id, t.t_val,
    sh.occurence_id, sh.location_id, st.ms,
    ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
      ORDER BY st.start_tz, sh.start_tz) % 4 AS rn,
  
    CASE 
      ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
                             ORDER BY st.start_tz, sh.start_tz) % 4
        WHEN 1 THEN  TSTZRANGE(st.start_tz, st.end_tz)
        WHEN 2 THEN  TSTZRANGE(st.start_tz, sh.end_tz)
        WHEN 3 THEN  TSTZRANGE(sh.start_tz, st.end_tz)
        WHEN 0 THEN  TSTZRANGE(st.start_tz, st.end_tz)
    END AS range_date     
                
FROM sensor s
  JOIN status    st USING (sensor_id)
  JOIN shift     sh USING (location_id)
  JOIN telemetry t  USING (sensor_id)
WHERE (st.start_tz, st.end_tz) OVERLAPS (sh.start_tz, sh.end_tz)
  AND t.t_ts = CASE
                  WHEN sh.start_tz > st.start_tz THEN sh.start_tz
                  ELSE st.start_tz
                END                 -- <<=== Note JOIN on CASE!
) AS sub
ON t.t_ts = UPPER(range_date) - INTERVAL '1 SECOND'
ORDER BY range_date;


s_id
loc_id
S_val
E_val
range_date
oc_id
ms


5
16
1
1200
["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00")
1
stationary

5
16
1201
1800
["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00")
1
in_motion)

5
16
1801
2400
["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00")
2
in_motion)

5
16
2401
3600
["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00")
2
stationary

5
16
3601
4800
["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00")
3
stationary

5
16
4801
5400
["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00")
3
stationary

5
16
5401
6000
["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00")
4
stationary

5
16
6001
7200
["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00")
4
stationary

5
16
7201
8400
["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00")
5
in_motion)

5
16
8401
9000
["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00")
5
in_motion)

5
16
9001
9600
["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00")
6
in_motion)

5
16
9601
10800
["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00")
6
stationary

5
16
10801
12000
["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00")
7
in_motion)

5
16
12001
12600
["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00")
7
in_motion)

5
16
12601
13200
["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00")
8
in_motion)

5
16
13201
14400
["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00")
8
in_motion)

5
16
14401
15600
["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00")
9
in_motion)

5
16
15601
16200
["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00")
9
stationary

5
16
16201
16800
["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00")
10
stationary

5
16
16801
18000
["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00")
10
stationary

5
16
18001
19200
["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00")
11
in_motion)

5
16
19201
19800
["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00")
11
stationary

5
16
19801
20400
["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00")
12
stationary

5
16
20401
21600
["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00")
12
in_motion)



SELECT 24


--
--
-- Performance Analysis
--
--
SET enable_seqscan = off;
SET enable_nestloop = off;

SET



SET


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING)
SELECT
  t.sensor_id AS s_id, sub.location_id AS loc_id, 
  sub.t_val AS "S_val", t.t_val AS "E_val", 
  sub.range_date, sub.occurence_id AS oc_id, sub.ms   -- can have other fields...
FROM
  telemetry t
JOIN
(  
  SELECT 
    t.sensor_id, t.t_val,
    sh.occurence_id, sh.location_id, st.ms,
    ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
      ORDER BY st.start_tz, sh.start_tz) % 4 AS rn,
  
    CASE 
      ROW_NUMBER() OVER (PARTITION BY s.sensor_id, s.location_id 
                             ORDER BY st.start_tz, sh.start_tz) % 4
        WHEN 1 THEN  TSTZRANGE(st.start_tz, st.end_tz)
        WHEN 2 THEN  TSTZRANGE(st.start_tz, sh.end_tz)
        WHEN 3 THEN  TSTZRANGE(sh.start_tz, st.end_tz)
        WHEN 0 THEN  TSTZRANGE(st.start_tz, st.end_tz)
    END AS range_date     
                
FROM sensor s
  JOIN status    st USING (sensor_id)
  JOIN shift     sh USING (location_id)
  JOIN telemetry t  USING (sensor_id)
WHERE (st.start_tz, st.end_tz) OVERLAPS (sh.start_tz, sh.end_tz)
  AND t.t_ts = CASE
                  WHEN sh.start_tz > st.start_tz THEN sh.start_tz
                  ELSE st.start_tz
                END                 -- <<=== Note JOIN on CASE!
) AS sub
ON t.t_ts = UPPER(range_date) - INTERVAL '1 SECOND'
ORDER BY range_date;


QUERY PLAN


Sort  (cost=2311.38..2311.92 rows=218 width=80) (actual time=161.350..161.356 rows=24 loops=1)

Output: t.sensor_id, sub.location_id, sub.t_val, t.t_val, sub.range_date, sub.occurence_id, sub.ms

Sort Key: sub.range_date

Sort Method: quicksort  Memory: 28kB

Buffers: shared hit=2138 read=1

->  Hash Join  (cost=1116.84..2302.91 rows=218 width=80) (actual time=156.278..161.327 rows=24 loops=1)

Output: t.sensor_id, sub.location_id, sub.t_val, t.t_val, sub.range_date, sub.occurence_id, sub.ms

Hash Cond: (t.t_ts = (upper(sub.range_date) - '00:00:01'::interval))

Buffers: shared hit=2138 read=1

->  Index Scan using telemetry_t_ix on public.telemetry t  (cost=0.29..1047.74 rows=21830 width=16) (actual time=0.028..3.191 rows=21700 loops=1)

Output: t.t_ts, t.sensor_id, t.t_val

Buffers: shared hit=179

->  Hash  (cost=1116.53..1116.53 rows=2 width=72) (actual time=155.928..155.931 rows=25 loops=1)

Output: sub.location_id, sub.t_val, sub.range_date, sub.occurence_id, sub.ms

Buckets: 1024  Batches: 1  Memory Usage: 10kB

Buffers: shared hit=1959 read=1

->  Subquery Scan on sub  (cost=615.56..1116.53 rows=2 width=72) (actual time=155.854..155.900 rows=25 loops=1)

Output: sub.location_id, sub.t_val, sub.range_date, sub.occurence_id, sub.ms

Buffers: shared hit=1959 read=1

->  WindowAgg  (cost=615.56..1116.51 rows=2 width=104) (actual time=155.853..155.894 rows=25 loops=1)

Output: NULL::integer, t_1.t_val, sh.occurence_id, sh.location_id, st.ms, NULL::bigint, CASE (row_number() OVER (?) % '4'::bigint) WHEN 1 THEN tstzrange(st.start_tz, st.end_tz) WHEN 2 THEN tstzrange(st.start_tz, sh.end_tz) WHEN 3 THEN tstzrange(sh.start_tz, st.end_tz) WHEN 0 THEN tstzrange(st.start_tz, st.end_tz) ELSE NULL::tstzrange END, st.start_tz, sh.start_tz, s.sensor_id, s.location_id

Buffers: shared hit=1959 read=1

->  Incremental Sort  (cost=615.56..1116.42 rows=2 width=76) (actual time=155.835..155.840 rows=25 loops=1)

Output: st.start_tz, sh.start_tz, s.sensor_id, s.location_id, t_1.t_val, sh.occurence_id, sh.location_id, st.ms, st.end_tz, sh.end_tz

Sort Key: s.sensor_id, s.location_id, st.start_tz, sh.start_tz

Presorted Key: s.sensor_id

Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

Buffers: shared hit=1959 read=1

->  Merge Join  (cost=114.76..1116.33 rows=1 width=76) (actual time=0.113..155.782 rows=25 loops=1)

Output: st.start_tz, sh.start_tz, s.sensor_id, s.location_id, t_1.t_val, sh.occurence_id, sh.location_id, st.ms, st.end_tz, sh.end_tz

Inner Unique: true

Merge Cond: (s.sensor_id = t_1.sensor_id)

Join Filter: (CASE WHEN (sh.start_tz > st.start_tz) THEN sh.start_tz ELSE st.start_tz END = t_1.t_ts)

Rows Removed by Join Filter: 313400

Buffers: shared hit=1959 read=1

->  Merge Join  (cost=114.48..182.61 rows=26 width=74) (actual time=0.105..0.246 rows=27 loops=1)

Output: s.sensor_id, s.location_id, st.ms, st.start_tz, st.end_tz, st.sensor_id, sh.occurence_id, sh.location_id, sh.end_tz, sh.start_tz

Merge Cond: (s.sensor_id = st.sensor_id)

Join Filter: ((st.start_tz, st.end_tz) OVERLAPS (sh.start_tz, sh.end_tz))

Rows Removed by Join Filter: 273

Buffers: shared hit=19 read=1

->  Sort  (cost=114.32..114.80 rows=191 width=24) (actual time=0.089..0.096 rows=15 loops=1)

Output: s.sensor_id, s.location_id, sh.occurence_id, sh.location_id, sh.end_tz, sh.start_tz

Sort Key: s.sensor_id

Sort Method: quicksort  Memory: 26kB

Buffers: shared hit=3 read=1

->  Merge Join  (cost=0.29..107.09 rows=191 width=24) (actual time=0.055..0.065 rows=15 loops=1)

Output: s.sensor_id, s.location_id, sh.occurence_id, sh.location_id, sh.end_tz, sh.start_tz

Merge Cond: (sh.location_id = s.location_id)

Buffers: shared hit=3 read=1

->  Index Scan using shift_loc_ix on public.shift sh  (cost=0.14..12.36 rows=15 width=20) (actual time=0.032..0.035 rows=15 loops=1)

Output: sh.shift_id, sh.location_id, sh.occurence_id, sh.start_tz, sh.end_tz

Buffers: shared hit=1 read=1

->  Index Scan using sensor_l_ix on public.sensor s  (cost=0.15..86.41 rows=2550 width=4) (actual time=0.018..0.020 rows=1 loops=1)

Output: s.sensor_id, s.location_id

Buffers: shared hit=2

->  Index Scan using status_s_ix on public.status st  (cost=0.15..63.75 rows=1040 width=50) (actual time=0.013..0.072 rows=286 loops=1)

Output: st.start_tz, st.end_tz, st.ms, st.sensor_id

Buffers: shared hit=16

->  Index Scan using telemetry_s_ix on public.telemetry t_1  (cost=0.29..875.74 rows=21830 width=16) (actual time=0.005..87.169 rows=313399 loops=1)

Output: t_1.t_ts, t_1.sensor_id, t_1.t_val

Buffers: shared hit=1940

Planning Time: 0.816 ms

Execution Time: 161.466 ms



EXPLAIN

t_ts	sensor_id	t_val
2022-01-01 00:00:00+00	5	1
2022-01-01 00:00:01+00	5	2
2022-01-01 00:00:02+00	5	3
2022-01-01 00:00:03+00	5	4
2022-01-01 00:00:04+00	5	5

start_tz	end_tz	ms	sensor_id
2022-01-01 00:00:00+00	2022-01-01 00:20:00+00	stationary	5
2022-01-01 00:20:00+00	2022-01-01 00:40:00+00	in_motion)	5
2022-01-01 00:40:00+00	2022-01-01 01:00:00+00	stationary	5
2022-01-01 01:00:00+00	2022-01-01 01:20:00+00	stationary	5
2022-01-01 01:20:00+00	2022-01-01 01:40:00+00	stationary	5

shift_id	location_id	occurence_id	start_tz	end_tz
Shift A	16	1	2022-01-01 00:00:00+00	2022-01-01 00:30:00+00
Shift B	16	2	2022-01-01 00:30:00+00	2022-01-01 01:00:00+00
Shift A	16	3	2022-01-01 01:00:00+00	2022-01-01 01:30:00+00
Shift B	16	4	2022-01-01 01:30:00+00	2022-01-01 02:00:00+00
Shift A	16	5	2022-01-01 02:00:00+00	2022-01-01 02:30:00+00

rn	mod	range_date	occurence_id	st_start	st_end	sh_start	sh_end
1	1	["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00")	1	00:00:00	00:20:00	00:00:00	00:30:00
2	2	["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00")	1	00:20:00	00:40:00	00:00:00	00:30:00
3	3	["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00")	2	00:20:00	00:40:00	00:30:00	01:00:00
4	0	["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00")	2	00:40:00	01:00:00	00:30:00	01:00:00
5	1	["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00")	3	01:00:00	01:20:00	01:00:00	01:30:00
6	2	["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00")	3	01:20:00	01:40:00	01:00:00	01:30:00
7	3	["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00")	4	01:20:00	01:40:00	01:30:00	02:00:00
8	0	["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00")	4	01:40:00	02:00:00	01:30:00	02:00:00
9	1	["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00")	5	02:00:00	02:20:00	02:00:00	02:30:00
10	2	["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00")	5	02:20:00	02:40:00	02:00:00	02:30:00
11	3	["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00")	6	02:20:00	02:40:00	02:30:00	03:00:00
12	0	["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00")	6	02:40:00	03:00:00	02:30:00	03:00:00
13	1	["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00")	7	03:00:00	03:20:00	03:00:00	03:30:00
14	2	["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00")	7	03:20:00	03:40:00	03:00:00	03:30:00
15	3	["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00")	8	03:20:00	03:40:00	03:30:00	04:00:00
16	0	["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00")	8	03:40:00	04:00:00	03:30:00	04:00:00
17	1	["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00")	9	04:00:00	04:20:00	04:00:00	04:30:00
18	2	["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00")	9	04:20:00	04:40:00	04:00:00	04:30:00
19	3	["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00")	10	04:20:00	04:40:00	04:30:00	05:00:00
20	0	["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00")	10	04:40:00	05:00:00	04:30:00	05:00:00
21	1	["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00")	11	05:00:00	05:20:00	05:00:00	05:30:00
22	2	["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00")	11	05:20:00	05:40:00	05:00:00	05:30:00
23	3	["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00")	12	05:20:00	05:40:00	05:30:00	06:00:00
24	0	["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00")	12	05:40:00	06:00:00	05:30:00	06:00:00
25	1	["2022-01-01 06:00:00+00","2022-01-01 06:20:00+00")	13	06:00:00	06:20:00	06:00:00	06:30:00
26	2	["2022-01-01 06:20:00+00","2022-01-01 06:30:00+00")	13	06:20:00	06:40:00	06:00:00	06:30:00
27	3	["2022-01-01 06:30:00+00","2022-01-01 06:40:00+00")	14	06:20:00	06:40:00	06:30:00	07:00:00

s_id	loc_id	S_val	E_val	range_date	oc_id	ms
5	16	1	1200	["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00")	1	stationary
5	16	1201	1800	["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00")	1	in_motion)
5	16	1801	2400	["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00")	2	in_motion)
5	16	2401	3600	["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00")	2	stationary
5	16	3601	4800	["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00")	3	stationary
5	16	4801	5400	["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00")	3	stationary
5	16	5401	6000	["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00")	4	stationary
5	16	6001	7200	["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00")	4	stationary
5	16	7201	8400	["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00")	5	in_motion)
5	16	8401	9000	["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00")	5	in_motion)
5	16	9001	9600	["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00")	6	in_motion)
5	16	9601	10800	["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00")	6	stationary
5	16	10801	12000	["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00")	7	in_motion)
5	16	12001	12600	["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00")	7	in_motion)
5	16	12601	13200	["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00")	8	in_motion)
5	16	13201	14400	["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00")	8	in_motion)
5	16	14401	15600	["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00")	9	in_motion)
5	16	15601	16200	["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00")	9	stationary
5	16	16201	16800	["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00")	10	stationary
5	16	16801	18000	["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00")	10	stationary
5	16	18001	19200	["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00")	11	in_motion)
5	16	19201	19800	["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00")	11	stationary
5	16	19801	20400	["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00")	12	stationary
5	16	20401	21600	["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00")	12	in_motion)