PostgreSQL optimization: average over range of dates, PostgreSQL

PostgreSQL optimization: average over range of dates

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create table temperatures(
  site_id        int         not null,    -- references ...
  timestamp      timestamptz not null,
  temperature    numeric     not null,

primary key (site_id, timestamp)
);

insert into temperatures(site_id, timestamp, temperature)
select (random() * 3)::int, ts, 3 + (random() * 42)
from   generate_series('2010-01-01'::timestamptz, '2017-05-24'::timestamptz, interval '12 hours') ts;

select count(*), count(*) filter (where site_id = 1)
from   temperatures;

EXPLAIN ANALYZE
SELECT days,
       (SELECT avg(temperature)
        FROM   temperatures
        WHERE  site_id = 1
        AND    EXTRACT(doy FROM timestamp) BETWEEN EXTRACT(doy FROM days) - 7 AND EXTRACT(doy FROM days) + 7) AS temperature
FROM   generate_series('2017-05-01'::timestamp, '2017-08-31'::timestamp, interval '1 day') days;

explain analyze
with p as (
  select timestamptz '2017-05-01' min,
         timestamptz '2017-08-31' max
)
select     t.*
from       p
cross join (select     days, sum(sum(temperature)) over pn1week / sum(count(temperature)) over pn1week
            from       p
            cross join generate_series(min - interval '1 week', max + interval '1 week', interval '1 day') days
            left join  temperatures on site_id = 1 and extract(doy from timestamp) = extract(doy from days)
            group by   days
            window     pn1week as (order by days rows between 7 preceding and 7 following)) t
where      days between min and max
order by   days


absolute service time: 1,06 sec

	count	count
1	5400	1854

	QUERY PLAN
1	Function Scan on generate_series days (cost=0.00..41003.45 rows=1000 width=40) (actual time=5.831..489.503 rows=123 loops=1)
2	SubPlan 1
3	-> Aggregate (cost=40.98..40.99 rows=1 width=32) (actual time=3.975..3.975 rows=1 loops=123)
4	-> Bitmap Heap Scan on temperatures (cost=4.46..40.98 rows=1 width=32) (actual time=0.374..3.920 rows=75 loops=123)
5	Recheck Cond: (site_id = 1)
6	Filter: ((date_part('doy'::text, "timestamp") >= (date_part('doy'::text, days.days) - '7'::double precision)) AND (date_part('doy'::text, "timestamp") <= (date_part('doy'::text, days.days) + '7'::double precision)))
7	Rows Removed by Filter: 1779
8	Heap Blocks: exact=4920
9	-> Bitmap Index Scan on temperatures_pkey (cost=0.00..4.46 rows=23 width=0) (actual time=0.184..0.184 rows=1854 loops=123)
10	Index Cond: (site_id = 1)
11	Planning time: 0.382 ms
12	Execution time: 489.638 ms

	QUERY PLAN
1	Sort (cost=99.65..99.71 rows=22 width=40) (actual time=3.732..3.741 rows=123 loops=1)
2	Sort Key: days.days
3	Sort Method: quicksort Memory: 34kB
4	CTE p
5	-> Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)
6	-> Nested Loop (cost=89.63..99.15 rows=22 width=40) (actual time=3.382..3.697 rows=123 loops=1)
7	Join Filter: ((days.days >= p.min) AND (days.days <= p.max))
8	Rows Removed by Join Filter: 14
9	-> CTE Scan on p (cost=0.00..0.02 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)
10	-> WindowAgg (cost=89.63..94.13 rows=200 width=40) (actual time=3.359..3.660 rows=137 loops=1)
11	-> Sort (cost=89.63..90.13 rows=200 width=48) (actual time=3.334..3.345 rows=137 loops=1)
12	Sort Key: days.days
13	Sort Method: quicksort Memory: 35kB
14	-> HashAggregate (cost=79.49..81.99 rows=200 width=48) (actual time=3.252..3.284 rows=137 loops=1)
15	Group Key: days.days
16	-> Hash Left Join (cost=40.82..71.99 rows=1000 width=40) (actual time=1.954..2.898 rows=689 loops=1)
17	Hash Cond: (date_part('doy'::text, days.days) = date_part('doy'::text, temperatures."timestamp"))
18	-> Nested Loop (cost=0.01..20.03 rows=1000 width=8) (actual time=0.082..0.120 rows=137 loops=1)
19	-> CTE Scan on p p_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)
20	-> Function Scan on generate_series days (cost=0.01..10.01 rows=1000 width=8) (actual time=0.080..0.098 rows=137 loops=1)
21	-> Hash (cost=40.52..40.52 rows=23 width=40) (actual time=1.837..1.837 rows=1854 loops=1)
22	Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 118kB
23	-> Bitmap Heap Scan on temperatures (cost=4.46..40.52 rows=23 width=40) (actual time=0.151..0.581 rows=1854 loops=1)
24	Recheck Cond: (site_id = 1)
25	Heap Blocks: exact=40
26	-> Bitmap Index Scan on temperatures_pkey (cost=0.00..4.46 rows=23 width=0) (actual time=0.139..0.139 rows=1854 loops=1)
27	Index Cond: (site_id = 1)
28	Planning time: 0.483 ms
29	Execution time: 3.999 ms