问缓慢Postgres聚合查询

EN

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;

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