title: Aggregate query on 50M+ row table in PostgreSQL
body: <h1>Problem statement</h1>
  
  <p>I have table "event_statistics" with following definition:</p>
  
  <pre><code>CREATE TABLE public.event_statistics (
      id int4 NOT NULL DEFAULT nextval('event_statistics_id_seq'::regclass),
      client_id int4 NULL,
      session_id int4 NULL,
      action_name text NULL,
      value text NULL,
      product_id int8 NULL,
      product_options jsonb NOT NULL DEFAULT '{}'::jsonb,
      url text NULL,
      url_options jsonb NOT NULL DEFAULT '{}'::jsonb,
      visit int4 NULL DEFAULT 0,
      date_update timestamptz NULL,
  CONSTRAINT event_statistics_pkey PRIMARY KEY (id),
  CONSTRAINT event_statistics_client_id_session_id_sessions_client_id_id_for 
  FOREIGN KEY 
  (client_id,session_id) REFERENCES &lt;?&gt;() ON DELETE CASCADE ON UPDATE CASCADE
  )
  WITH (
      OIDS=FALSE
  ) ;
  CREATE INDEX regdate ON public.event_statistics (date_update 
  timestamptz_ops) ;
  </code></pre>
  
  <p>And table "clients":</p>
  
  <pre><code>CREATE TABLE public.clients (
      id int4 NOT NULL DEFAULT nextval('clients_id_seq'::regclass),
      client_name text NULL,
      client_hash text NULL,
  CONSTRAINT clients_pkey PRIMARY KEY (id)
  )
  WITH (
      OIDS=FALSE
  ) ;
  CREATE INDEX clients_client_name_idx ON public.clients (client_name 
  text_ops) ;
  </code></pre>
  
  <p>What I need is to get count of events in "event_statistics" table of each "action_name" type for specific "date_update" range grouping by "action_name" and specific time step and all of that for specific client.</p>
  
  <p>The goal is to provide statistics for all relevant events for each client on his dashboard on our web site with option to choose report dates and depending on interval time step in chart should be different like: </p>
  
  <ul>
  <li>current day — count for each hour; </li>
  <li>1+ day and &lt;= 1 month — count for each day; </li>
  <li>1+ month and &lt;= 6 months — count for each week; </li>
  <li>6+ months — month. </li>
  </ul>
  
  <h1>What I did:</h1>
  
  <pre><code>SELECT t.date, A.actionName, count(E.id)
  FROM generate_series(current_date - interval '1 week',now(),interval '1 
  day') as t(date) cross join
  (values
  ('page_open'),
  ('product_add'),
  ('product_buy'),
  ('product_event'),
  ('product_favourite'),
  ('product_open'),
  ('product_share'),
  ('session_start')) as A(actionName) left join
  (select action_name,date_trunc('day',e.date_update) as dateTime, e.id 
  from event_statistics as e 
  where e.client_id = (select id from clients as c where c.client_name = 
  'client name') and 
  (date_update between (current_date - interval '1 week') and now())) E 
  on t.date = E.dateTime and A.actionName = E.action_name
  group by A.actionName,t.date
  order by A.actionName,t.date;
  </code></pre>
  
  <p>It takes too long, more than 10 seconds, to count events by event type and day for last week. I need it to be able to do the same faster and for wider periods of time like weeks, months, years with different group interval(each hour of current day, days for month, then weeks, months).</p>
  
  <p>The query plan:</p>
  
  <pre><code>GroupAggregate  (cost=171937.16..188106.84 rows=1600 width=44)
    Group Key: "*VALUES*".column1, t.date
    InitPlan 1 (returns $0)
      -&gt;  Seq Scan on clients c  (cost=0.00..1.07 rows=1 width=4)
            Filter: (client_name = 'client name'::text)
    -&gt;  Merge Left Join  (cost=171936.08..183784.31 rows=574060 width=44)
          Merge Cond: (("*VALUES*".column1 = e.action_name) AND (t.date =(date_trunc('day'::text, e.date_update))))
          -&gt;  Sort  (cost=628.77..648.77 rows=8000 width=40)
                Sort Key: "*VALUES*".column1, t.date
                -&gt;  Nested Loop  (cost=0.02..110.14 rows=8000 width=40)
                      -&gt;  Function Scan on generate_series t (cost=0.02..10.02 rows=1000 width=8)
                      -&gt;  Materialize  (cost=0.00..0.14 rows=8 width=32)
                            -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.10 rows=8 width=32)
          -&gt;  Materialize  (cost=171307.32..171881.38 rows=114812 width=24)
                -&gt;  Sort  (cost=171307.32..171594.35 rows=114812 width=24)
                      Sort Key: e.action_name, (date_trunc('day'::text, e.date_update))
                      -&gt;  Index Scan using regdate on event_statistics e (cost=0.57..159302.49 rows=114812 width=24)
                            Index Cond: ((date_update &gt; (('now'::cstring)::date - '7 days'::interval)) AND (date_update &lt;= now()))
                            Filter: (client_id = $0)
  </code></pre>
  
  <p>The "event_statistics" table has more than 50 millions of rows and it will only grow with clients added and the records will not be changed.</p>
  
  <p>I've tried a lot of different query plans and indexes but couldn't reach acceptable speed when aggregated for wider date range.
  I've spent whole week learning different aspects of this problem and ways to solve this on stackoverflow and some blogs, but still unsure what is the best way: </p>
  
  <ul>
  <li>partitioning by client_id or date range </li>
  <li>pre-aggregate to separate result table and then update it on a daily basis(also not sure how to do it best? trigger on insert into original table or schedule a separate app for that or materialized view or by a request from the web site)</li>
  <li>change DB schema design to schema per client or apply sharding </li>
  <li>change server hardware(CPU Intel Xeon E7-4850 2.00GHz, RAM 6GB and it's the host of both web app and the DB)</li>
  <li>use a different DB for analytics with OLAP capabilities like Postgres-XL 
  or something else?</li>
  </ul>
  
  <p>I also tried btree index on event_statistics (client_id asc, action_name asc, date_update asc, id). And it was faster with index only scan, but still not enough and it's not very good in terms of disk space usage.</p>
  
  <p><strong>What is the best way to solve this problem?</strong> </p>
  
  <h1>Update</h1>
  
  <p>As requested, the output of <code>explain (analyze, verbose)</code> command:</p>
  
  <pre><code>GroupAggregate  (cost=860934.44..969228.46 rows=1600 width=44) (actual time=52388.678..54671.187 rows=64 loops=1)
    Output: t.date, "*VALUES*".column1, count(e.id)
    Group Key: "*VALUES*".column1, t.date
    InitPlan 1 (returns $0)
      -&gt;  Seq Scan on public.clients c  (cost=0.00..1.07 rows=1 width=4) (actual time=0.058..0.059 rows=1 loops=1)
            Output: c.id
            Filter: (c.client_name = 'client name'::text)
            Rows Removed by Filter: 5
    -&gt;  Merge Left Join  (cost=860933.36..940229.77 rows=3864215 width=44) (actual time=52388.649..54388.698 rows=799737 loops=1)
          Output: t.date, "*VALUES*".column1, e.id
          Merge Cond: (("*VALUES*".column1 = e.action_name) AND (t.date = (date_trunc('day'::text, e.date_update))))
          -&gt;  Sort  (cost=628.77..648.77 rows=8000 width=40) (actual time=0.190..0.244 rows=64 loops=1)
                Output: t.date, "*VALUES*".column1
                Sort Key: "*VALUES*".column1, t.date
                Sort Method: quicksort  Memory: 30kB
                -&gt;  Nested Loop  (cost=0.02..110.14 rows=8000 width=40) (actual time=0.059..0.080 rows=64 loops=1)
                      Output: t.date, "*VALUES*".column1
                      -&gt;  Function Scan on pg_catalog.generate_series t  (cost=0.02..10.02 rows=1000 width=8) (actual time=0.043..0.043 rows=8 loops=1)
                            Output: t.date
                            Function Call: generate_series(((('now'::cstring)::date - '7 days'::interval))::timestamp with time zone, now(), '1 day'::interval)
                      -&gt;  Materialize  (cost=0.00..0.14 rows=8 width=32) (actual time=0.002..0.003 rows=8 loops=8)
                            Output: "*VALUES*".column1
                            -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.10 rows=8 width=32) (actual time=0.004..0.005 rows=8 loops=1)
                                  Output: "*VALUES*".column1
          -&gt;  Materialize  (cost=860304.60..864168.81 rows=772843 width=24) (actual time=52388.441..54053.748 rows=799720 loops=1)
                Output: e.id, e.date_update, e.action_name, (date_trunc('day'::text, e.date_update))
                -&gt;  Sort  (cost=860304.60..862236.70 rows=772843 width=24) (actual time=52388.432..53703.531 rows=799720 loops=1)
                      Output: e.id, e.date_update, e.action_name, (date_trunc('day'::text, e.date_update))
                      Sort Key: e.action_name, (date_trunc('day'::text, e.date_update))
                      Sort Method: external merge  Disk: 39080kB
                      -&gt;  Index Scan using regdate on public.event_statistics e  (cost=0.57..753018.26 rows=772843 width=24) (actual time=31.423..44284.363 rows=799720 loops=1)
                            Output: e.id, e.date_update, e.action_name, date_trunc('day'::text, e.date_update)
                            Index Cond: ((e.date_update &gt;= (('now'::cstring)::date - '7 days'::interval)) AND (e.date_update &lt;= now()))
                            Filter: (e.client_id = $0)
                            Rows Removed by Filter: 2983424
  Planning time: 7.278 ms
  Execution time: 54708.041 ms
  </code></pre>