Slow count(*) on postgresql 9.2

Question

There are a few discussions this and there (including the official post on postgres web) about the slow count(*) prior version 9.2; somehow I did not find satisfied answer.

Basically I had postgres 9.1 installed, and I observed slow count(*) as simple as

select count(*) from restaurants;

on tables with records of 100k+. The average request is around 850ms. Well I assumed that that was the symptom people have been talking about for slow count on postgres 9.1 and below since postgres 9.2 has some new feature like index-only scan. I want to experiment this by using the same dataset from 9.1 and put it on 9.2. I call the count statement, and it still give a bad result as 9.1.

explain analyze select count(*) from restaurants;
------------------------------------------------------------------
Aggregate  (cost=23510.35..23510.36 rows=1 width=0) (actual time=979.960..979.961 rows=1 loops=1)
   ->  Seq Scan on restaurants  (cost=0.00..23214.88 rows=118188 width=0) (actual time=0.050..845.097 rows=118188 loops=1)
 Total runtime: 980.037 ms

Can anyone suggest feasible solution to this problem? Do I need to configure anything on postgres to enable the feature?

P.S. where clause doesn't help in my case either.

Answer 1

See the index only scans wiki entries:

• What types of queries may be satisfied by an index-only scan?
• is count(*) much faster now?
• Why isn't my query using an index-only scan?

In particular, I quote:

It is important to realise that the planner is concerned with minimising the total cost of the query. With databases, the cost of I/O typically dominates. For that reason, "count(*) without any predicate" queries will only use an index-only scan if the index is significantly smaller than its table. This typically only happens when the table's row width is much wider than some indexes'.

See also the discussion of VACUUM and ANALYZE for maintaining the visibility map. Essentially, you probably want to make VACUUM more aggressive, and you'll want to manually VACUUM ANALYZE the table after you first load it.