Re: why does count take so long?

On Mon, Sep 08, 2003 at 05:47:48PM -0400, Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
> > Things like count(*) could use int4 until it overflows though.
>
> I don't see a reasonable way for an aggregate to change state datatype
> on the fly; otherwise this would be a great solution.

What about estimating which one you'll need based on statistics? Yeah,
might fail some times, but see my next comment...

> > Is int4 a pass-by-value datatype on 32-bit machines?
>
> Yeah.  Not too long ago, count() used int4, but we got some complaints
> about it overflowing on big tables.

MS SQL (and I believe some other databases) provide countbig() which is
int8 for this very reason. Many times you *know* you won't have more
than int4 rows (ie, if you're using an int4 as a PK), so there's no
reason to spend the overhead associated with an int8.

Maybe have count4(), count8(), and have count() guess as to which one it
should use based on statistics?
--
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"