Slow "Select count(*) ..." query on table with 60 Mio. rows - Mailing list pgsql-performance

Hi,

=== Problem ===

i have a db-table "data_measurand" with about 60000000 (60 Millions)
rows and the following query takes about 20-30 seconds (with psql):

mydb=# select count(*) from data_measurand;
  count
----------
 60846187
(1 row)


=== Question ===

- What can i do to improve the performance for the data_measurand table?

=== Background ===

I created a application with django 1.1 ( http://djangoproject.com ) to
collect, analyze and visualize measurement data.

=== My System ===

= Postgres Version =
postgres=# select version();
version
---------------------------------------------------------------------
 PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2
(1 row)

I installed postgres with apt-get from debian lenny without any
modifications.

= Debian Lenny Kernel Version =
lenny:~# uname  -a
Linux or.ammonit.com 2.6.26-2-686-bigmem #1 SMP Wed Nov 4 21:12:12 UTC
2009 i686 GNU/Linux

= Hardware =
model name    : AMD Athlon(tm) 64 X2 Dual Core Processor 6000+
cpu MHz        : 1000.000
cache size    : 512 KB
MemTotal    : 8281516 kB (8 GB)

I use a software raid and LVM for Logical Volume Management. Filesystem
is ext3



=== My Table Definitions ===

mydb=# \d data_measurand;
                                     Table "public.data_measurand"
     Column      |          Type          |
Modifiers
-----------------+------------------------+-------------------------------------------------------------
 id              | integer                | not null default
nextval('data_measurand_id_seq'::regclass)
 entry_id        | integer                | not null
 sensor_id       | integer                | not null
 avg_value       | numeric(10,4)          |
 avg_count_value | integer                |
 min_value       | numeric(10,4)          |
 max_value       | numeric(10,4)          |
 sigma_value     | numeric(10,4)          |
 unit            | character varying(20)  | not null
 status          | integer                | not null
 comment         | character varying(255) | not null
Indexes:
    "data_measurand_pkey" PRIMARY KEY, btree (id)
    "data_measurand_entry_id_68e2e3fe" UNIQUE, btree (entry_id,
sensor_id)
    "data_measurand_avg_count_value" btree (avg_count_value)
    "data_measurand_avg_value" btree (avg_value)
    "data_measurand_comment" btree (comment)
    "data_measurand_entry_id" btree (entry_id)
    "data_measurand_max_value" btree (max_value)
    "data_measurand_min_value" btree (min_value)
    "data_measurand_sensor_id" btree (sensor_id)
    "data_measurand_sigma_value" btree (sigma_value)
    "data_measurand_status" btree (status)
    "data_measurand_unit" btree (unit)
Foreign-key constraints:
    "entry_id_refs_id_50fa9bdf" FOREIGN KEY (entry_id) REFERENCES
data_entry(id) DEFERRABLE INITIALLY DEFERRED
    "sensor_id_refs_id_5ed84c7c" FOREIGN KEY (sensor_id) REFERENCES
sensor_sensor(id) DEFERRABLE INITIALLY DEFERRED



mydb=# \d data_entry;
                                       Table "public.data_entry"
      Column      |           Type           |
Modifiers
------------------+--------------------------+---------------------------------------------------------
 id               | integer                  | not null default
nextval('data_entry_id_seq'::regclass)
 project_id       | integer                  | not null
 logger_id        | integer                  | not null
 original_file_id | integer                  | not null
 datetime         | timestamp with time zone | not null
Indexes:
    "data_entry_pkey" PRIMARY KEY, btree (id)
    "data_entry_logger_id_197f5d41" UNIQUE, btree (logger_id, datetime)
    "data_entry_datetime" btree (datetime)
    "data_entry_logger_id" btree (logger_id)
    "data_entry_original_file_id" btree (original_file_id)
    "data_entry_project_id" btree (project_id)
Foreign-key constraints:
    "logger_id_refs_id_5f73cf46" FOREIGN KEY (logger_id) REFERENCES
logger_logger(id) DEFERRABLE INITIALLY DEFERRED
    "original_file_id_refs_id_44e8d3b1" FOREIGN KEY (original_file_id)
REFERENCES data_originalfile(id) DEFERRABLE INITIALLY DEFERRED
    "project_id_refs_id_719fb302" FOREIGN KEY (project_id) REFERENCES
project_project(id) DEFERRABLE INITIALLY DEFERRED



mydb=# \d project_project;
                                    Table "public.project_project"
    Column     |          Type          |
Modifiers
---------------+------------------------+--------------------------------------------------------------
 id            | integer                | not null default
nextval('project_project_id_seq'::regclass)
 auth_group_id | integer                | not null
 name          | character varying(200) | not null
 timezone      | character varying(200) |
 longitude     | double precision       |
 latitude      | double precision       |
 altitude      | double precision       |
 comment       | text                   |
Indexes:
    "project_project_pkey" PRIMARY KEY, btree (id)
    "project_project_auth_group_id" btree (auth_group_id)
Foreign-key constraints:
    "auth_group_id_refs_id_267c7fe5" FOREIGN KEY (auth_group_id)
REFERENCES auth_group(id) DEFERRABLE INITIALLY DEFERRED



mydb=# \d logger_logger;
                                         Table "public.logger_logger"
        Column         |           Type           |
Modifiers
-----------------------+--------------------------+------------------------------------------------------------
 id                    | integer                  | not null default
nextval('logger_logger_id_seq'::regclass)
 auth_group_id         | integer                  | not null
 project_id            | integer                  |
 serial                | character varying(50)    | not null
 type                  | character varying(30)    | not null
 comment               | text                     |
 last_email            | timestamp with time zone |
 last_checked_datetime | timestamp with time zone |
Indexes:
    "logger_logger_pkey" PRIMARY KEY, btree (id)
    "logger_logger_serial_key" UNIQUE, btree (serial)
    "logger_logger_auth_group_id" btree (auth_group_id)
    "logger_logger_last_checked_datetime" btree (last_checked_datetime)
    "logger_logger_project_id" btree (project_id)
Foreign-key constraints:
    "auth_group_id_refs_id_355ed859" FOREIGN KEY (auth_group_id)
REFERENCES auth_group(id) DEFERRABLE INITIALLY DEFERRED
    "project_id_refs_id_5f4a56f3" FOREIGN KEY (project_id) REFERENCES
project_project(id) DEFERRABLE INITIALLY DEFERRED




I hope that's enough information.

Cheers Tom