Why Index Only Scan is taking so long?

Question

Why does executing my simple query

select count(this_.Id) as y0_ from Activity this_

take so long (more than 10 minutes this time)?

Here is the query plan (output of the EXPLAIN ANALYZE):

QUERY PLAN  
 Aggregate (cost=854047.36..854047.37 rows=1 width=4)
> (actual time=728525.277..728525.277 rows=1 loops=1)   
->  Index Only
> Scan using activity_pkey on activity this_  (cost=0.56..805401.87
> rows=19458196 width=4) (actual time=36.961..725381.557 rows=19517989
> loops=1)  
> Heap Fetches: 10351403  
Total runtime: 728533.529 ms

And PostgreSql version:

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

The index is here too, on the Id field:

ALTER TABLE public.activity
ADD CONSTRAINT activity_pkey 
PRIMARY KEY (id);

Answer 1

Index Only Scan in PostgreSQL has to look sometimes into the table (heap), because index pages don't contain information about tuple visibility. That's how many rows were fetched from the index:

(actual ... rows=19517989

And that's how many rows were re-checked in the heap:

Heap Fetches: 10351403

To speed it up, you should run vacuum on you table: vacuum Activity

Vacuum will update visibility map, and after that Index Only Scan will be able to perform using (almost) only index pages.