sql - 如何加快此 SQL 查询以查找以前的条目 "on this day"？

Question

我正在尝试加快此 PostgreSQL 查询的速度，以从表中查找过去几年“在这一天”的先前条目。我目前有以下查询:

select * from sample
where date_part('month', "timestamp") = date_part('month', now())
and date_part('day', "timestamp") = date_part('day', now())
order by "timestamp" desc;

这似乎得到了预期的结果，但它的运行速度比预期的要慢得多。是否有更好的方法来比较当前月份和日期？

另外，在过去的几年里，对“这个时间”进行类似的搜索会有什么变化吗？类似于以下内容:

select * from sample
where date_part('month', "timestamp") = date_part('month', now())
and date_part('day', "timestamp") = date_part('day', now())
and date_part('hour', "timestamp") = date_part('hour', now())
order by "timestamp" desc;

数据本质上是时间序列的，使用TimescaleDB作为数据库。这是当前的定义:

CREATE TABLE public.sample (
    "timestamp" timestamptz NOT NULL DEFAULT now(),
    entity varchar(256) NOT NULL,
    quantity numeric NULL
);
CREATE INDEX sample_entity_time_idx ON public.sample (entity, "timestamp" DESC);
CREATE INDEX sample_time_idx ON public.sample ("timestamp" DESC);

Answer 1

如果您需要所有前几年的同一天，我猜查询将返回 1/365 的行；那是 0.27% 的选择性。太好了。

有了这种选择性，索引可以显着加快查询速度。现在，由于您选择的是非连续行，因此您需要一个功能索引。我会尝试:

create index ix1 on sample ((date_part('doy', "timestamp")));

然后，您可以将查询修改为:

select * 
from sample
where date_part('doy', "timestamp") = date_part('doy', now())
order by "timestamp" desc;

对于过去几年的当前小时，你会有更好的选择性，约为 1/365/24；即 0.01%。太棒了。

create index ix2 on sample (
  (date_part('doy', "timestamp")), 
  (date_part('hour', "timestamp"))
);

然后，新查询可能如下所示:

select * 
from sample
where date_part('doy', "timestamp") = date_part('doy', now()) 
  and date_part('hour', "timestamp") = date_part('hour', now()) 
order by "timestamp" desc;

请发布这些查询的执行计划以及创建的索引。我很想知道性能如何。