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_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) AS ptime
         , CASE
             WHEN the_value < 10
               THEN 1
             ELSE 0
           END AS change
         , row_number() over (partition by id
                                 , case when the_value < 10 
                                        then 1
                                        else 0 
                                   end 
                              order by the_time) as grp
    from tab
    ORDER BY id, the_time;


id
the_value
the_time
ptime
change
grp


1
10
1
1
0
1

1
10
2
2
0
2

1
7
3
3
1
1

1
8
4
4
1
2

1
6
5
5
1
3

1
5
6
6
1
4

1
4
7
7
1
5

1
3
8
8
1
6

1
7
9
9
1
7

1
10
10
10
0
3

1
3
11
11
1
8

1
8
12
12
1
9

1
2
13
13
1
10

1
1
14
14
1
11

1
1
15
15
1
12

2
10
1
1
0
1

2
10
2
2
0
2

2
10
3
3
0
3

2
13
4
4
0
4

2
6
5
5
1
1

2
5
6
6
1
2

2
4
7
7
1
3

2
3
8
8
1
4

2
7
9
9
1
5

2
10
10
10
0
5

2
12
11
11
0
6


select id, MIN(the_time) AS min, MAX(the_time) AS max -- min and max per interval
from (
    select id, the_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) 
         - row_number() over (partition by id
                                 , case when the_value < 10 
                                        then 1
                                        else 0 
                                   end 
                              order by the_time) as grp
    from tab
) as x
where the_value < 10 -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4 -- duration
ORDER BY id, min;


id
min
max


1
3
9

1
11
15

2
5
9


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_value	the_time	ptime	change	grp
1	10	1	1	0	1
1	10	2	2	0	2
1	7	3	3	1	1
1	8	4	4	1	2
1	6	5	5	1	3
1	5	6	6	1	4
1	4	7	7	1	5
1	3	8	8	1	6
1	7	9	9	1	7
1	10	10	10	0	3
1	3	11	11	1	8
1	8	12	12	1	9
1	2	13	13	1	10
1	1	14	14	1	11
1	1	15	15	1	12
2	10	1	1	0	1
2	10	2	2	0	2
2	10	3	3	0	3
2	13	4	4	0	4
2	6	5	5	1	1
2	5	6	6	1	2
2	4	7	7	1	3
2	3	8	8	1	4
2	7	9	9	1	5
2	10	10	10	0	5
2	12	11	11	0	6

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_value	the_time	ptime	change	grp
1	10	1	1	0	1
1	10	2	2	0	2
1	7	3	3	1	1
1	8	4	4	1	2
1	6	5	5	1	3
1	5	6	6	1	4
1	4	7	7	1	5
1	3	8	8	1	6
1	7	9	9	1	7
1	10	10	10	0	3
1	3	11	11	1	8
1	8	12	12	1	9
1	2	13	13	1	10
1	1	14	14	1	11
1	1	15	15	1	12
2	10	1	1	0	1
2	10	2	2	0	2
2	10	3	3	0	3
2	13	4	4	0	4
2	6	5	5	1	1
2	5	6	6	1	2
2	4	7	7	1	3
2	3	8	8	1	4
2	7	9	9	1	5
2	10	10	10	0	5
2	12	11	11	0	6

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_value	the_time	ptime	change	grp
1	10	1	1	0	1
1	10	2	2	0	2
1	7	3	3	1	1
1	8	4	4	1	2
1	6	5	5	1	3
1	5	6	6	1	4
1	4	7	7	1	5
1	3	8	8	1	6
1	7	9	9	1	7
1	10	10	10	0	3
1	3	11	11	1	8
1	8	12	12	1	9
1	2	13	13	1	10
1	1	14	14	1	11
1	1	15	15	1	12
2	10	1	1	0	1
2	10	2	2	0	2
2	10	3	3	0	3
2	13	4	4	0	4
2	6	5	5	1	1
2	5	6	6	1	2
2	4	7	7	1	3
2	3	8	8	1	4
2	7	9	9	1	5
2	10	10	10	0	5
2	12	11	11	0	6