Skip site navigation (1) Skip section navigation (2)

Re: SELECT COUNT(*) does a scan?

From: Dave Cramer <davec(at)fastcrypt(dot)com>
To: David Wall <d(dot)wall(at)computer(dot)org>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: SELECT COUNT(*) does a scan?
Date: 2005-09-08 18:15:21
Message-ID: 82B89335-B770-4326-B524-5B9B5461CFEE@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
See Oliver's post:

Additionally you can get count to use an index, but you need a where  
clause.

Dave
On 8-Sep-05, at 11:22 AM, David Wall wrote:

> When I do an EXPLAIN SELECT COUNT(*) FROM tablename, I noted that  
> it does a table scan.  I thought PG had some sort of table stat  
> that kept track of the current number of rows in a table, but that  
> doesn't appear to always be the case.
>
> It seems that right after a VACUUM ANALYZE, that command is very  
> fast (on a table with 100,000+ rows), but it can also get quite  
> slow, as if a table scan is taking place.
> Does this make sense?  Is there an algorithm that says to use the  
> stats from analyze only until sufficient updates/inserts/deletes  
> have taken place to make them "out of date"?
>
> David
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>


In response to

pgsql-jdbc by date

Next:From: Kevin GrittnerDate: 2005-09-08 22:11:52
Subject: Re: simple insert operation
Previous:From: Oliver JowettDate: 2005-09-08 15:40:46
Subject: Re: SELECT COUNT(*) does a scan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group