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, 12),
(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;
22 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
12

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


-- FIRST SQL - inner loop

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 (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
      AND      the_value >= 10)
    THEN 1
    ELSE 0
  END AS change
FROM tab;


id
the_time
lag
the_value
lead
change


1
1
null
10
10
1

1
2
10
10
7
0

1
3
10
7
8
1

1
4
7
8
6
0

1
5
8
6
5
0

1
6
6
5
4
0

1
7
5
4
3
0

1
8
4
3
7
0

1
9
3
7
10
0

1
10
7
10
12
1

1
11
10
12
null
0

2
1
null
10
10
1

2
2
10
10
10
1

2
3
10
10
13
1

2
4
10
13
6
0

2
5
13
6
5
1

2
6
6
5
4
0

2
7
5
4
3
0

2
8
4
3
7
0

2
9
3
7
10
0

2
10
7
10
12
1

2
11
10
12
null
0


-- 2nd SQL - the middle loop!
SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
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 (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
        AND      the_value >= 10)
      THEN 1
      ELSE 0
    END AS change
  FROM tab
) AS t1
ORDER BY id, the_time;


id
the_time
the_sum


1
1
1

1
2
1

1
3
2

1
4
2

1
5
2

1
6
2

1
7
2

1
8
2

1
9
2

1
10
3

1
11
3

2
1
1

2
2
2

2
3
3

2
4
3

2
5
4

2
6
4

2
7
4

2
8
4

2
9
4

2
10
5

2
11
5


-- The third SQL - 
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
  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 (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
          AND      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;


id
the_min
the_max
the_sum


1
1
2
1

1
3
9
2

1
10
11
3

2
1
1
1

2
2
2
2

2
3
4
3

2
5
9
4

2
10
11
5


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
    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 (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
            AND      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) >= 5  -- always brackets, operator precedence can be tricky!
AND (the_min < 10)
AND (the_max < 10)  -- don't want runs of greater than or = 10, only less than!
ORDER BY id, the_min;


id
start_time
end_time
the_run_length


1
3
9
7

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	12
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	lag	the_value	lead	change
1	1	null	10	10	1
1	2	10	10	7	0
1	3	10	7	8	1
1	4	7	8	6	0
1	5	8	6	5	0
1	6	6	5	4	0
1	7	5	4	3	0
1	8	4	3	7	0
1	9	3	7	10	0
1	10	7	10	12	1
1	11	10	12	null	0
2	1	null	10	10	1
2	2	10	10	10	1
2	3	10	10	13	1
2	4	10	13	6	0
2	5	13	6	5	1
2	6	6	5	4	0
2	7	5	4	3	0
2	8	4	3	7	0
2	9	3	7	10	0
2	10	7	10	12	1
2	11	10	12	null	0

id	the_time	the_sum
1	1	1
1	2	1
1	3	2
1	4	2
1	5	2
1	6	2
1	7	2
1	8	2
1	9	2
1	10	3
1	11	3
2	1	1
2	2	2
2	3	3
2	4	3
2	5	4
2	6	4
2	7	4
2	8	4
2	9	4
2	10	5
2	11	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	12
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_sum
1	1	1
1	2	1
1	3	2
1	4	2
1	5	2
1	6	2
1	7	2
1	8	2
1	9	2
1	10	3
1	11	3
2	1	1
2	2	2
2	3	3
2	4	3
2	5	4
2	6	4
2	7	4
2	8	4
2	9	4
2	10	5
2	11	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	12
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_sum
1	1	1
1	2	1
1	3	2
1	4	2
1	5	2
1	6	2
1	7	2
1	8	2
1	9	2
1	10	3
1	11	3
2	1	1
2	2	2
2	3	3
2	4	3
2	5	4
2	6	4
2	7	4
2	8	4
2	9	4
2	10	5
2	11	5