如何在不扫描给定时间范围之外的区块的 TimescaleDB 超表上编写联接查询

Question

考虑以下一组表：

create table devices(
id integer,
primary key(id)
);
create table metrics(
ts timestamp,
id integer,
power double precision,
primary key (ts, id)
);
select create_hypertable('metrics', 'ts', chunk_time_interval := interval '1 days');
create table forecast(
ts timestamp,
id integer,
forecast_power double precision,
primary key (ts, id)
);
select create_hypertable('forecast', 'ts', chunk_time_interval := interval '1 days');
create table default_forecast(
ts timestamp,
default_forecast_power double precision,
primary key(ts)
);
select create_hypertable('default_forecast', 'ts', chunk_time_interval := interval '1 days');

这个视图：

create or replace view vw_forecast as
(
select d.id,
df.ts,
coalesce(f.forecast_power, df.default_forecast_power) as forecast_power
from devices d
join default_forecast df on true
left join forecast f on df.ts = f.ts and f.id = d.id
);

添加一些数据：

insert into metrics(ts, id, power)
select times.ts,
d.id,
5
from devices d,
generate_series('2000-01-01 00:00:00'::timestamp, '2001-01-01 00:00:00'::timestamp,
'5 minutes'::interval) as times(ts);
insert into forecast(ts, id, forecast_power)
select times.ts,
d.id,
6
from devices d,
generate_series('2000-01-01 00:00:00'::timestamp, '2001-01-01 00:00:00'::timestamp,
'2 hour'::interval) as times(ts);
insert into default_forecast(ts, default_forecast_power)
select times.ts,
7
from generate_series('2000-01-01 00:00:00'::timestamp, '2001-01-01 00:00:00'::timestamp,
'1 hour'::interval) as times(ts);

并执行此查询：

explain(analyze, format json)
select *
from metrics m
join vw_forecast f on f.id = m.id and f.ts = time_bucket('1 hour', m.ts)
where m.ts between '2000-01-10 00:00:00' and '2000-01-11 00:00:00';

我观察到查询计划导致对forecast和default_forecast表进行顺序扫描。我意识到，我可以在vw_forecast中公开forecast和default_forecast表中的时间戳列，并在查询的where子句中对这些时间戳列添加约束，这将消除顺序扫描。然而，我希望在多个上下文中使用此视图，而不是为了获得合理的性能而添加多个约束来加重视图的消费者的负担。有办法实现我的目标吗？

Answer 1

您也可以尝试添加一些额外的where子句来限制vw_forecast表吗？

一些新的片段，如：

and f.ts between '2000-01-10 00:00:00' and '2000-01-11 00:00:00';

查看完整视图并解释：

explain(analyze)
select *
from metrics m
join vw_forecast f on f.id = m.id and f.ts = time_bucket('1 hour', m.ts)
where m.ts between '2000-01-10 00:00:00' and '2000-01-11 00:00:00' 
and f.ts between '2000-01-10 00:00:00' and '2000-01-11 00:00:00';
...
│ Planning Time: 79.190 ms                                                                                                                                                                               │
│ Execution Time: 273.094 ms

在没有额外where子句的情况下进行比较：

explain(analyze)
select *
from metrics m
join vw_forecast f on f.id = m.id and f.ts = time_bucket('1 hour', m.ts)
where m.ts between '2000-01-10 00:00:00' and '2000-01-11 00:00:00';
...
│ Planning Time: 207.309 ms                                                                                                                                                                              │
│ Execution Time: 414.093 ms

请注意，查询中的where子句没有直接附加到视图中。