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