pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow "Select count(*) ..." query on table with 60 Mio. rows
11+ messages / 6 participants
[nested] [flat]
* Slow "Select count(*) ..." query on table with 60 Mio. rows
@ 2010-01-14 14:58 tom <toabctl@googlemail.com>
2010-01-14 15:11 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Matthew Wakeling <matthew@flymine.org>
2010-01-14 15:18 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 11+ messages in thread
From: tom @ 2010-01-14 14:58 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 14:58 Slow "Select count(*) ..." query on table with 60 Mio. rows tom <toabctl@googlemail.com>
@ 2010-01-14 15:11 ` Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 11+ messages in thread
From: Matthew Wakeling @ 2010-01-14 15:11 UTC (permalink / raw)
To: tom <toabctl@googlemail.com>; +Cc: pgsql-performance
On Thu, 14 Jan 2010, tom wrote:
> 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)
Sounds pretty reasonable to me. Looking at your table, the rows are maybe
200 bytes wide? That's 12GB of data for Postgres to munch through. 30
seconds is really rather quick for that (400MB/s). What sort of RAID array
is managing to give you that much?
> I use a software raid and LVM for Logical Volume Management. Filesystem
> is ext3
Ditch lvm.
This is an FAQ. Counting the rows in a table is an expensive operation in
Postgres. It can't be answered directly from an index. If you want, you
can keep track of the number of rows yourself with triggers, but beware
that this will slow down write access to the table.
Matthew
--
Nog: Look! They've made me into an ensign!
O'Brien: I didn't know things were going so badly.
Nog: Frightening, isn't it?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 14:58 Slow "Select count(*) ..." query on table with 60 Mio. rows tom <toabctl@googlemail.com>
@ 2010-01-14 15:18 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 11+ messages in thread
From: A. Kretschmer @ 2010-01-14 15:18 UTC (permalink / raw)
To: pgsql-performance
In response to tom :
> 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?
Short answer: nothing.
Long answer: PG has to check the visibility for each record, so it
forces a seq.scan.
But you can get an estimation, ask pg_class (a system table), the column
reltuples there contains an estimated row rount.
http://www.postgresql.org/docs/current/static/catalog-pg-class.html
If you really needs the correct row-count you should create a TRIGGER
and count with this trigger all INSERTs and DELETEs.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
@ 2010-01-14 15:47 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 15:59 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Ivan Voras <ivoras@freebsd.org>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 11+ messages in thread
From: Kevin Grittner @ 2010-01-14 15:47 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Matthew Wakeling <matthew@flymine.org> wrote:
> This is an FAQ.
I just added it to the wiki FAQ page:
http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-14 15:59 ` Ivan Voras <ivoras@freebsd.org>
2010-01-14 16:03 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 11+ messages in thread
From: Ivan Voras @ 2010-01-14 15:59 UTC (permalink / raw)
To: pgsql-performance
Kevin Grittner wrote:
> Matthew Wakeling <matthew@flymine.org> wrote:
>
>> This is an FAQ.
>
> I just added it to the wiki FAQ page:
>
> http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
Maybe you could add a short note why an estimation like from the
pg_class table is usually enough.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 15:59 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Ivan Voras <ivoras@freebsd.org>
@ 2010-01-14 16:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2010-01-14 16:03 UTC (permalink / raw)
To: Ivan Voras <ivoras@freebsd.org>; pgsql-performance
Ivan Voras <ivoras@freebsd.org> wrote:
> Maybe you could add a short note why an estimation like from the
> pg_class table is usually enough.
OK. Will do.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-14 18:01 ` Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:12 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 11+ messages in thread
From: Greg Smith @ 2010-01-14 18:01 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Matthew Wakeling <matthew@flymine.org> wrote:
>
>
>> This is an FAQ.
>>
>
> I just added it to the wiki FAQ page:
>
> http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
>
The content was already there, just not linked into the main FAQ yet:
http://wiki.postgresql.org/wiki/Slow_Counting
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
@ 2010-01-14 18:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:25 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2010-01-14 18:12 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith <greg@2ndquadrant.com> wrote:
> The content was already there, just not linked into the main FAQ
> yet:
> http://wiki.postgresql.org/wiki/Slow_Counting
For a question asked this frequently, it should probably be in the
FAQ. I'll add a link from there to the more thorough write-up.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:12 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-14 18:25 ` Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:33 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Smith @ 2010-01-14 18:25 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Greg Smith <greg@2ndquadrant.com> wrote:
>
>
>> The content was already there, just not linked into the main FAQ
>> yet:
>> http://wiki.postgresql.org/wiki/Slow_Counting
>>
>
> For a question asked this frequently, it should probably be in the
> FAQ. I'll add a link from there to the more thorough write-up.
>
There's a whole list of FAQs that are documented on the wiki but not in
the main FAQ yet leftover from before the main FAQ was hosted there. You
can see them all at
http://wiki.postgresql.org/wiki/Frequently_Asked_Questions
I just haven't had time to merge those all usefully into the main FAQ.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:12 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:25 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
@ 2010-01-14 18:33 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:49 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2010-01-14 18:33 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith <greg@2ndquadrant.com> wrote:
> There's a whole list of FAQs that are documented on the wiki but
> not in the main FAQ yet leftover from before the main FAQ was
> hosted there. You can see them all at
> http://wiki.postgresql.org/wiki/Frequently_Asked_Questions
>
> I just haven't had time to merge those all usefully into the main
> FAQ.
Well, unless you object to the way I did it, there's one down.
Should I remove it from the list of "Other FAQs" on the page you
cite?
(Of course, it goes without saying that you're welcome to improve
upon anything I put in there.)
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow "Select count(*) ..." query on table with 60 Mio. rows
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:12 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:25 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:33 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-14 18:49 ` Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 11+ messages in thread
From: Greg Smith @ 2010-01-14 18:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Greg Smith <greg@2ndquadrant.com> wrote:
>
>
>> There's a whole list of FAQs that are documented on the wiki but
>> not in the main FAQ yet leftover from before the main FAQ was
>> hosted there. You can see them all at
>> http://wiki.postgresql.org/wiki/Frequently_Asked_Questions
>>
>> I just haven't had time to merge those all usefully into the main
>> FAQ.
>>
>
> Well, unless you object to the way I did it, there's one down.
> Should I remove it from the list of "Other FAQs" on the page you
> cite?
>
Sure; everyone should feel free to assimilate into the main FAQ and wipe
out anything on that smaller list. Those are mainly topics where the
discussion of workarounds and approaches can be much longer than
standard FAQ length, so I suspect many of the answers are going to be a
very brief summary with a link to longer discussion. If you come across
a really small one, we might even wipe out the original page once it's
merged in.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-01-14 18:49 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-14 14:58 Slow "Select count(*) ..." query on table with 60 Mio. rows tom <toabctl@googlemail.com>
2010-01-14 15:11 ` Matthew Wakeling <matthew@flymine.org>
2010-01-14 15:18 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-01-14 15:47 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 15:59 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Ivan Voras <ivoras@freebsd.org>
2010-01-14 16:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:01 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:25 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
2010-01-14 18:33 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-14 18:49 ` Re: Slow "Select count(*) ..." query on table with 60 Mio. rows Greg Smith <greg@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox