title: Improve PostgresSQL aggregation query performance
body: <p>I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second. </p>
  
  <p>Please find below the execution details:</p>
  
  <hr>
  
  <p><strong>Query</strong></p>
  
  <pre><code>select
      a.search_keyword,
      hll_cardinality( hll_union_agg(a.users) ):: int as user_count,
      hll_cardinality( hll_union_agg(a.sessions) ):: int as session_count,
      sum(a.total) as keyword_count
  from
      rollup_day a
  where
      a.created_date between '2018-09-01' and '2019-09-30'
      and a.tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'
  group by
      a.search_keyword
  order by
      session_count desc
  limit 100;
  </code></pre>
  
  <hr>
  
  <p><strong>Table metadata</strong></p>
  
  <ol>
  <li>Total number of rows - <strong>506527</strong></li>
  <li>Composite Index on columns : <strong>tenant_id and created_date</strong></li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/8RkDW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8RkDW.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p><strong>Query plan</strong></p>
  
  <pre><code>Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=1722.685..1722.694 rows=100 loops=1)
    Task Count: 1
    Tasks Shown: All
    -&gt;  Task
          Node: host=localhost port=5454 dbname=postgres
          -&gt;  Limit  (cost=64250.24..64250.49 rows=100 width=42) (actual time=1783.087..1783.106 rows=100 loops=1)
                -&gt;  Sort  (cost=64250.24..64558.81 rows=123430 width=42) (actual time=1783.085..1783.093 rows=100 loops=1)
                      Sort Key: ((hll_cardinality(hll_union_agg(sessions)))::integer) DESC
                      Sort Method: top-N heapsort  Memory: 33kB
                      -&gt;  GroupAggregate  (cost=52933.89..59532.83 rows=123430 width=42) (actual time=905.502..1724.363 rows=212633 loops=1)
                            Group Key: search_keyword
                            -&gt;  Sort  (cost=52933.89..53636.53 rows=281055 width=54) (actual time=905.483..1351.212 rows=280981 loops=1)
                                  Sort Key: search_keyword
                                  Sort Method: external merge  Disk: 18496kB
                                  -&gt;  Seq Scan on rollup_day a  (cost=0.00..17890.22 rows=281055 width=54) (actual time=29.720..112.161 rows=280981 loops=1)
                                        Filter: ((created_date &gt;= '2018-09-01'::date) AND (created_date &lt;= '2019-09-30'::date) AND (tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'::uuid))
                                        Rows Removed by Filter: 225546
              Planning Time: 0.129 ms
              Execution Time: 1786.222 ms
  Planning Time: 0.103 ms
  Execution Time: 1722.718 ms
  </code></pre>
  
  <hr>
  
  <p><strong>What I've tried</strong></p>
  
  <ol>
  <li>I've tried with indexes on <strong>tenant_id and created_date</strong> but as the data is huge so it's always doing sequence scan rather than an index scan for filters. I've read about it and found, the Postgres query engine switch to sequence scan if the data returned is > <strong>5-10%</strong> of the total rows. Please follow the link for more <a href="https://stackoverflow.com/questions/5203755/why-does-postgresql-perform-sequential-scan-on-indexed-column">reference</a>.</li>
  <li>I've increased the <strong>work_mem</strong> to <strong>100MB</strong> but it only improved the performance a little bit.</li>
  </ol>
  
  <p>Any help would be really appreciated.</p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <blockquote>
    <p>Query plan after setting <strong>work_mem to 100MB</strong></p>
  </blockquote>
  
  <pre><code>Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=1375.926..1375.935 rows=100 loops=1)
    Task Count: 1
    Tasks Shown: All
    -&gt;  Task
          Node: host=localhost port=5454 dbname=postgres
          -&gt;  Limit  (cost=48348.85..48349.10 rows=100 width=42) (actual time=1307.072..1307.093 rows=100 loops=1)
                -&gt;  Sort  (cost=48348.85..48633.55 rows=113880 width=42) (actual time=1307.071..1307.080 rows=100 loops=1)
                      Sort Key: (sum(total)) DESC
                      Sort Method: top-N heapsort  Memory: 35kB
                      -&gt;  GroupAggregate  (cost=38285.79..43996.44 rows=113880 width=42) (actual time=941.504..1261.177 rows=172945 loops=1)
                            Group Key: search_keyword
                            -&gt;  Sort  (cost=38285.79..38858.52 rows=229092 width=54) (actual time=941.484..963.061 rows=227261 loops=1)
                                  Sort Key: search_keyword
                                  Sort Method: quicksort  Memory: 32982kB
                                  -&gt;  Seq Scan on rollup_day_104290 a  (cost=0.00..17890.22 rows=229092 width=54) (actual time=38.803..104.350 rows=227261 loops=1)
                                        Filter: ((created_date &gt;= '2019-01-01'::date) AND (created_date &lt;= '2019-12-30'::date) AND (tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'::uuid))
                                        Rows Removed by Filter: 279266
              Planning Time: 0.131 ms
              Execution Time: 1308.814 ms
  Planning Time: 0.112 ms
  Execution Time: 1375.961 ms
  </code></pre>
  
  <hr>
  
  <p><strong>Update 2</strong></p>
  
  <blockquote>
    <p>After creating an <strong>index on created_date</strong> and increased <strong>work_mem to 120MB</strong></p>
  </blockquote>
  
  <p><code>create index date_idx on rollup_day(created_date);</code></p>
  
  <p>The total number of rows is: <strong>12,124,608</strong></p>
  
  <p><strong>Query Plan is:</strong></p>
  
  <pre><code>Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=2635.530..2635.540 rows=100 loops=1)
    Task Count: 1
    Tasks Shown: All
    -&gt;  Task
          Node: host=localhost port=9702 dbname=postgres
          -&gt;  Limit  (cost=73545.19..73545.44 rows=100 width=51) (actual time=2755.849..2755.873 rows=100 loops=1)
                -&gt;  Sort  (cost=73545.19..73911.25 rows=146424 width=51) (actual time=2755.847..2755.858 rows=100 loops=1)
                      Sort Key: (sum(total)) DESC
                      Sort Method: top-N heapsort  Memory: 35kB
                      -&gt;  GroupAggregate  (cost=59173.97..67948.97 rows=146424 width=51) (actual time=2014.260..2670.732 rows=296537 loops=1)
                            Group Key: search_keyword
                            -&gt;  Sort  (cost=59173.97..60196.85 rows=409152 width=55) (actual time=2013.885..2064.775 rows=410618 loops=1)
                                  Sort Key: search_keyword
                                  Sort Method: quicksort  Memory: 61381kB
                                  -&gt;  Index Scan using date_idx_102913 on rollup_day_102913 a  (cost=0.42..21036.35 rows=409152 width=55) (actual time=0.026..183.370 rows=410618 loops=1)
                                        Index Cond: ((created_date &gt;= '2018-01-01'::date) AND (created_date &lt;= '2018-12-31'::date))
                                        Filter: (tenant_id = '12850a62-19ac-477d-9cd7-837f3d716885'::uuid)
              Planning Time: 0.135 ms
              Execution Time: 2760.667 ms
  Planning Time: 0.090 ms
  Execution Time: 2635.568 ms
  </code></pre>