From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Make COUNT(*) Faster?
Date: Thu, 07 Jul 2005 23:39:21 -0400
Organization: cbbrowne Computing Inc
Lines: 49
Message-ID: <60vf3mklue.fsf@dba2.int.libertyrms.com>
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux)
Cancel-Lock: sha1:pJfJGVBJu/BzcfKRkcfh+HrPXe4=
To: pgsql-sql@postgresql.org

vmehta@apple.com (Varun Mehta) writes:
> If I run an EXPLAIN on this query I can see that it is doing a
> sequential scan, which seems quite needless, as surely this
> information is cached in some secret location.

That would in fact surely *NOT* be the case.

If you have multiple users performing updates on that table
concurrently, with the possibility of some of those updates rolling
back, then it doesn't make sense for there to be any such "one place"
where a count would be stored.

Consider the case where you ask for COUNT(*) while the following set
of transactions are outstanding:

  1.  A transaction, which, as it turns out, will get rolled back,
      that has inserted 40 tuples;

  2.  A transaction which has modified 10 tuples, thereby generating
      10 dead tuples and adding 10 new ones;

  3.  14 transactions are outstanding, each of which have added
      2 tuples to the table.

None of those transactions have COMMITted, so there are some 78 tuples
"in limbo" spread across 16 transactions.

If there were some "single secret place" with a count, how would you
suggest it address those 78 tuples and 16 transactions that aren't yet
(and maybe never will be) part of the count?

> It is very possible that I am missing something, so I ask you: is
> there a faster way to find out how many rows are in a table?  I've
> tried doing a COUNT(column) where I have an index on column, but it
> still does a sequential scan and it is still very very slow.  What
> are my options?

Use of the index doesn't help because the index isn't forcibly up to
date.  It has no notion of marking "index tuples" as dead/not visible.
Visibility information is only attached to the tuples themselves.

Look up "MVCC" for more details...
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>