Re: Performance Issues with count()

asdf asdasfa wrote:
[text/html is unsupported, treating like TEXT/PLAIN]

> <DIV><FONT face=Arial size=2>
> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face="Times New Roman" size=3>Hi,</FONT></P>

First a little hint: mime mail with plain text and HTML is
usually already unwanted on mailing lists, because it
increases the bandwidth for absolutely no benefit at all.
Plus, alot of people don't even read HTML mail, so HTML-only
mail like yours isn't the smartest way to get access to
developers knowledge here.

Anyway, the problem is that your Perl script is optimized for
your purpose, while PostgreSQL has to use generic algorithms
that work in ANY situation to solve the problem.

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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #