Postgres row count

Question

I have a table with a row count of 985 records. But it is really slow to query this table.

# select count(*) from movie;
 count 
-------
   985

Then I perform query using EXPLAIN ANALYZE The row count over here is 1636712, which is very much different from 985. Why is this happen and how to fix this?

EXPLAIN ANALYZE SELECT count(*) FROM movie;
QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=194963.62..194963.63 rows=1 width=0) (actual time=21051.607..21051.607 rows=1 loops=1)
   ->  Seq Scan on movie  (cost=0.00..190852.29 rows=1644529 width=0) (actual time=2813.737..21051.094 rows=985 loops=1)
 Total runtime: 21053.506 ms

\d movie

Indexes:
    "movie_pkey" PRIMARY KEY, btree (movie_id)
    "index_movie_internal_id" btree (movie_internal_id)
    "movie_release_date_index" btree (movie_release_date DESC NULLS LAST, movie_internal_title)
    "movie_release_date_title_index" btree (movie_release_date, movie_internal_title)


# VACUUM FULL ANALYZE movie; EXPLAIN ANALYZE SELECT count(*) FROM movie;
    VACUUM
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=194963.62..194963.63 rows=1 width=0) (actual time=21051.607..21051.607 rows=1 loops=1)
   ->  Seq Scan on movie  (cost=0.00..190852.29 rows=1644529 width=0) (actual time=2813.737..21051.094 rows=985 loops=1)
 Total runtime: 21053.506 ms

Answer 1

PostgreSQL 8.4 is very old. It was EOL in 2014. 9.2 came out in 2012 and it solves all these problems. You need to upgrade to a modern version of PostgreSQL that supports index-only scans.