Re: Performance Issues with count()

On Thu, 25 Apr 2002, S Grannis wrote:

> Others have suggested the "fix" is in the future.
>
> Stephan Szabo wrote:
> "I didn't see anything about your settings in postgresql.conf,
> but increasing the sort_mem parameter may help that really
> expensive sort step. I think the desired fix for this would
> probably be the TODO entry on hash based aggregates but that's
> still in the future..."
>
> The "non-default" postgresql.conf settings are as follows:
>
> shared_buffers = 240000 # uses ~2GB of shared mem
> sort_mem = 512

It might be interesting to try with sort_mem set up an order
of magnitude to see if that helps at all.  I doubt it'll be
enough, but it's probably worth trying.

> I think our work-around for now will be to SELECT the column we wish
> to analyze into a flat file and then run a Perl script to do the
> actual counting.

Another option would be a C function that does runs a query via
SPI and does the hashing count and sticks it in a temporary table
but that may be a bit excessive.