Postgres 16

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 df (x NUMERIC(3,1), y NUMERIC(3,1));

CREATE TABLE


INSERT INTO df VALUES (0, 2), (1, NULL), (3, NULL), (5, 7), (6, 4), (8, 5), (9, NULL)

INSERT 0 7


SELECT
    x,
    COALESCE(
        y,
        (
            SELECT 
                prev.y + ( (next.y - prev.y) * (parent.x - prev.x) / (next.x - prev.x) )
            FROM
                ( SELECT x, y, ROW_NUMBER() OVER (ORDER BY x DESC) as rn FROM df WHERE x <= parent.x and y is not null ) AS prev
            CROSS JOIN
                ( SELECT x, y, ROW_NUMBER() OVER (ORDER BY x ASC) as rn FROM df WHERE x >= parent.x and y is not null ) AS next
            WHERE
                prev.rn = next.rn
        )
    ) AS y
FROM
    df parent


x
y


0.0
2.0

1.0
3.00000000000000000000

3.0
5.0000000000000000

5.0
7.0

6.0
4.0

8.0
5.0

9.0
null



SELECT 7

x	y
0.0	2.0
1.0	3.00000000000000000000
3.0	5.0000000000000000
5.0	7.0
6.0	4.0
8.0	5.0
9.0	null