将聚集物插入大桌子太慢

Question

我需要作为Cronjob进行查询，并且执行太慢。它从视图表（data_time_series）总和一个值，每对aks 和ii，并将一行插入storecore_time_time_series 如果这些值尚未存在： 以下是查询：

INSERT INTO storage_time_series (time, value, akd, ii)
SELECT data.time, data.value, data.akd, data.ii FROM
   ( SELECT 
        now() AS time,
        SUM(value) AS value,
        labels[2] AS akd,
        labels[4] AS ii
    FROM data_time_series
    GROUP BY akd, ii) AS data
WHERE NOT EXISTS (
    SELECT 1 FROM storage_time_series AS dst WHERE dst.value = data.value AND dst.access_key_id = data.akd AND dst.instance_id = data.ii );

查询的第一部分产生约200k行，并且很快：

SELECT 
        now() AS time,
        SUM(nullif(value, 'NaN'))  AS value,
        labels[2] AS akd,
        labels[4] AS ii
    FROM public.data_time_series
    GROUP BY akd, ii

查询的第二部分检查数据是否存在于storege_time_time_series中。这部分太慢：

WHERE NOT EXISTS (
    SELECT 1 FROM storage_time_series AS dst WHERE dst.value = data.value AND dst.access_key_id = data.akd AND dst.instance_id = data.ii)

storege_time_series是一个巨大的表格，我需要保留数据，akd和ii ii 在storege> storege_time_series上索引。

这是表模式：

SELECT version() :
PostgreSQL 12.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit

CREATE TABLE storage_time_series
(
    "time" timestamp with time zone,
    value double precision,
    akd integer,
    ii integer
)

CREATE  INDEX 
    ON storage_time_series USING btree
    (akd ASC NULLS LAST, ii ASC NULLS LAST, value ASC NULLS LAST)

storege_time_series上的行数表：〜1.9亿 data_time_series 是一个视图，约有130万行。

有什么方法可以加快此过程？

编辑：我已经测试过创建 akd ， ii 和 value 将查询更改为Simple 插入和在冲突上无济于事，但似乎具有唯一的索引使插入过程比以前更慢。

Answer 1

akd和ii在storege> storege_time_series

上索引

听起来不错。但是太模糊了，无法说更多。如果看来，（akd，ii，value）应该是唯一的，请相应地创建唯一的索引：

CREATE UNIQUE INDEX ON storage_time_series (akd, ii, value);

然后您的查询将燃烧至：

INSERT INTO storage_time_series
      (akd      , ii       , value     , time )
SELECT labels[2], labels[4], sum(value), now())
FROM   data_time_series
GROUP  BY 1, 2
ON     CONFLICT DO NOTHING;

哪个应该快速。很大程度上取决于未公开的细节。