From: Brian Hurt <bhurt@janestcapital.com>
To: runic <runic@gmx.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: select count(*) performance
Date: Fri, 10 Aug 2007 09:08:18 -0400
Message-ID: <46BC6342.4010002@janestcapital.com> (raw)
In-Reply-To: <1186578085.034463.116030@q75g2000hsh.googlegroups.com>
References: <1186578085.034463.116030@q75g2000hsh.googlegroups.com>
runic wrote:
>Hello Group,
>
>I'm new in PostgreSQL Business, therefore please forgive me a "newbie"
>Question. I have a table with ca. 1.250.000 Records. When I execute
>a "select count (*) from table" (with pgAdmin III) it takes about 40
>secs.
>I think that takes much to long. Can you please give me hints, where
>I can search for Improvements?
>
>TIA, Det
>
>
1) VACUUM FULL the table, maybe the whole database.
2) Buy more/faster hard disks
The problem is that count(*) on a table has to scan the whole table, due
to the fact that Postgres uses MVCC for it's concurrency control. This
is normally a huge win- but one of the few places where it's a loss is
doing count(*) over a whole table. In this case, Postgres has no choice
but to inspect each and every row to see if it's live or not, and thus
has no choice but to read in the whole table.
If you've been doing a lot of inserts, updates, and/or deletes to the
table, and you either don't have autovacuum turned on or agressive
enough, the table can be littered with a bunch of dead rows that haven't
been deleted yet. Postgres still has to read in those rows to make sure
they're dead, so it's easy for it to have to read many multiples of the
number of live rows in the table. What vacuum does is it goes through
and deletes those dead rows.
If that isn't the problem, then it's just that you have to read the
whole table. If the rows are large enough, and the disk subsystem is
slow enough, this can just take a while. My advice in this case to buy
either more disks and/or faster disks, to speed up the reading of the table.
Brian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bhurt@janestcapital.com, runic@gmx.de
Subject: Re: select count(*) performance
In-Reply-To: <46BC6342.4010002@janestcapital.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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