如何优化TimescaleDB中机器性能分段查询的执行速度？

我需要按8小时分段（可切换为1小时/1周/1月等维度）展示机器性能，数据表tbl_pieza是TimescaleDB超表，包含200万条记录，当前查询执行耗时10秒。请问该速度是否合理，能否进行优化？

软硬件与配置 #

• 8GB内存
• Intel Haswell 4核CPU
• PostgreSQL 14.2
• TimescaleDB 2.6.1
• 数据库参数：
  • shared_buffers = 1024MB
  • temp_buffers = 16MB
  • work_mem = 64MB

数据表定义与查询语句 #

create table tbl_pieza
(
    id_nu_pieza             integer                             not null,
    id_nu_orden_fabricacion integer,
    id_nu_referencia        integer,
    id_nu_operacion         integer,
    id_nu_maquina           integer,
    id_nu_usuario           integer,
    ind_paro                integer,
    ind_validada            integer   default 0,
    nu_segundos             integer,
    dtm_inicio_at           timestamp default CURRENT_TIMESTAMP not null,
    dtm_fin_at              timestamp,
    ind_estatus             integer   default 1,
    dtm_create_at           timestamp,
    dtm_update_at           timestamp default CURRENT_TIMESTAMP,
    ind_retrabajo           integer   default 0,
    primary key (id_nu_pieza, dtm_inicio_at)
);

create index tbl_pieza_dtm_inicio_at_idx
    on tbl_pieza (dtm_inicio_at desc);

create index idx_time_range
    on tbl_pieza (dtm_inicio_at, dtm_fin_at);

WITH Rangos AS (
    SELECT
        generate_series(
            '2023-05-22 16:23:14'::timestamp,
            '2023-05-26 08:23:14'::timestamp,
            '8 hour'::interval
        ) AS inicio,
        generate_series(
            '2023-05-23 00:23:14'::timestamp,
            '2023-05-26 16:23:14'::timestamp,
            '8 hour'::interval
        ) AS fin
),
PiezasPorIntervalo AS (
    SELECT
        r.inicio,
        r.fin,
        p.id_nu_operacion,
        p.id_nu_maquina,
        SUM(
            CASE
                WHEN EXTRACT(epoch FROM p.dtm_fin_at - p.dtm_inicio_at) = 0 THEN 0
                ELSE GREATEST(0, EXTRACT(epoch FROM LEAST(r.fin, p.dtm_fin_at) - GREATEST(r.inicio, p.dtm_inicio_at)) / EXTRACT(epoch FROM p.dtm_fin_at - p.dtm_inicio_at))
            END
        ) as PiezasReales
    FROM Rangos r
    JOIN tbl_pieza p ON p.dtm_inicio_at < r.fin AND p.dtm_fin_at > r.inicio
                            AND p.id_nu_usuario in (1,8,11,43,44,45,46,47,48,49)
                            AND p.id_nu_operacion in (84,85,86,87,88,89,90,91,92,93,118,119)
                            AND p.id_nu_referencia in (46,58,59,60)
                            AND p.id_nu_maquina in (1,2,3,8)
    GROUP BY r.inicio, r.fin, p.id_nu_operacion, p.id_nu_maquina
)
SELECT
    p.inicio as fecha_inicio,
    p.fin as fecha_fin,
    p.id_nu_maquina as id_maquina,
    CASE
        WHEN o.ciclo_estimado + o.tiempo_cambio_estimado = 0 THEN 0
        ELSE (p.PiezasReales::decimal / (28800 / (o.ciclo_estimado + o.tiempo_cambio_estimado))) * 100
    END as resultado
FROM PiezasPorIntervalo p
JOIN operacion o ON o.id_operacion = p.id_nu_operacion
ORDER BY fecha_inicio;

核心计算逻辑说明 #

PiezasPorIntervalo是查询耗时最长的部分，其作用为：统计每个时间分段内，各机器、工序对应的PiezasReales（即各生产任务在分段区间内的时间占比之和）。例如在'2023-05-23 09:00:00'至'2023-05-23 11:00:00'区间内，通过计算各生产任务与区间的时间交集占比求和，得到等效的1.2个PiezasReales。

我已准备好该查询的EXPLAIN (ANALYZE, BUFFERS)输出结果，恳请提供针对性的性能优化建议。

内容的提问来源于stack exchange，提问作者Isra