title: Postgresql count performance
body: <p>I am doing a count query on a postgresql table. Table name is simcards containing fields id, card_state and 10 more. Simcards contains around 13 million records</p>
  
  <p>My query is</p>
  
  <pre><code>SELECT CAST(count(*) AS INT) FROM simcards WHERE card_state = 'ACTIVATED';
  </code></pre>
  
  <p>This is taking more than 6 seconds and I want to optimize it. I tried creating partial index below</p>
  
  <pre><code>CREATE INDEX activated_count on simcards (card_state) where card_state = 'ACTIVATED';
  </code></pre>
  
  <p>But no improvements. I think it is because I got more than 12 million records with card_state = 'ACTIVATED'. Note that card_state can be 'ACTIVATED', 'PREPROVISIONED', 'TERMINATED'</p>
  
  <p>Anyone got an idea on how the count can be drastically improved?</p>
  
  <p>Running <code>EXPLAIN (ANALYZE, BUFFERS) SELECT CAST(count(*) AS INT) FROM simcards WHERE card_state = 'ACTIVATED';</code> gives</p>
  
  <pre><code>Finalize Aggregate  (cost=540300.95..540300.96 rows=1 width=4) (actual time=7103.814..7103.814 rows=1 loops=1)
    Buffers: shared hit=2295 read=155298
    -&gt;  Gather  (cost=540300.74..540300.95 rows=2 width=8) (actual time=7103.773..7103.810 rows=3 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          Buffers: shared hit=2295 read=155298
          -&gt;  Partial Aggregate  (cost=539300.74..539300.75 rows=1 width=8) (actual time=7006.368..7006.368 rows=1 loops=3)
                Buffers: shared hit=5983 read=455025
                -&gt;  Parallel Seq Scan on simcards  (cost=0.00..526282.77 rows=5207186 width=0) (actual time=2.677..6483.503 rows=4166620 loops=3)
                      Filter: (card_state = 'ACTIVATED'::text)
                      Rows Removed by Filter: 10965
                      Buffers: shared hit=5983 read=455025
  Planning time: 0.333 ms
  Execution time: 7123.739 ms
  </code></pre>