我使用以下查询以8小时分段显示机器的性能（尽管它可以更改为1小时、1周、1个月等）。我的表有200万条记录，查询需要10秒执行。这是一个合理的速度，还是可以改进？
我有

• 8 GB内存
• Intel Haswell CPU 4核心
• PostgreSQL 14.2
• TimescaleDB 2.6.1
• shared_buffers = 1024MB
• temp_buffers = 16MB
• work_css = 64MB

tbl_pieza表是一个超表。

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;

我在一个系统上运行这个与上述规格。任何关于如何优化此查询以获得更好性能的建议都将非常感谢。谢谢你，谢谢！
EXPLAIN输出（ANALYZE、BUFFERS）
Explain output
PiezasPorIntervalo是需要最长时间的部分，我会马上解释我做什么
假设我们有一个生产表，其中包含以下条目：

Piece | Production Start         | Production End
----- | ------------------------| -------------------------
A     | 2023-05-23 08:00:00     | 2023-05-23 10:00:00
B     | 2023-05-23 09:30:00     | 2023-05-23 12:00:00
C     | 2023-05-23 10:30:00     | 2023-05-23 11:30:00
D     | 2023-05-23 12:00:00     | 2023-05-23 13:30:00

假设我们要计算从“2023-05-23 09：00：00”到“2023 - 05 -23 11：00：00”的特定时间间隔内的“PiezasReales”的数量。以下是逐步计算：
时间范围和单件生产时间之间的交叉持续时间：

For Piece A: MIN(2023-05-23 11:00:00, 2023-05-23 10:00:00) - MAX(2023-05-23 09:00:00, 2023-05-23 08:00:00) = 1 hour
For Piece B: MIN(2023-05-23 11:00:00, 2023-05-23 12:00:00) - MAX(2023-05-23 09:00:00, 2023-05-23 09:30:00) = 0.5 hours
For Piece C: MIN(2023-05-23 11:00:00, 2023-05-23 11:30:00) - MAX(2023-05-23 09:00:00, 2023-05-23 10:30:00) = 0.5 hours
For Piece D: No intersection with the interval, so the duration is 0.

作品总时长：

For Piece A: 2023-05-23 10:00:00 - 2023-05-23 08:00:00 = 2 hours
For Piece B: 2023-05-23 12:00:00 - 2023-05-23 09:30:00 = 2.5 hours
For Piece C: 2023-05-23 11:30:00 - 2023-05-23 10:30:00 = 1 hour
For Piece D: 2023-05-23 13:30:00 - 2023-05-23 12:00:00 = 1.5 hours

时间间隔中的时间分数：

For Piece A: 1 hour / 2 hours = 0.5
For Piece B: 0.5 hours / 2.5 hours = 0.2
For Piece C: 0.5 hours / 1 hour = 0.5
For Piece D: 0 (since there's no intersection with the interval).

区间内的PiezasReales数：
时间间隔内的时间分数之和：0.5 + 0.2 + 0.5 + 0 = 1.2因此，在这个特定的时间间隔内，有相当于1.2件的“RealPieces”，考虑到每个零件在该时间间隔内生产的时间比例。