Re: Performance of count(*)

From: mark@mark.mielke.cc
Subject: Re: Performance of count(*)
Date: ,
Msg-id: 20070322145226.GA15670@mark.mielke.cc
(view: Whole thread, Raw)
In response to: Re: Performance of count(*)  (Michael Stone)
List: pgsql-performance

Tree view

Performance of count(*)  (Andreas Tille, )
 Re: Performance of count(*)  (Andreas Kostyrka, )
  Re: Performance of count(*)  (Andreas Tille, )
   Re: Performance of count(*)  (Andreas Kostyrka, )
    Re: Performance of count(*)  (Michael Fuhr, )
    Re: Performance of count(*)  (Carlos Moreno, )
   Re: Performance of count(*)  ("Luke Lonergan", )
 Re: Performance of count(*)  (Albert Cervera Areny, )
  Re: Performance of count(*)  (, )
   Re: Performance of count(*)  (Bill Moran, )
   Re: Performance of count(*)  (Mario Weilguni, )
   Re: Performance of count(*)  (Michael Stone, )
    Re: Performance of count(*)  (, )
    Re: Performance of count(*)  ("Craig A. James", )
     Re: Performance of count(*)  (Tino Wildenhain, )
      Re: Performance of count(*)  ("Craig A. James", )
       Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  ("Merlin Moncure", )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
           Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tino Wildenhain, )
       Re: Performance of count(*)  (Steve Atkins, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tom Lane, )
          Re: Performance of count(*)  ("Craig A. James", )
           Re: Performance of count(*)  (Tom Lane, )
        Re: Performance of count(*)  (Guido Neitzer, )
         Re: Performance of count(*)  (Steve Atkins, )
     Re: Performance of count(*)  (Brian Hurt, )
      Re: Performance of count(*)  ("Craig A. James", )
 Re: Performance of count(*)  (, )
 Re: Performance of count(*)  ("Merlin Moncure", )
  Re: Performance of count(*)  ("Jonah H. Harris", )
   Re: Performance of count(*)  (Mario Weilguni, )
    Re: Performance of count(*)  (Andreas Kostyrka, )
     Re: Performance of count(*)  (Mario Weilguni, )
  Re: Performance of count(*)  (Michael Stone, )

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

--
 /  /      __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | 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/



pgsql-performance by date:

From: Tino Wildenhain
Date:
Subject: Re: Performance of count(*)
From: Michael Stone
Date:
Subject: Re: Performance of count(*)