0

I think this is a fairly standard type of task but I haven't seen anything covering performance in detail, though Index Optimization for Datetime comparing day of week and hour is pretty close. From my temperature sensors I would like to calculate the average temperature in each month of a year.

I'm using Postgres 15

The table looks like this:

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);

This was my first time using window functions and this is my query:

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

One question I have is whether it's possible to avoid typing date_part('month', "timestamp") repeatedly in the query. I suspect this is because the scope of the expression in the select is different to that in the windowing function.

Initially I created data with 7 minute granualarity (75,000 rows), then 1 minute (525,600 rows), then 1 second (30 million).

I created the following partial index.

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

With 7 minute granularity adding an index is negligible, with 1 minute it becomes noticeable, though using DISTINCT ON gives the biggest boost.

However, I was surprised that the performance with the largest set of data is slightly faster without an index, because the planner starts to work in parallel. I've read that this should be possible with an index but couldn't manage to persuade the server to do it even by dropping the parallel_cost to nearly 0.

This is the query plan for the large table with an index:

 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

And this is the planner with the index removed:

 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

I would expect a parallel index scan to be possible here. I wonder if a different kind of index (hash?) might make more sense. What else is possible? Presumably a materialised view using containing the month. Thanks for your comments and suggestions.

UPDATE

It looks like a hash index is the wrong strategy an will just be ignored.

As @jjanes points out: there's no need to use a window function here as this is possible with a simple aggregate.

This is the query:

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

And this is the plan:

 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

The use of the index isn't immediately apparent but removing it is slower and the plan is different:

 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

You can see it used to reduce the sample size, when grouping and sorting. A 3-4 times improvement in real time and the index is very quick to build. At about a third of the size of the table this is reasonable for that kind of improvement.

4
  • I usually use to_char instead of date_part for queries like this. I don't remember why I started doing that. I'm not sure if it will help or make things worse for you.
    – rotten
    Aug 9, 2023 at 12:43
  • Unless it provokes a different index strategy I can't see much difference but thanks for the suggestion. Aug 9, 2023 at 12:54
  • 1
    The functional index has stats gathered on its results, which lets the system know that there are going to be few distinct months. Without that index, it thinks there will be a lot of distinct months (it doesn't understand the internal semantics of the date_part function) and so chooses different approach, which ends up being worse. Starting in v14, you can use CREATE STATISTICS to gather those function-result statistics without having the functional index.
    – jjanes
    Aug 10, 2023 at 15:57
  • Yes, you can see the index is at work in the plan. I'm learning a lot because of my mistake! :-) Aug 10, 2023 at 16:00

1 Answer 1

1

This is an inappropriate use of a window function. You are using a window function to preserve duplicates that you don't actually want, then using DISTINCT ON to remove those same duplicates.

You should just a regular aggregate function with a GROUP BY:

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

This will probably be faster, and if not will at least serve as a better starting point for optimization. And if want to pursue that, you need an EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN.

1
  • Thanks! I am an idiot! Works fine and is much, much faster of course. I guess I can put this down to a "learning experience" and I have learned a lot. I'll update the question with the answer and the query plan. Doesn't look like the index is being used. Aug 10, 2023 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.