473,686 Members | 2,952 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #1
3 2047
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 YourEmailAddres sHere" to ma*******@postg resql.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****@liberty rms.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
2565
by: devdatta_clc | last post by:
Hi C experts I've a bunch of questions. Consider this simplified piece of code. const int a = 10; int main () { static int b = a;
22
5974
by: Laurent Deniau | last post by:
Is there any way to create a constant of type double _Complex without including <complex.h>? Why _Complex_I is a macro an not an implementation-defined constant? Thanks. a+, ld.
6
4006
by: Amit Bhatia | last post by:
Hi, I am not sure if this belongs to this group. Anyway, my question is as follows: I have a list (STL list) whose elements are pairs of integers (STL pairs, say objects of class T). When I create a new object of class T, I would like to check if this object already exists in the list: meaning one having same integers. This can be done in linear time in a list, and probably faster if I use STL Set instead of list. I am wondering however if...
15
2902
by: skibud2 | last post by:
Consider this example: u8 my_array; for (i = 0; i < (sizeof(my_array)/sizeof(u8)); i++) { ... } In the standard C specification, is the evaluation of '(sizeof(my_array)/sizeof(u8))' to 10 guarenteed to happen at compile
33
5548
by: desktop | last post by:
In the C++ standard sec 23.1.2 table 69 it says that erase(q) where q is a pointer to an element can be done in amortized constant time. I guess that is not worst case since std::set is practically a red-black tree where insert/delete takes O(lg n) time. Or are there some other explanation for this complexity?
22
3603
by: Tomás Ó hÉilidhe | last post by:
I've been developing a C89 microcontroller application for a while now and I've been testing its compilation using gcc. I've gotten zero errors and zero warnings with gcc, but now that I've moved over to the micrcontroller compiler I'm getting all sorts of errors. One thing I'd like to clarify is the need (in C89) for a compile- time constant in the initialiser of a variable. The compiler rejects the following source file: /* Start...
3
4759
by: Dan Smithers | last post by:
What constitutes a constant-expression? I know that it is something that can be determined at compile time. I am trying to use template code and keep getting compiler errors "error: cannot appear in a constant-expression" template <int s> class CFoo { private:
13
21828
by: sinbad | last post by:
hi, how to concatenate a "hash defined" constant value to another "hash defined" constant string. For example #define ABC 100 #define MYSTR "The value of ABC is" Now i need a string that will concatenate the value of ABC to MYSTR . I need this at compile time.
1
2721
by: hackerbob | last post by:
I'm trying to create a constant time event timer. Basically, a routine can set a callback to be called n ms from the current time, and the main event loop will wait until the delta between the current time and the earliest event timer has elapsed. When the list is sorted, checking for expiration is O(n) time where n is the number of timers that have expired, or O(1) in the case where no timers have expired (which is important in an I/O...
0
8523
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8781
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7613
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6444
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4317
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4538
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2959
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1943
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.