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
in_motion)
5

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



SELECT 5


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


WITH cte1 AS
(
SELECT
  t1.t_ts, t1.t_val, t1.sensor_id,
  ROW_NUMBER() OVER (ORDER BY t1.t_ts) AS rn1
FROM 
  telemetry t1
WHERE 
     EXTRACT('MINUTE' FROM t1.t_ts) =  0 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 20 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 30 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 40 AND EXTRACT('SECOND' FROM t1.t_ts) = 0 
UNION ALL -- because there can't be any duplicates, so no need to sort and filter!
SELECT
  t2.t_ts, t2.t_val, t2.sensor_id,
  ROW_NUMBER() OVER (ORDER BY t2.t_ts) AS rn2
FROM 
  telemetry t2
WHERE 
     EXTRACT('MINUTE' FROM t2.t_ts) = 19 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 29 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 39 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 59 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  ORDER BY t_ts
),
cte2 AS
(
  SELECT 
    cx.sensor_id AS sid, cx.t_ts AS t1, cy.t_ts AS t2, cx.t_val AS sv, cy.t_val AS ev
  FROM
    cte1 AS cx
  JOIN cte1 AS cy
    ON cx.rn1 = cy.rn1 AND cx.t_ts != cy.t_ts AND cx.t_val < cy.t_val
)
SELECT * FROM cte2


sid
t1
t2
sv
ev


5
2022-01-01 00:00:00+00
2022-01-01 00:19:59+00
1
1200

5
2022-01-01 00:20:00+00
2022-01-01 00:29:59+00
1201
1800

5
2022-01-01 00:30:00+00
2022-01-01 00:39:59+00
1801
2400

5
2022-01-01 00:40:00+00
2022-01-01 00:59:59+00
2401
3600

5
2022-01-01 01:00:00+00
2022-01-01 01:19:59+00
3601
4800

5
2022-01-01 01:20:00+00
2022-01-01 01:29:59+00
4801
5400

5
2022-01-01 01:30:00+00
2022-01-01 01:39:59+00
5401
6000

5
2022-01-01 01:40:00+00
2022-01-01 01:59:59+00
6001
7200

5
2022-01-01 02:00:00+00
2022-01-01 02:19:59+00
7201
8400

5
2022-01-01 02:20:00+00
2022-01-01 02:29:59+00
8401
9000

5
2022-01-01 02:30:00+00
2022-01-01 02:39:59+00
9001
9600

5
2022-01-01 02:40:00+00
2022-01-01 02:59:59+00
9601
10800

5
2022-01-01 03:00:00+00
2022-01-01 03:19:59+00
10801
12000

5
2022-01-01 03:20:00+00
2022-01-01 03:29:59+00
12001
12600

5
2022-01-01 03:30:00+00
2022-01-01 03:39:59+00
12601
13200

5
2022-01-01 03:40:00+00
2022-01-01 03:59:59+00
13201
14400

5
2022-01-01 04:00:00+00
2022-01-01 04:19:59+00
14401
15600

5
2022-01-01 04:20:00+00
2022-01-01 04:29:59+00
15601
16200

5
2022-01-01 04:30:00+00
2022-01-01 04:39:59+00
16201
16800

5
2022-01-01 04:40:00+00
2022-01-01 04:59:59+00
16801
18000

5
2022-01-01 05:00:00+00
2022-01-01 05:19:59+00
18001
19200

5
2022-01-01 05:20:00+00
2022-01-01 05:29:59+00
19201
19800

5
2022-01-01 05:30:00+00
2022-01-01 05:39:59+00
19801
20400

5
2022-01-01 05:40:00+00
2022-01-01 05:59:59+00
20401
21600



SELECT 24


WITH cte1 AS
(
SELECT
  t1.t_ts, t1.t_val, t1.sensor_id,
  ROW_NUMBER() OVER (PARTITION BY t1.sensor_id ORDER BY t1.t_ts) AS rn1
FROM 
  telemetry t1
WHERE 
     EXTRACT('MINUTE' FROM t1.t_ts) =  0 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 20 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 30 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 40 AND EXTRACT('SECOND' FROM t1.t_ts) = 0 
UNION ALL -- because there can't be any duplicates, so no need to sort and filter!
SELECT
  t2.t_ts, t2.t_val, t2.sensor_id,
  ROW_NUMBER() OVER (PARTITION BY t2.sensor_id ORDER BY t2.t_ts) AS rn2
FROM 
  telemetry t2
WHERE 
     EXTRACT('MINUTE' FROM t2.t_ts) = 19 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 29 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 39 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 59 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  ORDER BY t_ts
),
cte2 AS
(
  SELECT 
    cx.rn1, cx.sensor_id AS sid, cx.t_ts AS t1, cy.t_ts AS t2, cx.t_val AS sv, cy.t_val AS ev
  FROM
    cte1 AS cx
  JOIN cte1 AS cy
    ON cx.rn1 = cy.rn1 AND cx.t_ts != cy.t_ts AND cx.t_val < cy.t_val
)

SELECT
  st.sensor_id AS sid, sh.location_id AS lid, cte2.rn1, -- rn1 not strictly necessary - left in to demonstrate!

  CASE rn1 % 2 
    WHEN 1 THEN sh.start_tz
    ELSE        st.start_tz
  END AS t1,

  CASE rn1 % 2 
    WHEN 1 THEN st.start_tz
    ELSE        sh.end_tz
  END AS t2,  
  
  sh.occurence_id AS oc_id, st.ms, 
  sh.shift_id AS sh_id 
  
FROM
  shift sh
JOIN status st
  ON (sh.start_tz, sh.end_tz) OVERLAPS (st.start_tz, sh.end_tz)
JOIN cte2
  ON st.sensor_id = cte2.sid
WHERE sh.start_tz <= st.start_tz AND sh.end_tz <= st.end_tz 
AND (cte2.t1, cte2.t2) OVERLAPS (sh.start_tz, sh.end_tz) 
ORDER BY sh.start_tz, sh.end_tz;


sid
lid
rn1
t1
t2
oc_id
ms
sh_id


5
16
1
2022-01-01 00:00:00+00
2022-01-01 00:20:00+00
1
in_motion)
Shift A

5
16
2
2022-01-01 00:20:00+00
2022-01-01 00:30:00+00
1
in_motion)
Shift A

5
16
3
2022-01-01 00:30:00+00
2022-01-01 00:40:00+00
2
stationary
Shift B

5
16
4
2022-01-01 00:40:00+00
2022-01-01 01:00:00+00
2
stationary
Shift B

5
16
5
2022-01-01 01:00:00+00
2022-01-01 01:20:00+00
3
stationary
Shift A

5
16
6
2022-01-01 01:20:00+00
2022-01-01 01:30:00+00
3
stationary
Shift A

5
16
7
2022-01-01 01:30:00+00
2022-01-01 01:40:00+00
4
stationary
Shift B

5
16
8
2022-01-01 01:40:00+00
2022-01-01 02:00:00+00
4
stationary
Shift B

5
16
9
2022-01-01 02:00:00+00
2022-01-01 02:20:00+00
5
stationary
Shift A

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

5
16
11
2022-01-01 02:30:00+00
2022-01-01 02:40:00+00
6
stationary
Shift B

5
16
12
2022-01-01 02:40:00+00
2022-01-01 03:00:00+00
6
stationary
Shift B

5
16
13
2022-01-01 03:00:00+00
2022-01-01 03:20:00+00
7
stationary
Shift A

5
16
14
2022-01-01 03:20:00+00
2022-01-01 03:30:00+00
7
stationary
Shift A

5
16
15
2022-01-01 03:30:00+00
2022-01-01 03:40:00+00
8
stationary
Shift B

5
16
16
2022-01-01 03:40:00+00
2022-01-01 04:00:00+00
8
stationary
Shift B

5
16
17
2022-01-01 04:00:00+00
2022-01-01 04:20:00+00
9
stationary
Shift A

5
16
18
2022-01-01 04:20:00+00
2022-01-01 04:30:00+00
9
stationary
Shift A

5
16
19
2022-01-01 04:30:00+00
2022-01-01 04:40:00+00
10
stationary
Shift B

5
16
20
2022-01-01 04:40:00+00
2022-01-01 05:00:00+00
10
stationary
Shift B

5
16
21
2022-01-01 05:00:00+00
2022-01-01 05:20:00+00
11
in_motion)
Shift A

5
16
22
2022-01-01 05:20:00+00
2022-01-01 05:30:00+00
11
in_motion)
Shift A

5
16
23
2022-01-01 05:30:00+00
2022-01-01 05:40:00+00
12
in_motion)
Shift B

5
16
24
2022-01-01 05:40:00+00
2022-01-01 06:00:00+00
12
in_motion)
Shift B



SELECT 24


EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
WITH cte1 AS
(
SELECT
  t1.t_ts, t1.t_val, t1.sensor_id,
  ROW_NUMBER() OVER (PARTITION BY t1.sensor_id ORDER BY t1.t_ts) AS rn1
FROM 
  telemetry t1
WHERE 
     EXTRACT('MINUTE' FROM t1.t_ts) =  0 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 20 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 30 AND EXTRACT('SECOND' FROM t1.t_ts) = 0
  OR EXTRACT('MINUTE' FROM t1.t_ts) = 40 AND EXTRACT('SECOND' FROM t1.t_ts) = 0 
UNION ALL -- because there can't be any duplicates, so no need to sort and filter!
SELECT
  t2.t_ts, t2.t_val, t2.sensor_id,
  ROW_NUMBER() OVER (PARTITION BY t2.sensor_id ORDER BY t2.t_ts) AS rn2
FROM 
  telemetry t2
WHERE 
     EXTRACT('MINUTE' FROM t2.t_ts) = 19 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 29 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 39 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  OR EXTRACT('MINUTE' FROM t2.t_ts) = 59 AND EXTRACT('SECOND' FROM t2.t_ts) = 59
  ORDER BY t_ts
),
cte2 AS
(
  SELECT 
    cx.rn1, cx.sensor_id AS sid, cx.t_ts AS t1, cy.t_ts AS t2, cx.t_val AS sv, cy.t_val AS ev
  FROM
    cte1 AS cx
  JOIN cte1 AS cy
    ON cx.rn1 = cy.rn1 AND cx.t_ts != cy.t_ts AND cx.t_val < cy.t_val
)

SELECT
  st.sensor_id AS sid, sh.location_id AS lid, cte2.rn1, -- rn1 not strictly necessary - left in to demonstrate!

  CASE rn1 % 2 
    WHEN 1 THEN sh.start_tz
    ELSE        st.start_tz
  END AS t1,

  CASE rn1 % 2 
    WHEN 1 THEN st.start_tz
    ELSE        sh.end_tz
  END AS t2,  
  
  sh.occurence_id AS oc_id, st.ms, 
  sh.shift_id AS sh_id 
  
FROM
  shift sh
JOIN status st
  ON (sh.start_tz, sh.end_tz) OVERLAPS (st.start_tz, sh.end_tz)
JOIN cte2
  ON st.sensor_id = cte2.sid
WHERE sh.start_tz <= st.start_tz AND sh.end_tz <= st.end_tz 
AND (cte2.t1, cte2.t2) OVERLAPS (sh.start_tz, sh.end_tz) 
ORDER BY sh.start_tz, sh.end_tz;


QUERY PLAN


Sort  (cost=1775.35..1775.35 rows=1 width=110) (actual time=27.250..27.254 rows=24 loops=1)

  Output: st.sensor_id, sh.location_id, cx.rn1, (CASE (cx.rn1 % '2'::bigint) WHEN 1 THEN sh.start_tz ELSE st.start_tz END), (CASE (cx.rn1 % '2'::bigint) WHEN 1 THEN st.start_tz ELSE sh.end_tz END), sh.occurence_id, st.ms, sh.shift_id, sh.start_tz, sh.end_tz

  Sort Key: sh.start_tz, sh.end_tz

  Sort Method: quicksort  Memory: 28kB

  Buffers: shared hit=1244

  CTE cte1

    ->  Sort  (cost=1764.30..1764.31 rows=4 width=24) (actual time=25.541..25.546 rows=49 loops=1)

          Output: t1.t_ts, t1.t_val, t1.sensor_id, (row_number() OVER (?))

          Sort Key: t1.t_ts

          Sort Method: quicksort  Memory: 28kB

          Buffers: shared hit=236

          ->  Append  (cost=882.06..1764.26 rows=4 width=24) (actual time=7.261..25.522 rows=49 loops=1)

                Buffers: shared hit=236

                ->  WindowAgg  (cost=882.06..882.10 rows=2 width=24) (actual time=7.260..7.274 rows=25 loops=1)

                      Output: t1.t_ts, t1.t_val, t1.sensor_id, row_number() OVER (?)

                      Buffers: shared hit=118

                      ->  Sort  (cost=882.06..882.07 rows=2 width=16) (actual time=7.246..7.249 rows=25 loops=1)

                            Output: t1.t_ts, t1.sensor_id, t1.t_val

                            Sort Key: t1.sensor_id, t1.t_ts

                            Sort Method: quicksort  Memory: 26kB

                            Buffers: shared hit=118

                            ->  Seq Scan on public.telemetry t1  (cost=0.00..882.05 rows=2 width=16) (actual time=0.016..7.225 rows=25 loops=1)

                                  Output: t1.t_ts, t1.sensor_id, t1.t_val

                                  Filter: ((EXTRACT(SECOND FROM t1.t_ts) = '0'::numeric) AND ((EXTRACT(MINUTE FROM t1.t_ts) = '0'::numeric) OR (EXTRACT(MINUTE FROM t1.t_ts) = '20'::numeric) OR (EXTRACT(MINUTE FROM t1.t_ts) = '30'::numeric) OR (EXTRACT(MINUTE FROM t1.t_ts) = '40'::numeric)))

                                  Rows Removed by Filter: 21675

                                  Buffers: shared hit=118

                ->  WindowAgg  (cost=882.06..882.10 rows=2 width=24) (actual time=18.228..18.241 rows=24 loops=1)

                      Output: t2.t_ts, t2.t_val, t2.sensor_id, row_number() OVER (?)

                      Buffers: shared hit=118

                      ->  Sort  (cost=882.06..882.07 rows=2 width=16) (actual time=18.219..18.221 rows=24 loops=1)

                            Output: t2.t_ts, t2.sensor_id, t2.t_val

                            Sort Key: t2.sensor_id, t2.t_ts

                            Sort Method: quicksort  Memory: 26kB

                            Buffers: shared hit=118

                            ->  Seq Scan on public.telemetry t2  (cost=0.00..882.05 rows=2 width=16) (actual time=0.399..18.193 rows=24 loops=1)

                                  Output: t2.t_ts, t2.sensor_id, t2.t_val

                                  Filter: ((EXTRACT(SECOND FROM t2.t_ts) = '59'::numeric) AND ((EXTRACT(MINUTE FROM t2.t_ts) = '19'::numeric) OR (EXTRACT(MINUTE FROM t2.t_ts) = '29'::numeric) OR (EXTRACT(MINUTE FROM t2.t_ts) = '39'::numeric) OR (EXTRACT(MINUTE FROM t2.t_ts) = '59'::numeric)))

                                  Rows Removed by Filter: 21676

                                  Buffers: shared hit=118

  ->  Nested Loop  (cost=2.46..11.03 rows=1 width=110) (actual time=25.633..27.224 rows=24 loops=1)

        Output: st.sensor_id, sh.location_id, cx.rn1, CASE (cx.rn1 % '2'::bigint) WHEN 1 THEN sh.start_tz ELSE st.start_tz END, CASE (cx.rn1 % '2'::bigint) WHEN 1 THEN st.start_tz ELSE sh.end_tz END, sh.occurence_id, st.ms, sh.shift_id, sh.start_tz, sh.end_tz

        Join Filter: ((cx.t_ts, cy.t_ts) OVERLAPS (sh.start_tz, sh.end_tz))

        Rows Removed by Join Filter: 288

        Buffers: shared hit=1244

        ->  Nested Loop  (cost=2.32..9.66 rows=5 width=74) (actual time=25.613..25.989 rows=480 loops=1)

              Output: st.sensor_id, st.start_tz, st.ms, st.end_tz, cx.rn1, cx.t_ts, cy.t_ts

              Buffers: shared hit=284

              ->  Hash Join  (cost=0.13..0.29 rows=1 width=28) (actual time=25.592..25.634 rows=24 loops=1)

                    Output: cx.rn1, cx.t_ts, cx.sensor_id, cy.t_ts

                    Hash Cond: (cx.rn1 = cy.rn1)

                    Join Filter: ((cx.t_ts <> cy.t_ts) AND (cx.t_val < cy.t_val))

                    Rows Removed by Join Filter: 73

                    Buffers: shared hit=236

                    ->  CTE Scan on cte1 cx  (cost=0.00..0.08 rows=4 width=24) (actual time=25.542..25.550 rows=49 loops=1)

                          Output: cx.t_ts, cx.t_val, cx.sensor_id, cx.rn1

                          Buffers: shared hit=236

                    ->  Hash  (cost=0.08..0.08 rows=4 width=20) (actual time=0.031..0.032 rows=49 loops=1)

                          Output: cy.rn1, cy.t_ts, cy.t_val

                          Buckets: 1024  Batches: 1  Memory Usage: 11kB

                          ->  CTE Scan on cte1 cy  (cost=0.00..0.08 rows=4 width=20) (actual time=0.001..0.015 rows=49 loops=1)

                                Output: cy.rn1, cy.t_ts, cy.t_val

              ->  Bitmap Heap Scan on public.status st  (cost=2.19..9.32 rows=5 width=50) (actual time=0.006..0.008 rows=20 loops=24)

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

                    Recheck Cond: (st.sensor_id = cx.sensor_id)

                    Heap Blocks: exact=24

                    Buffers: shared hit=48

                    ->  Bitmap Index Scan on status_s_ix  (cost=0.00..2.19 rows=5 width=0) (actual time=0.003..0.003 rows=20 loops=24)

                          Index Cond: (st.sensor_id = cx.sensor_id)

                          Buffers: shared hit=24

        ->  Index Scan using shift_stz_ix on public.shift sh  (cost=0.14..0.26 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=480)

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

              Index Cond: (sh.start_tz <= st.start_tz)

              Filter: (((sh.start_tz, sh.end_tz) OVERLAPS (st.start_tz, sh.end_tz)) AND (sh.end_tz <= st.end_tz))

              Rows Removed by Filter: 6

              Buffers: shared hit=960

Planning Time: 0.650 ms

Execution Time: 27.337 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	in_motion)	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
sid	t1	t2	sv	ev
5	2022-01-01 00:00:00+00	2022-01-01 00:19:59+00	1	1200
5	2022-01-01 00:20:00+00	2022-01-01 00:29:59+00	1201	1800
5	2022-01-01 00:30:00+00	2022-01-01 00:39:59+00	1801	2400
5	2022-01-01 00:40:00+00	2022-01-01 00:59:59+00	2401	3600
5	2022-01-01 01:00:00+00	2022-01-01 01:19:59+00	3601	4800
5	2022-01-01 01:20:00+00	2022-01-01 01:29:59+00	4801	5400
5	2022-01-01 01:30:00+00	2022-01-01 01:39:59+00	5401	6000
5	2022-01-01 01:40:00+00	2022-01-01 01:59:59+00	6001	7200
5	2022-01-01 02:00:00+00	2022-01-01 02:19:59+00	7201	8400
5	2022-01-01 02:20:00+00	2022-01-01 02:29:59+00	8401	9000
5	2022-01-01 02:30:00+00	2022-01-01 02:39:59+00	9001	9600
5	2022-01-01 02:40:00+00	2022-01-01 02:59:59+00	9601	10800
5	2022-01-01 03:00:00+00	2022-01-01 03:19:59+00	10801	12000
5	2022-01-01 03:20:00+00	2022-01-01 03:29:59+00	12001	12600
5	2022-01-01 03:30:00+00	2022-01-01 03:39:59+00	12601	13200
5	2022-01-01 03:40:00+00	2022-01-01 03:59:59+00	13201	14400
5	2022-01-01 04:00:00+00	2022-01-01 04:19:59+00	14401	15600
5	2022-01-01 04:20:00+00	2022-01-01 04:29:59+00	15601	16200
5	2022-01-01 04:30:00+00	2022-01-01 04:39:59+00	16201	16800
5	2022-01-01 04:40:00+00	2022-01-01 04:59:59+00	16801	18000
5	2022-01-01 05:00:00+00	2022-01-01 05:19:59+00	18001	19200
5	2022-01-01 05:20:00+00	2022-01-01 05:29:59+00	19201	19800
5	2022-01-01 05:30:00+00	2022-01-01 05:39:59+00	19801	20400
5	2022-01-01 05:40:00+00	2022-01-01 05:59:59+00	20401	21600