What alternatives are there to replace the use of CASE, and will also improve the speed of the query.
SELECT
created_at,
COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk,
COUNT((CASE WHEN cp = 1 THEN 1 END)) AS first,
COUNT((CASE WHEN (cp > 1 OR cp = 0) AND pp = 1 THEN 1 END)) AS first_out,
COUNT((CASE WHEN cp = 1 AND (pp > 1 OR pp = 0) THEN 1 END)) AS first_in,
COUNT((CASE WHEN cp = 2 THEN 1 END)) AS second,
COUNT((CASE WHEN cp > 2 AND pp = 2 THEN 1 END)) AS second_out,
COUNT((CASE WHEN cp = 2 AND pp > 2 THEN 1 END)) AS second_in,
COUNT((CASE WHEN cp = 2 AND pp > 2 AND state = 'inter' THEN 1 END)) AS second_in_state,
FROM (
SELECT
created_at::date,
keyword_id,
min(pos) as pp,
min(y_pos) as cp,
(CASE WHEN min(pos) != min(y_pos) THEN (SELECT change_state FROM rk.change_state(min(pos), min(y_pos)))) END as state
FROM rkre.p_reports_1
WHERE
project_id = 1 AND engine_id = 1 AND type_id = 1
AND created_at >= current_date - 14
AND created_at <= current_date + 1
GROUP BY 1, 2) records
GROUP BY created_at
Only the subquery:
HashAggregate (cost=19923.35..22539.67 rows=9344 width=16) (actual time=109.270..390.802 rows=81705 loops=1)
Group Key: (p_reports_1.created_at)::date, p_reports_1.keyword_id
-> Bitmap Heap Scan on p_reports_1 (cost=3223.27..18211.99 rows=85568 width=16) (actual time=13.971..46.560 rows=92671 loops=1)
Recheck Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= (('now'::cstring)::date - 14)) AND (created_at <= (('now'::cstring)::date + 1)))
Heap Blocks: exact=1264
-> Bitmap Index Scan on p_reports_1_index_on_columns (cost=0.00..3201.88 rows=85568 width=0) (actual time=13.744..13.744 rows=98757 loops=1)
Index Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= (('now'::cstring)::date - 14)) AND (created_at <= (('now'::cstring)::date + 1)))
SubPlan 1
-> Function Scan on change_state (cost=0.26..0.27 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=25923)
Planning time: 0.438 ms
Execution time: 397.723 ms
Having all those counts with cases in total 30 count(case) produced:
HashAggregate (cost=25415.74..25420.24 rows=200 width=40) (actual time=1119.734..1119.743 rows=15 loops=1)
Group Key: (date_part('epoch'::text, timezone('utc'::text, (records.created_at)::timestamp with time zone)) * 1000::double precision)
-> Subquery Scan on records (cost=20141.56..23071.66 rows=9767 width=40) (actual time=121.611..607.528 rows=81705 loops=1)
-> HashAggregate (cost=20141.56..22876.32 rows=9767 width=16) (actual time=121.591..539.761 rows=81705 loops=1)
Group Key: (p_reports_1.created_at)::date, p_reports_1.keyword_id
-> Bitmap Heap Scan on p_reports_1 (cost=3364.04..18352.72 rows=89442 width=16) (actual time=15.296..50.630 rows=92607 loops=1)
Recheck Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= (('now'::cstring)::date - 14)) AND (created_at <= (('now'::cstring)::date + 1)))
Heap Blocks: exact=1186
-> Bitmap Index Scan on p_reports_1_index_on_columns (cost=0.00..3341.68 rows=89442 width=0) (actual time=15.075..15.075 rows=92607 loops=1)
Index Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= (('now'::cstring)::date - 14)) AND (created_at <= (('now'::cstring)::date + 1)))
SubPlan 1
-> Function Scan on change_state (cost=0.26..0.27 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=25780)
Planning time: 0.637 ms
Execution time: 1139.618 ms
I am using Postgresql 9.4.