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

CREATE TABLE maintable (
	value1 int4,
	datetest date
);

CREATE TABLE


Insert Into maintable(
       value1,
       datetest)
VALUES
      (
      7263,
      '2021-03-31'
      ),      
      (
      6789,
      '2020-03-31'
      ),
      (
      6347,
      '2019-03-31'
      ),
      (
      6047,
      '2018-03-31'
      )
,
      (
      5647,
      '2017-03-31'
      )

INSERT 0 5


select 
*,
value1 -lag(value1)over (order by datetest) as diff
from
maintable
order by datetest desc


value1
datetest
diff


7263
2021-03-31
474

6789
2020-03-31
442

6347
2019-03-31
300

6047
2018-03-31
400

5647
2017-03-31
null



SELECT 5

value1	datetest	diff
7263	2021-03-31	474
6789	2020-03-31	442
6347	2019-03-31	300
6047	2018-03-31	400
5647	2017-03-31	null