hashagg, statistisics and excessive memory allocation

Hi!

on irc somebody complained yesterday that a simple group by on a 25M
integer row caused his backend to exhaust the 3GB process limit on his
32bit built(one a box with 16GB Ram).
Some testing showed that the planner was seriously underestimating the
number of distinct rows in the table (with the default statistic target
it estimated ~150k rows while there are about 19M distinct values) and
chosing a hashagg for the aggregate.
uping the statistics target to 1000 improves the estimate to about 5M
rows which unfortunably is still not enough to cause the planner to
switch to a groupagg with work_mem set to 256000.
Some testing seems to indicate that even with perfectly matching stats
like(8.1.3 here):

foo=# create table testtable AS select a from generate_series(1,5000000)
as a;
SELECT
foo=# CREATE INDEX test_idx on testtable (a);
CREATE INDEX
foo=# ANALYZE ;
ANALYZE
foo=# explain select a,count(*) from testtable group by a;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=97014.73..159504.51 rows=4999182 width=4)
-> Seq Scan on testtable (cost=0.00..72018.82 rows=4999182 width=4)
(2 rows)

will use about 2,5x of what work_mem is set too, while that is partly
expected it seems quite dangerous that one can even with only moderate
underestimation of the expected resultcount(say 2x or 4x) run a server
out of memory.

Stefan

Stefan,

> Some testing showed that the planner was seriously underestimating the
> number of distinct rows in the table (with the default statistic target
> it estimated ~150k rows while there are about 19M distinct values) and
> chosing a hashagg for the aggregate.
> uping the statistics target to 1000 improves the estimate to about 5M
> rows which unfortunably is still not enough to cause the planner to
> switch to a groupagg with work_mem set to 256000.

Well, it's pretty well-known that we need to fix n-distinct estimation.
But we also need to handle it gracefully if the estimate is still wrong
and we start using too much memory. Is there any way we can check how
much memory the hashagg actually *is* using and spill to disk if it goes
over work_mem?

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, May 11, 2006 at 08:36:25PM +0200, Stefan Kaltenbrunner wrote:
> Hi!
>
> on irc somebody complained yesterday that a simple group by on a 25M
> integer row caused his backend to exhaust the 3GB process limit on his
> 32bit built(one a box with 16GB Ram).
> Some testing showed that the planner was seriously underestimating the
> number of distinct rows in the table (with the default statistic target
> it estimated ~150k rows while there are about 19M distinct values) and
> chosing a hashagg for the aggregate.
> uping the statistics target to 1000 improves the estimate to about 5M
> rows which unfortunably is still not enough to cause the planner to
> switch to a groupagg with work_mem set to 256000.
> Some testing seems to indicate that even with perfectly matching stats
> like(8.1.3 here):
>
> foo=# create table testtable AS select a from generate_series(1,5000000)
> as a;
> SELECT
> foo=# CREATE INDEX test_idx on testtable (a);
> CREATE INDEX
> foo=# ANALYZE ;
> ANALYZE
> foo=# explain select a,count(*) from testtable group by a;
> QUERY PLAN
> -------------------------------------------------------------------------
> HashAggregate (cost=97014.73..159504.51 rows=4999182 width=4)
> -> Seq Scan on testtable (cost=0.00..72018.82 rows=4999182 width=4)
> (2 rows)
>
> will use about 2,5x of what work_mem is set too, while that is partly
> expected it seems quite dangerous that one can even with only moderate
> underestimation of the expected resultcount(say 2x or 4x) run a server
> out of memory.

Well, the real problem is that hash operations will happily chew through
all available memory instead of either falling back to something else or
at least going to disk. I thought that was on the TODO to be fixed, but
I don't see it there. I also thought some improvement had been made
there, but maybe it's only in HEAD...?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461