From: mark@mark.mielke.cc
To: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 10:52:27 -0400
Message-ID: <20070322145226.GA15670@mark.mielke.cc> (raw)
In-Reply-To: <20070322141807.GE11402@mathom.us>
References: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
<200703221208.19358.albert@sedifa.com>
<Pine.LNX.4.64.0703221323590.1503@ismoli.solenovo.jns>
<20070322141807.GE11402@mathom.us>
On Thu, Mar 22, 2007 at 10:18:10AM -0400, Michael Stone wrote:
> IIRC, that's basically what you get with the mysql count anyway, since
> there are corner cases for results in a transaction. Avoiding those
> cases is why the postgres count takes so long; sometimes that's what's
> desired and sometimes it is not.
Adding to this point:
In any production system, the count presented to the user is usually
wrong very shortly after it is displayed anyways. Transactions in the
background or from other users are adding or removing items, perhaps
even before the count reaches the user's display.
The idea of transaction-safety for counts doesn't apply in this case.
Both the transaction and the number are complete before the value is
displayed.
In my own systems, I rarely use count(*) for anything except user
visible results. For the PostgreSQL system I use, I keep a table of
counts, and lock the row for update when adding or removing items.
This turns out to be best in this system anyways, as I need my new
rows to be ordered, and locking the 'count' row lets me assign a
new sequence number for the row. (Don't want to use SEQUENCE objects,
as there could as the rows are [key, sequence, data], with thousands
or more keys)
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
http://mark.mielke.cc/
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: mark@mark.mielke.cc
Subject: Re: Performance of count(*)
In-Reply-To: <20070322145226.GA15670@mark.mielke.cc>
* 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