Count rows within a group, but also from global result set: performance issue, PostgreSQL

Count rows within a group, but also from global result set: performanc...

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

DROP TABLE IF EXISTS logs;

CREATE TABLE logs (
  id          BIGSERIAL PRIMARY KEY,
  status      VARCHAR NOT NULL,
  inserted_at DATE NOT NULL
);

INSERT INTO logs (status, inserted_at) VALUES
  ('created', '2017-01-01'),
  ('created', '2017-01-01'),
  ('closed', '2017-01-02'),
  ('created', '2017-01-02'),
  ('created', '2017-01-02'),
  ('created', '2017-01-03'),
  ('closed', '2017-01-03'),
  ('created', '2017-01-04'),
  ('created', '2017-01-05'),
  ('created', '2017-01-06'),
  ('created', '2017-01-07'),
  ('closed', '2017-01-08');

SELECT days.day,
          count(case when logs.inserted_at  = days.day AND logs.status = 'created' then 1 end) as created,
          count(case when logs.inserted_at  = days.day AND logs.status = 'closed' then 1 end) as closed,
          count(case when logs.inserted_at <= days.day AND logs.status = 'created' then 1 end) -
          count(case when logs.inserted_at <= days.day AND logs.status = 'closed' then 1 end) as total
     FROM (SELECT day::date FROM generate_series('2017-01-01'::date, '2017-01-10'::date, '1 day'::interval) day) days,
          logs
 GROUP BY days.day
 ORDER BY days.day;
 
 EXPLAIN ANALYZE
    SELECT days.day,
          count(case when logs.inserted_at  = days.day AND logs.status = 'created' then 1 end) as created,
          count(case when logs.inserted_at  = days.day AND logs.status = 'closed' then 1 end) as closed,
          count(case when logs.inserted_at <= days.day AND logs.status = 'created' then 1 end) -
          count(case when logs.inserted_at <= days.day AND logs.status = 'closed' then 1 end) as total
     FROM (SELECT day::date FROM generate_series('2017-01-01'::date, '2017-01-10'::date, '1 day'::interval) day) days,
          logs
 GROUP BY days.day
 ORDER BY days.day;

select s.*, sum(created - closed) over (order by inserted_at)
from   (select    inserted_at,
                  count(status) filter (where status = 'created') created,
                  count(status) filter (where status = 'closed')  closed
        from      (select d::date inserted_at from generate_series('2017-01-01'::date, '2017-01-10'::date, '1 day'::interval) d) d
        left join logs using (inserted_at)
        group by  inserted_at) s;

explain analyze
select s.*, sum(created - closed) over (order by inserted_at)
from   (select    inserted_at,
                  count(status) filter (where status = 'created') created,
                  count(status) filter (where status = 'closed')  closed
        from      (select d::date inserted_at from generate_series('2017-01-01'::date, '2017-01-10'::date, '1 day'::interval) d) d
        left join logs using (inserted_at)
        group by  inserted_at) s;


absolute service time: 0,45 sec

	day	created	closed	total
1	01.01.2017 00:00:00	2	0	2
2	02.01.2017 00:00:00	2	1	3
3	03.01.2017 00:00:00	1	1	3
4	04.01.2017 00:00:00	1	0	4
5	05.01.2017 00:00:00	1	0	5
6	06.01.2017 00:00:00	1	0	6
7	07.01.2017 00:00:00	1	0	7
8	08.01.2017 00:00:00	0	1	6
9	09.01.2017 00:00:00	0	0	6
10	10.01.2017 00:00:00	0	0	6

	QUERY PLAN
1	Sort (cost=65019.78..65020.28 rows=200 width=44) (actual time=0.368..0.369 rows=10 loops=1)
2	Sort Key: ((day.day)::date)
3	Sort Method: quicksort Memory: 25kB
4	-> HashAggregate (cost=65009.13..65012.13 rows=200 width=44) (actual time=0.334..0.338 rows=10 loops=1)
5	Group Key: (day.day)::date
6	-> Nested Loop (cost=0.01..16984.13 rows=1130000 width=44) (actual time=0.043..0.151 rows=120 loops=1)
7	-> Function Scan on generate_series day (cost=0.01..10.01 rows=1000 width=8) (actual time=0.022..0.027 rows=10 loops=1)
8	-> Materialize (cost=0.00..26.95 rows=1130 width=36) (actual time=0.001..0.004 rows=12 loops=10)
9	-> Seq Scan on logs (cost=0.00..21.30 rows=1130 width=36) (actual time=0.007..0.011 rows=12 loops=1)
10	Planning time: 0.196 ms
11	Execution time: 0.645 ms

	inserted_at	created	closed	sum
1	01.01.2017 00:00:00	2	0	2
2	02.01.2017 00:00:00	2	1	3
3	03.01.2017 00:00:00	1	1	3
4	04.01.2017 00:00:00	1	0	4
5	05.01.2017 00:00:00	1	0	5
6	06.01.2017 00:00:00	1	0	6
7	07.01.2017 00:00:00	1	0	7
8	08.01.2017 00:00:00	0	1	6
9	09.01.2017 00:00:00	0	0	6
10	10.01.2017 00:00:00	0	0	6

	QUERY PLAN
1	WindowAgg (cost=138.44..337.56 rows=200 width=20) (actual time=0.108..0.144 rows=10 loops=1)
2	-> GroupAggregate (cost=138.44..332.06 rows=200 width=40) (actual time=0.085..0.108 rows=10 loops=1)
3	Group Key: ((d.d)::date)
4	-> Merge Left Join (cost=138.44..258.94 rows=5650 width=40) (actual time=0.070..0.081 rows=14 loops=1)
5	Merge Cond: (((d.d)::date) = logs.inserted_at)
6	-> Sort (cost=59.84..62.34 rows=1000 width=8) (actual time=0.040..0.042 rows=10 loops=1)
7	Sort Key: ((d.d)::date)
8	Sort Method: quicksort Memory: 25kB
9	-> Function Scan on generate_series d (cost=0.01..10.01 rows=1000 width=8) (actual time=0.022..0.028 rows=10 loops=1)
10	-> Sort (cost=78.60..81.43 rows=1130 width=36) (actual time=0.027..0.029 rows=12 loops=1)
11	Sort Key: logs.inserted_at
12	Sort Method: quicksort Memory: 25kB
13	-> Seq Scan on logs (cost=0.00..21.30 rows=1130 width=36) (actual time=0.006..0.010 rows=12 loops=1)
14	Planning time: 0.214 ms
15	Execution time: 0.264 ms