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

select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE TABLE Measures(
    expId SERIAL,
    iteration INT NOT NULL,
    value float4 NOT NULL,
    PRIMARY KEY(expId, iteration)
);

CREATE TABLE TmpMeasures(
    expId SERIAL PRIMARY KEY,
    values float4[] NOT NULL
);

CREATE TABLE



CREATE TABLE


INSERT INTO Measures (expId, iteration, value)
VALUES
  (1, 1, 1.1), (1, 2, 1.2), (1, 3, 1.3),
  (2, 1, 2.1),                           (2, 4, 2.4),
  (3, 1, 3.1),                           (3, 4, 3.4);

INSERT 0 7


SELECT * FROM Measures;


expid
iteration
value


1
1
1.1

1
2
1.2

1
3
1.3

2
1
2.1

2
4
2.4

3
1
3.1

3
4
3.4



SELECT 7


INSERT INTO TmpMeasures
SELECT m1.expid, ARRAY[m1.value, m2.value, m3.value,m4.value] as a
FROM (SELECT distinct expid FROM Measures) m0
LEFT JOIN Measures m1 ON m1.expid = m0.expid and m1.iteration = 1
LEFT JOIN Measures m2 ON m2.expid = m0.expid and m2.iteration = 2
LEFT JOIN Measures m3 ON m3.expid = m0.expid and m3.iteration = 3
LEFT JOIN Measures m4 ON m4.expid = m0.expid and m4.iteration = 4
ORDER BY expid

INSERT 0 3


SELECT * FROM TmpMeasures;


expid
values


1
{1.1,1.2,1.3,NULL}

2
{2.1,NULL,NULL,2.4}

3
{3.1,NULL,NULL,3.4}



SELECT 3