Postgres 11

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 11.5 (Debian 11.5-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE tab
(
  id TEXT NOT NULL,
  the_time INTEGER NOT NULL,
  the_value INTEGER NOT NULL
);

INSERT INTO tab
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 7), (1, 4, 8), (1, 5, 6), (1, 6, 5), (1, 7, 4),
(1, 8, 3), (1, 9, 7), (1, 10, 10), (1, 11, 3), (1, 12, 8), (1, 13, 2), (1, 14, 1), (1, 15, 1),
(2, 1, 10), (2, 2, 10), (2, 3, 10), (2, 4, 13), (2, 5, 6), (2, 6, 5), (2, 7, 4),
(2, 8, 3), (2, 9, 7),
(2, 10, 10), (2, 11, 12);

SELECT * FROM tab;
26 rows affected



id
the_time
the_value


1
1
10

1
2
10

1
3
7

1
4
8

1
5
6

1
6
5

1
7
4

1
8
3

1
9
7

1
10
10

1
11
3

1
12
8

1
13
2

1
14
1

1
15
1

2
1
10

2
2
10

2
3
10

2
4
13

2
5
6

2
6
5

2
7
4

2
8
3

2
9
7

2
10
10

2
11
12


SELECT 
  id, 
  the_min AS start_time, 
  the_max AS end_time, 
  (the_max - the_min) + 1 AS the_run_length  -- Optional, not requested
FROM
(
  SELECT id, MIN(the_time) AS the_min, MAX(the_time) AS the_max, the_sum
  FROM
  (
    SELECT id, the_time, 
      SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum,
     the_value
    FROM
    (
      SELECT 
        id, the_time, 
        LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        the_value,
        LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        CASE 
          WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
            AND     the_value < 10) 
          THEN 1  
          WHEN (the_value >= 10)
          THEN 1
          ELSE 0
        END AS change
      FROM tab
    ) AS t1
    ORDER BY id, the_time
  ) AS t2 
  GROUP BY id, the_sum
  ORDER BY id, the_sum
) AS t3
WHERE ((the_max - the_min) + 1) >= 4  -- always brackets, operator precedence can be tricky!
ORDER BY id, the_min;


id
start_time
end_time
the_run_length


1
3
9
7

1
11
15
5

2
5
9
5

id	the_time	the_value
1	1	10
1	2	10
1	3	7
1	4	8
1	5	6
1	6	5
1	7	4
1	8	3
1	9	7
1	10	10
1	11	3
1	12	8
1	13	2
1	14	1
1	15	1
2	1	10
2	2	10
2	3	10
2	4	13
2	5	6
2	6	5
2	7	4
2	8	3
2	9	7
2	10	10
2	11	12

id	the_time	the_value
1	1	10
1	2	10
1	3	7
1	4	8
1	5	6
1	6	5
1	7	4
1	8	3
1	9	7
1	10	10
1	11	3
1	12	8
1	13	2
1	14	1
1	15	1
2	1	10
2	2	10
2	3	10
2	4	13
2	5	6
2	6	5
2	7	4
2	8	3
2	9	7
2	10	10
2	11	12

id	the_time	the_value
1	1	10
1	2	10
1	3	7
1	4	8
1	5	6
1	6	5
1	7	4
1	8	3
1	9	7
1	10	10
1	11	3
1	12	8
1	13	2
1	14	1
1	15	1
2	1	10
2	2	10
2	3	10
2	4	13
2	5	6
2	6	5
2	7	4
2	8	3
2	9	7
2	10	10
2	11	12