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:
select setseed(.42);--stabilise random() between tests
INSERT INTO Measures (expId, iteration, value)
select expId, iteration, round(random()::numeric*10,1)
from generate_series(1,60)expId
join generate_series(1,800)iteration on .6>random();
--each expId randomly tries to get a value for an iteration
--40% of the time it will not get an iteration, therefore no value, no entry
--now below 30k, to let the looped update finish
--array_agg took 370ms on 120k Measures total,
--plpgsql looped update failed without completing
select * from Measures tablesample bernoulli(.42)repeatable(.42)limit 15;
CREATE TABLE
CREATE TABLE
| setseed |
|---|
SELECT 1
INSERT 0 28716
| expid | iteration | value |
|---|---|---|
| 1 | 187 | 0.5 |
| 2 | 242 | 4.6 |
| 2 | 464 | 8.7 |
| 2 | 710 | 6.8 |
| 3 | 635 | 9.2 |
| 5 | 450 | 5 |
| 5 | 464 | 5.9 |
| 6 | 699 | 0 |
| 8 | 27 | 7.2 |
| 8 | 467 | 1.4 |
| 8 | 497 | 4.9 |
| 12 | 14 | 6.9 |
| 12 | 112 | 0.3 |
| 12 | 256 | 2 |
| 12 | 328 | 1.4 |
SELECT 15
drop table if exists TmpMeasures;
explain analyze verbose
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;
/*
--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
| QUERY PLAN |
|---|
| GroupAggregate (cost=26740.47..28242.97 rows=200 width=36) (actual time=79.905..90.117 rows=60 loops=1) |
| Output: measures_1.expid, array_agg(m.value ORDER BY g.iteration) |
| Group Key: measures_1.expid |
| -> Sort (cost=26740.47..27240.47 rows=200000 width=12) (actual time=79.688..82.897 rows=48000 loops=1) |
| Output: measures_1.expid, m.value, g.iteration |
| Sort Key: measures_1.expid, g.iteration |
| Sort Method: quicksort Memory: 3261kB |
| -> Hash Left Join (cost=2138.76..5711.33 rows=200000 width=12) (actual time=23.440..45.293 rows=48000 loops=1) |
| Output: measures_1.expid, m.value, g.iteration |
| Inner Unique: true |
| Hash Cond: ((g.iteration = m.iteration) AND (measures_1.expid = m.expid)) |
| -> Nested Loop (cost=1187.16..3709.67 rows=200000 width=8) (actual time=12.184..21.588 rows=48000 loops=1) |
| Output: g.iteration, measures_1.expid |
| -> Nested Loop (cost=633.36..653.37 rows=1000 width=4) (actual time=5.480..5.700 rows=800 loops=1) |
| Output: g.iteration |
| -> Aggregate (cost=633.36..633.37 rows=1 width=8) (actual time=5.353..5.354 rows=1 loops=1) |
| Output: min(measures.iteration), max(measures.iteration) |
| -> Seq Scan on public.measures (cost=0.00..474.24 rows=31824 width=4) (actual time=0.012..2.727 rows=28716 loops=1) |
| Output: measures.expid, measures.iteration, measures.value |
| -> Function Scan on pg_catalog.generate_series g (cost=0.00..10.00 rows=1000 width=4) (actual time=0.124..0.227 rows=800 loops=1) |
| Output: g.iteration |
| Function Call: generate_series((min(measures.iteration)), (max(measures.iteration))) |
| -> Materialize (cost=553.80..556.80 rows=200 width=4) (actual time=0.008..0.012 rows=60 loops=800) |
| Output: measures_1.expid |
| -> HashAggregate (cost=553.80..555.80 rows=200 width=4) (actual time=6.691..6.702 rows=60 loops=1) |
| Output: measures_1.expid |
| Group Key: measures_1.expid |
| Batches: 1 Memory Usage: 40kB |
| -> Seq Scan on public.measures measures_1 (cost=0.00..474.24 rows=31824 width=4) (actual time=0.007..2.309 rows=28716 loops=1) |
| Output: measures_1.expid, measures_1.iteration, measures_1.value |
| -> Hash (cost=474.24..474.24 rows=31824 width=12) (actual time=10.881..10.882 rows=28716 loops=1) |
| Output: m.value, m.iteration, m.expid |
| Buckets: 32768 Batches: 1 Memory Usage: 1490kB |
| -> Seq Scan on public.measures m (cost=0.00..474.24 rows=31824 width=12) (actual time=0.008..3.698 rows=28716 loops=1) |
| Output: m.value, m.iteration, m.expid |
| Planning Time: 1.077 ms |
| Execution Time: 94.321 ms |
EXPLAIN
create function plpgsql_looped_update() returns void as $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$ language plpgsql;
explain analyze verbose select plpgsql_looped_update();
CREATE FUNCTION
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.