Slow "Select count(*) ..." query on table with 60 Mio. rows

From: tom <toabctl(at)googlemail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow "Select count(*) ..." query on table with 60 Mio. rows
Date: 2010-01-14 14:58:44
Message-ID: 1263481124.3307.59.camel@zitrone
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Wakeling 2010-01-14 15:04:02 Re: Inserting 8MB bytea: just 25% of disk perf used?
Previous Message Andy Colson 2010-01-14 14:49:36 Re: a heavy duty operation on an "unused" table kills my server