Re: Performance Issues with count()

On Tue, Apr 23, 2002 at 07:25:34PM -0400, Jan Wieck wrote:
> In particular, the Perl script uses a hash table with one
> entry for each group. Now what happens if the input data is
> spread out and contains 1G groups? It'll simply blow away
> your script because it runs out of memory. This behaviour is
> unacceptable for a database system, so as you see in the
> Explain output, PostgreSQL sorts and groups the input data in
> temporary files before counting the rows. Due to that, the
> PostgreSQL solution to the problem requires a gazillion of IO
> operations, but it'll work whatever the input data is, given
> that there is enough disk space.

I've always had a little problem with this setup. Sure, if you're using 1
billion groups then you have a problem, but if you are going into only a
small number of groups, the sorting takes forever compared to using a hash.
Currently the planner beleives that the output of a group will be 1/10th of
the input, but I think the square root would be a better estimate.

Here we regularly summerise 2 million rows into 6 groups and it's almost
faster to do 6 sequential scans than it is to do the sort/group.

Besides, the running out of memory argument is silly, because if sorting
moves out to disk when it gets too big, you can do the same with a hash
table.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America