PostgreSQL(TimeScaleDB)：高效合并两个统计视图的优化问询

我将燃气采暖设备的监测数据存储在PostgreSQL数据库中，采用TimeScaleDB处理时序数据，需按月聚合采暖/热水燃烧占比、燃烧时长、燃气消耗等指标用于可视化分析。

目前通过三个视图实现统计：

1. 采暖与热水燃烧时长占比视图 v_heizung_ww_hz #

按月统计采暖与热水的燃烧时长占比，视图定义如下：

View "public.v_heizung_ww_hz"
 Column |           Type           | Collation | Nullable | Default | Storage | Description
--------+--------------------------+-----------+----------+---------+---------+-------------
 time   | timestamp with time zone |           |          |         | plain   |
 ww     | double precision         |           |          |         | plain   |
 hz     | double precision         |           |          |         | plain   |
View definition:
 SELECT date_trunc('month'::text, heizung.timest) AS "time",
    sum(
        CASE
            WHEN heizung.ventil = 3 THEN heizung.brenner_status::integer
            ELSE 0
        END)::double precision / sum(heizung.brenner_status)::double precision AS ww,
    sum(
        CASE
            WHEN heizung.ventil = 1 THEN heizung.brenner_status::integer
            ELSE 0
        END)::double precision / sum(heizung.brenner_status)::double precision AS hz
   FROM heizung
  GROUP BY (date_trunc('month'::text, heizung.timest));

2. 日统计视图 v_heizung_daily #

用于计算每日燃烧时长和启动次数，视图定义如下：

View "public.v_heizung_daily"
 Column  |       Type       | Collation | Nullable | Default | Storage | Description
---------+------------------+-----------+----------+---------+---------+-------------
 time    | date             |           |          |         | plain   |
 stunden | double precision |           |          |         | plain   |
 starts  | double precision |           |          |         | plain   |
View definition:
 SELECT s.timest::date AS "time",
    s.brenner_stunden::double precision - lag(s.brenner_stunden::double precision, 1) OVER (ORDER BY s.timest) AS stunden,
    s.brenner_starts::double precision - lag(s.brenner_starts::double precision, 1) OVER (ORDER BY s.timest) AS starts
   FROM ( SELECT DISTINCT ON ((heizung.timest::date)) heizung.timest,
            heizung.brenner_stunden,
            heizung.brenner_starts
           FROM heizung
          ORDER BY (heizung.timest::date) DESC, heizung.timest DESC) s;

3. 月度燃烧时长与燃气消耗视图 v_heizung_monthly #

基于日统计视图按月聚合燃烧时长、燃气消耗，视图定义如下：

View "public.v_heizung_monthly"
 Column  |           Type           | Collation | Nullable | Default | Storage | Description
---------+--------------------------+-----------+----------+---------+---------+-------------
 time    | timestamp with time zone |           |          |         | plain   |
 stunden | double precision         |           |          |         | plain   |
 Gas %   | double precision         |           |          |         | plain   |
 Gas l   | double precision         |           |          |         | plain   |
View definition:
 SELECT date_trunc('month'::text, v_heizung_daily."time"::timestamp with time zone) AS "time",
    sum(v_heizung_daily.stunden) AS stunden,
    sum(v_heizung_daily.stunden) * 0.65::double precision / 50::double precision AS "Gas %",
    sum(v_heizung_daily.stunden) * 0.65::double precision AS "Gas l"
   FROM v_heizung_daily
  GROUP BY (date_trunc('month'::text, v_heizung_daily."time"::timestamp with time zone))
  ORDER BY (date_trunc('month'::text, v_heizung_daily."time"::timestamp with time zone));

性能瓶颈 #

当执行以下JOIN语句合并两个月度视图时，查询耗时长达1546.846ms：

select a.time, stunden, stunden*0.65*7 as "kWh", ww, hz, "Gas %", "Gas l", "Gas l"*ww as "ww_l", "Gas l"*hz as "hz_l" from v_heizung_ww_hz a join v_heizung_monthly b on a.time = b.time;

希望能找到更高效的实现方案，或者咨询是否应该使用物化视图来优化查询性能。

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