Postgresql – Slow Postgres Aggregate Query

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.

Schema Setup

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;

Query

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;

db-fiddle: https://www.db-fiddle.com/f/uVTcf9Q2JDEkPf3S5hgvfB/2

Query plan visualization: http://tatiyants.com/pev/#/plans/plan_1569689658707

How to optimize the query?

Query Plan

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