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 Measures(
    expId SERIAL,
    iteration INT NOT NULL,
    value float4 NOT NULL,
    PRIMARY KEY(expId, iteration)
);

--So, a table of various measurements, repeated for n iterations. 
--Though, because we have more data than originally expected, 
--I want to move to a new table layout that instead uses an array column, 
--which overall gives better performance (already tested and benchmarked):

CREATE TABLE TmpMeasures(
    expId SERIAL PRIMARY KEY,
    values float4[] NOT NULL
);
--My problem now is how to get the old data into the new format. 
--The data may look something like this. 
--Not that iterations don't always produce all data, 
--so there may be NULL values in the final array:

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);
--And conversion could be done with a two step process, roughly like this, 
--to first create the array for an experiment, and then populate the iteration values:

INSERT INTO TmpMeasures(expId, values)
  SELECT expId, '{}'::float4[] 
  FROM Measures
  ON CONFLICT DO NOTHING;

UPDATE TmpMeasures tm
  SET values[iteration] = m.value
  FROM Measures m WHERE tm.expId = m.expId;
--Though, my problem now is that the UPDATE 
--actually only ever seems to take the first iteration, 
--i.e., iteration = 1. I am not quite understanding why that is the case.

--I suspect, alternative approaches to values[iteration] 
--would try to group by expId, and order by iteration 
--and aggregate that into an array.

--Unfortunately, the data isn't perfect, but iterations should line up.

--So, the following seems to work, but it's extremely slow, 
--and I don't quite understand why it's needed in the first place.

DO
$do$
BEGIN 
   FOR i IN 1..(SELECT max(iteration) FROM Measures m) LOOP
      UPDATE TmpMeasures tm
      SET values[i] = m.value
      FROM Measures m
      WHERE
        tm.expId = m.expId AND
        m.iteration=i;
   END LOOP;
END
$do$;

CREATE TABLE



CREATE TABLE



INSERT 0 7



INSERT 0 3



UPDATE 3



DO


drop table if exists TmpMeasures;

create table if not exists TmpMeasures as
with min_max_iteration as (
  select min(iteration), 
         max(iteration) from Measures)
,distinct_expIds as (
  select distinct expId from Measures)
select expId,
       array_agg(value order by iteration) as "values"
from min_max_iteration
cross join distinct_expIds d
cross join generate_series(min,max) g(iteration)
left join Measures m using(iteration,ExpId)
group by ExpId;

select * from TmpMeasures;

--needed to set the identity column to the right position
--subselect in alter table won't be allowed
select max(expId)+1 from TmpMeasures;
--add column constraints
alter table TmpMeasures 
   add primary key (expId)
  ,alter column expId add generated by default as identity(start with 4)
  ,alter column values set not null;

--test to see if the generated column works fine, starting from the latest
insert into TmpMeasures(values) select '{1,2,3,4}' returning *;

DROP TABLE



SELECT 3




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




?column?


4



SELECT 1



ALTER TABLE




expid
values


4
{1,2,3,4}



INSERT 0 1


--same, as an update
with min_max_iteration as (
  select min(iteration), 
         max(iteration) from Measures)
,distinct_expIds as (
  select distinct expId from Measures)
,update_batch(ExpId,values_arr) as (
  select expId,
         array_agg(value order by iteration) as "values"
  from min_max_iteration
  cross join distinct_expIds d
  cross join generate_series(min,max) g(iteration)
  left join Measures m using(iteration,ExpId)
  group by ExpId)
update TmpMeasures this
  set values=update_batch.values_arr
from update_batch
where this.expId=update_batch.expId
returning this.*;


expid
values


1
{1.1,1.2,1.3,NULL}

2
{2.1,NULL,NULL,2.4}

3
{3.1,NULL,NULL,3.4}



UPDATE 3


--inspecting the update batch
with min_max_iteration(min,max) as (
  select min(iteration),max(iteration) from Measures)
,distinct_expIds(dExpId) as (
  select distinct expId from Measures)
select dExpId,array_agg(value order by i)
from min_max_iteration
cross join distinct_expIds d
cross join generate_series(min,max)g(i)
left join Measures m on m.iteration=g.i
                    and m.expId=d.dExpId
group by dExpId;


dexpid
array_agg


1
{1.1,1.2,1.3,NULL}

2
{2.1,NULL,NULL,2.4}

3
{3.1,NULL,NULL,3.4}



SELECT 3


--inspecting the batch before aggregation
with min_max_iteration(min,max) as (
  select min(iteration),max(iteration) from Measures)
,distinct_expIds(dExpId) as (
  select distinct expId from Measures)
,cte as (
  select * 
  from min_max_iteration
  cross join distinct_expIds d
  cross join generate_series(min,max)g(i)
  left join Measures m on m.iteration=g.i
                      and m.expId=d.dExpId)
select * from cte 
order by i;


min
max
dexpid
i
expid
iteration
value


1
4
1
1
1
1
1.1

1
4
2
1
2
1
2.1

1
4
3
1
3
1
3.1

1
4
1
2
1
2
1.2

1
4
2
2
null
null
null

1
4
3
2
null
null
null

1
4
1
3
1
3
1.3

1
4
2
3
null
null
null

1
4
3
3
null
null
null

1
4
1
4
null
null
null

1
4
2
4
2
4
2.4

1
4
3
4
3
4
3.4



SELECT 12
min	max	dexpid	i	expid	iteration	value
1	4	1	1	1	1	1.1
1	4	2	1	2	1	2.1
1	4	3	1	3	1	3.1
1	4	1	2	1	2	1.2
1	4	2	2	null	null	null
1	4	3	2	null	null	null
1	4	1	3	1	3	1.3
1	4	2	3	null	null	null
1	4	3	3	null	null	null
1	4	1	4	null	null	null
1	4	2	4	2	4	2.4
1	4	3	4	3	4	3.4