|
Home > Archive > PostgreSQL JDBC > September 2005 > SELECT COUNT(*) does a scan?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SELECT COUNT(*) does a scan?
|
|
| David Wall 2005-09-08, 11:25 am |
| 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
| |
| Oliver Jowett 2005-09-08, 11:25 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's not the case, and this is a FAQ -- search archives.postgresql.org
for more details (the short version is that maintaining a row count
doesn't work well with MVCC).
> 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"?
Most likely a VACUUM ANALYZE is just pulling the whole table into cache,
so there is less disk I/O needed to do the scan.
-O
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Dave Cramer 2005-09-08, 1:25 pm |
| 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
>
>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
|
|
|
|