Postgres 13

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?.

CREATE TABLE test(
  main    int
, sub_id  int 
, value_t int
, newval  int
);

INSERT INTO test (main, sub_id, value_t) VALUES
(1,1,8),
(1,2,7),
(1,3,3),
(1,4,85),
(1,5,40),
(2,1,3),
(2,2,1),
(2,3,1),
(2,4,8),
(2,5,41);

TABLE test;
10 rows affected



main
sub_id
value_t
newval


1
1
8
null

1
2
7
null

1
3
3
null

1
4
85
null

1
5
40
null

2
1
3
null

2
2
1
null

2
3
1
null

2
4
8
null

2
5
41
null


SELECT *, bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
FROM (
   SELECT *
        , value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
          -- my diff is shifted by 1 row to make it simpler / cheaper
        , value_t - lead(value_t) OVER (PARTITION BY main ORDER BY sub_id) AS your_diff
   FROM   test
   ) sub;


main
sub_id
value_t
newval
diff
your_diff
flag


1
1
8
null
0
1
t

1
2
7
null
-1
4
t

1
3
3
null
-4
-82
t

1
4
85
null
82
45
f

1
5
40
null
-45
null
f

2
1
3
null
0
2
t

2
2
1
null
-2
0
t

2
3
1
null
0
-7
t

2
4
8
null
7
-33
t

2
5
41
null
33
null
f


UPDATE test AS t
SET    newval = 1
FROM  (
   SELECT *, bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
   FROM (
      SELECT *, value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
      FROM   test
      ) sub
   ) u
WHERE (t.main, t.sub_id) = (u.main, u.sub_id)
AND    u.flag
RETURNING *;


main
sub_id
value_t
newval
main
sub_id
value_t
newval
diff
flag


1
1
8
1
1
1
8
null
0
t

1
2
7
1
1
2
7
null
-1
t

1
3
3
1
1
3
3
null
-4
t

2
1
3
1
2
1
3
null
0
t

2
2
1
1
2
2
1
null
-2
t

2
3
1
1
2
3
1
null
0
t

2
4
8
1
2
4
8
null
7
t


TABLE test ORDER BY main, sub_id;


main
sub_id
value_t
newval


1
1
8
1

1
2
7
1

1
3
3
1

1
4
85
null

1
5
40
null

2
1
3
1

2
2
1
1

2
3
1
1

2
4
8
1

2
5
41
null

main	sub_id	value_t	newval
1	1	8	null
1	2	7	null
1	3	3	null
1	4	85	null
1	5	40	null
2	1	3	null
2	2	1	null
2	3	1	null
2	4	8	null
2	5	41	null