Postgres 中日期时间和窗口函数的（功能）索引的性能

我认为这是一个相当标准的任务类型，但我还没有看到任何详细介绍性能的内容，尽管比较星期几和小时的日期时间索引优化非常接近。我想通过我的温度传感器计算一年中每个月的平均温度。

我正在使用 Postgres 15

该表如下所示：

CREATE TABLE public.hygrometer (
    device_id bpchar(2) NOT NULL,
    temperature numeric(3, 1) NOT NULL,
    "timestamp" timestamp NOT NULL
);
CREATE INDEX time_id ON public.hygrometer (device_id bpchar_ops);

这是我第一次使用窗口函数，这是我的查询：

select distinct ON (month) 
date_part('month', "timestamp") as month, 
avg(temperature) over (PARTITION by date_part('month', "timestamp") ) as avg_temp
from hygrometer
order by month

我的一个问题是是否可以避免date_part('month', "timestamp")在查询中重复输入。我怀疑这是因为 select 中表达式的范围与窗口函数中的不同。

最初，我创建的数据粒度为 7 分钟（75,000 行），然后是 1 分钟（525,600 行），然后是 1 秒（3000 万行）。

我创建了以下部分索引。

CREATE INDEX month_idx ON public.hygrometer USING btree (date_part('month'::text, "timestamp"));

在 7 分钟粒度下，添加索引可以忽略不计，在 1 分钟粒度下，它会变得明显，尽管使用DISTINCT ON会带来最大的提升。

然而，令我惊讶的是，在没有索引的情况下，最大数据集的性能稍快一些，因为规划器开始并行工作。我读过，这应该可以通过索引实现，但即使将并行成本降低到接近 0，也无法说服服务器这样做。

这是带有索引的大表的查询计划：

 Unique  (cost=0.56..1725911.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=0.56..1647069.04 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Index Scan using month_idx on public.hygrometer  (cost=0.56..1095172.80 rows=31536928 width=14)
               Output: date_part('month'::text, "timestamp"), temperature

这是删除了索引的规划器：

 Unique  (cost=2364029.66..6667763.19 rows=31536928 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=2364029.66..6588920.87 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Gather Merge  (cost=2364029.66..6037024.63 rows=31536928 width=14)
               Output: (date_part('month'::text, "timestamp")), temperature
               Workers Planned: 2
               ->  Sort  (cost=2363979.63..2396830.60 rows=13140387 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361151.83 rows=13140387 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

我希望这里可以进行并行索引扫描。我想知道不同类型的索引（哈希？）是否更有意义。还有什么可能？大概是使用包含月份的物化视图。感谢您的意见和建议。

更新

看起来哈希索引是错误的策略，将会被忽略。

正如@jjanes 指出的：这里不需要使用窗口函数，因为这可以通过简单的聚合来实现。

这是查询：

select  
date_part('month', "timestamp") as month, 
avg(temperature)
from hygrometer
group by month
order by month

这就是计划：

 Finalize GroupAggregate  (cost=427831.20..427834.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=427831.20..427834.00 rows=24 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Sort  (cost=426831.18..426831.21 rows=12 width=40)
               Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
               Sort Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Partial HashAggregate  (cost=426830.78..426830.96 rows=12 width=40)
                     Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
                     Group Key: date_part('month'::text, hygrometer."timestamp")
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

索引的使用并不是立即显而易见的，但删除它的速度较慢，并且计划有所不同：

 Finalize GroupAggregate  (cost=2364783.98..6363725.60 rows=31534324 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=2364783.98..5693621.21 rows=26278604 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=2363783.96..2659418.25 rows=13139302 width=40)
               Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
               Group Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Sort  (cost=2363783.96..2396632.21 rows=13139302 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

您可以看到它在分组和排序时用于减少样本大小。实时性提高了 3-4 倍，并且索引构建速度非常快。对于这种改进来说，大约是桌子大小的三分之一是合理的。