471,354 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

constant time count(*) ?

We're looking into moving some data from mysql to postgresql, and
notice that count(*) does not seem to be a constant-time function
as it seems to be in mysql.

planb=# explain select count(*) from assets;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=22.50..22.50 rows=1 width=0)
-> Seq Scan on assets (cost=0.00..20.00 rows=1000 width=0)
(2 rows)

Is there a way to optimize count(*) such that it does not have
to do a sequential scan? We use this on some big tables and it
is slowing down processing quite a lot.

Thanks!
Mark

--
Mark Harrison
Pixar Animation Studios
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
3 1956
Mark Harrison writes:
Is there a way to optimize count(*) such that it does not have
to do a sequential scan?


No. If you need to count a lot, you need to store the information
separately.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2
On Wed, Oct 15, 2003 at 11:00:10AM -0700, Mark Harrison wrote:

Is there a way to optimize count(*) such that it does not have
to do a sequential scan? We use this on some big tables and it
is slowing down processing quite a lot.


No. There's a busload of discussion on this topic in the archives.
If you need an approximate value, you can get it from the system
tables.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<an****@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3
Hi Mark,

Mark Harrison wrote:
We're looking into moving some data from mysql to postgresql, and
notice that count(*) does not seem to be a constant-time function
as it seems to be in mysql.

planb=# explain select count(*) from assets;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=22.50..22.50 rows=1 width=0)
-> Seq Scan on assets (cost=0.00..20.00 rows=1000 width=0)
(2 rows)

Is there a way to optimize count(*) such that it does not have
to do a sequential scan? We use this on some big tables and it
is slowing down processing quite a lot.


How do you need an unqualified
select count(*) on a table so often
it is making a problem?

Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by devdatta_clc | last post: by
22 posts views Thread by Laurent Deniau | last post: by
6 posts views Thread by Amit Bhatia | last post: by
15 posts views Thread by skibud2 | last post: by
22 posts views Thread by Tomás Ó hÉilidhe | last post: by
3 posts views Thread by Dan Smithers | last post: by
1 post views Thread by hackerbob | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.