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
WITH nrs as (
  SELECT * 
  from generate_series(1,(select max(iteration) from Measures)) as f(n)
  cross join (select distinct expid from Measures)
)
SELECT nrs.expid, ARRAY_AGG(m.value) as a
FROM nrs
LEFT JOiN Measures m on m.expid=nrs.expid and m.iteration=nrs.n
GROUP BY nrs.expid
order by nrs.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