I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical input sets will contain between 10,000 to 1,000,000 rows. The following solution is the best idea i could think of. It works fine, but execution is rather slow. Because one of my requirements is to trigger the calculation quite often i need to optimize for execution time. Unfortunately it’s not an option to precalculate the values for the individual groups because every new measurement row affects the group size.
create table devices ( id varchar not null constraint devices_pkey primary key ); create table processes ( id integer not null, constraint processes_pkey primary key (id, device_id), device_id varchar not null constraint fk_processes_devices references devices on delete cascade ); create index processes_device_id_idx on processes (device_id); create table measurements ( timestamp timestamp with time zone not null, current real not null, process_id integer not null, device_id varchar not null, constraint measurements_pkey primary key (timestamp, process_id, device_id), constraint fk_measurements_processes foreign key (process_id, device_id) references processes on delete cascade ); create index measurements_process_id_device_id_idx on measurements (device_id, process_id); INSERT INTO devices (id) VALUES ('123'); INSERT INTO processes (id, device_id) VALUES (456, '123'); WITH numbers AS ( SELECT * FROM generate_series(1, 1000000) ) INSERT INTO measurements (timestamp, current, process_id, device_id) SELECT NOW() + (generate_series * interval '1 second'), generate_series * random(), 456, '123' FROM numbers;
select min(timestamp) as timestamp, case when sum(current) < 0 then -SQRT(AVG(POWER(current, 2))) else SQRT(AVG(POWER(current, 2))) end, 456 as process_id, '123' as device_id, index / 1000 as group_index from (select timestamp, current, row_number() over (order by timestamp) as index from measurements where device_id = '123' and process_id = 456 order by timestamp) as subquery group by group_index order by group_index;
Query plan visualization: http://tatiyants.com/pev/#/plans/plan_1569689658707
How to optimize the query?
Sort (cost=100157.88..100158.38 rows=200 width=60) (actual time=927.340..927.402 rows=1001 loops=1) Sort Key: ((subquery.index / 1000)) Sort Method: quicksort Memory: 103kB -> HashAggregate (cost=100144.74..100150.24 rows=200 width=60) (actual time=926.828..927.036 rows=1001 loops=1) Group Key: (subquery.index / 1000) -> Subquery Scan on subquery (cost=0.42..77644.74 rows=1000000 width=20) (actual time=0.049..704.478 rows=1000000 loops=1) -> WindowAgg (cost=0.42..65144.74 rows=1000000 width=20) (actual time=0.046..576.692 rows=1000000 loops=1) -> Index Scan using measurements_pkey on measurements (cost=0.42..50144.74 rows=1000000 width=12) (actual time=0.029..219.951 rows=1000000 loops=1) Index Cond: ((process_id = 456) AND ((device_id)::text = '123'::text)) Planning Time: 0.378 ms Execution Time: 927.591 ms