0

I'm using a select count(*) on a table with 800k rows. The table has id column as the primary key. The results take about 800ms. Is there a way to get better performance? I have given the explain analyze below.

"Aggregate  (cost=41581.76..41581.77 rows=1 width=0) (actual time=769.059..769.059 rows=1 loops=1)"
"  ->  Seq Scan on servicereports  (cost=0.00..39659.61 rows=768861 width=0) (actual time=0.018..610.502 rows=768861 loops=1)"
"Total runtime: 769.208 ms"
  1. I use the count in showing a pagination control in a webapp.
  2. While total is count is needed, count of a filtered subset is also needed.

What I have tried so far: Increased shared_buffers from 128MB to 512 MB. But there is no improvement.

Raj
  • 135
  • 2
  • 11
  • Have you vacuumed the table? Also, is it important that the answer be exact.. or is a very close number good enough? – Joe Love Feb 18 '15 at 05:50
  • 1
    Did you turn off enable_seqscan? What were the results? SET enable_seqscan TO off; – Frank Heikens Feb 18 '15 at 08:15
  • 1
    How does your question differ from: [If PostgreSQL count(*) is always slow how to paginate complex queries?](http://stackoverflow.com/questions/16916633) – Daniel Vérité Feb 18 '15 at 14:22
  • @JoeLove: Yes. the answer must be exact. Vacuum helped bring it down to 400 ms. Thanks much! – Raj Feb 19 '15 at 05:18
  • @FrankHeikens: Thanks for your suggestion. I get almost similar results between enable_seqscan off and on.457ms when it is off, 437ms when it is on. – Raj Feb 19 '15 at 05:33
  • @FrankHeikens: the solution suggested in that question was to upgrade to 9.2. But I am already using 9.3. Looking for further optimization. – Raj Feb 19 '15 at 05:39
  • Do you have a ton of daily transactions? IE, could we put a trigger to keep track of the counts so we have a 1 row table with accurate results? – Joe Love Feb 19 '15 at 06:10
  • 1
    @JoeLove: the problem is that the page contains filters. The number of combinations of these filters is a lot, so keeping counts in a separate table may not be a viable option. – Raj Feb 20 '15 at 09:58

0 Answers0