Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group