Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EC76563EAC0 for ; Thu, 14 Jan 2010 10:59:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18210-07-4 for ; Thu, 14 Jan 2010 14:59:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f227.google.com (mail-fx0-f227.google.com [209.85.220.227]) by mail.postgresql.org (Postfix) with ESMTP id BE6C163EF6B for ; Thu, 14 Jan 2010 10:58:50 -0400 (AST) Received: by fxm27 with SMTP id 27so907298fxm.3 for ; Thu, 14 Jan 2010 06:58:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:subject:from:to:content-type :date:message-id:mime-version:x-mailer:content-transfer-encoding; bh=P6Ke7XM++XZWKYPHX/ouJjxLtpzwOEsaY9jVMuCVLJs=; b=hSA93JIbSgVoMZ7bXA42tS1pu35T1iutjeYrd28K0rBThmcoBNLIB2JQL+R3Mo1EW/ ammD4L0tHtobh/lY74aJarbfPQKiPxGU3OFUIHgGjaYCsAgHKtAR1b+8Z0sMPFaI7pge gRpNsxQb/HcyQ4ow//xQNviTTSaInpwdi7Cug= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=subject:from:to:content-type:date:message-id:mime-version:x-mailer :content-transfer-encoding; b=dVC2H3H+vWxMkGWdbOOBtF6VQEdxTYAAia29HDxzBVxSKk6sahz2r69HCjSKpVxh8h UCENdDW1pJsDzqnl0ke7/2+kDBotHuZqw8RMXEIsgR4GAwqn9dTizbzI2dKiXr93m9SF 0HP3n7Tu1XDPloOwuuzSmFnwy9sVl3AcIJf+Q= Received: by 10.223.76.69 with SMTP id b5mr1160507fak.20.1263481128172; Thu, 14 Jan 2010 06:58:48 -0800 (PST) Received: from ?192.168.0.6? (g229060083.adsl.alicedsl.de [92.229.60.83]) by mx.google.com with ESMTPS id g28sm1001068fkg.38.2010.01.14.06.58.47 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 14 Jan 2010 06:58:47 -0800 (PST) Subject: Slow "Select count(*) ..." query on table with 60 Mio. rows From: tom To: pgsql-performance@postgresql.org Content-Type: text/plain; charset="UTF-8" Date: Thu, 14 Jan 2010 15:58:44 +0100 Message-ID: <1263481124.3307.59.camel@zitrone> Mime-Version: 1.0 X-Mailer: Evolution 2.28.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.589 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/275 X-Sequence-Number: 37155 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