1

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.

alexsmn
  • 353
  • 1
  • 3
  • 14

0 Answers0