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      timestamp 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'::timestamp, '2017-05-24'::timestamp, 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;

create index idx_temperatures_site_id_timestamp_doy on temperatures(site_id, extract(doy from timestamp));

explain analyze
with p as (
  select timestamp '2017-05-01' min,
         timestamp '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: 0,96 sec

	count	count
1	5401	1797

	QUERY PLAN
1	Function Scan on generate_series days (cost=0.00..41003.45 rows=1000 width=40) (actual time=4.232..327.528 rows=123 loops=1)
2	SubPlan 1
3	-> Aggregate (cost=40.98..40.99 rows=1 width=32) (actual time=2.658..2.658 rows=1 loops=123)
4	-> Bitmap Heap Scan on temperatures (cost=4.46..40.98 rows=1 width=32) (actual time=0.276..2.605 rows=72 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: 1725
8	Heap Blocks: exact=4920
9	-> Bitmap Index Scan on temperatures_pkey (cost=0.00..4.46 rows=23 width=0) (actual time=0.155..0.155 rows=1797 loops=123)
10	Index Cond: (site_id = 1)
11	Planning time: 0.366 ms
12	Execution time: 327.713 ms

	QUERY PLAN
1	Function Scan on generate_series days (cost=0.00..8340.00 rows=1000 width=40) (actual time=0.207..9.159 rows=123 loops=1)
2	SubPlan 1
3	-> Aggregate (cost=8.32..8.33 rows=1 width=32) (actual time=0.072..0.072 rows=1 loops=123)
4	-> Index Scan using idx_temperatures_site_id_timestamp_doy on temperatures (cost=0.29..8.31 rows=1 width=32) (actual time=0.012..0.046 rows=72 loops=123)
5	Index Cond: ((site_id = 1) AND (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)))
6	Planning time: 0.549 ms
7	Execution time: 9.272 ms

	QUERY PLAN
1	Sort (cost=106.31..106.37 rows=22 width=40) (actual time=4.314..4.322 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=96.29..105.81 rows=22 width=40) (actual time=3.987..4.271 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=96.29..100.79 rows=200 width=40) (actual time=3.965..4.237 rows=137 loops=1)
11	-> Sort (cost=96.29..96.79 rows=200 width=48) (actual time=3.935..3.952 rows=137 loops=1)
12	Sort Key: days.days
13	Sort Method: quicksort Memory: 35kB
14	-> HashAggregate (cost=86.15..88.65 rows=200 width=48) (actual time=3.845..3.876 rows=137 loops=1)
15	Group Key: days.days
16	-> Hash Left Join (cost=43.53..78.65 rows=1000 width=40) (actual time=2.584..3.346 rows=666 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.074..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.001 rows=1 loops=1)
20	-> Function Scan on generate_series days (cost=0.01..10.01 rows=1000 width=8) (actual time=0.071..0.092 rows=137 loops=1)
21	-> Hash (cost=43.18..43.18 rows=27 width=40) (actual time=2.457..2.457 rows=1797 loops=1)
22	Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
23	-> Bitmap Heap Scan on temperatures (cost=4.49..43.18 rows=27 width=40) (actual time=0.333..0.992 rows=1797 loops=1)
24	Recheck Cond: (site_id = 1)
25	Heap Blocks: exact=40
26	-> Bitmap Index Scan on idx_temperatures_site_id_timestamp_doy (cost=0.00..4.48 rows=27 width=0) (actual time=0.313..0.313 rows=1797 loops=1)
27	Index Cond: (site_id = 1)
28	Planning time: 0.708 ms
29	Execution time: 4.756 ms