Re: installcheck failing on psql_crosstab

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Tom Lane wrote:
>> Presumably what is happening is that the planner is switching from hash
>> to sort aggregation.

> I can't imagine that the server is avoiding hash aggregation on a 1MB
> work_mem limit for data that's a few dozen of bytes. Is it really doing
> that?

Yup:

regression=# explain SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
GROUP BY v, h ORDER BY h,v;
QUERY PLAN
------------------------------------------------------------------------
Sort (cost=33.87..34.37 rows=200 width=96)
Sort Key: h, v
-> HashAggregate (cost=23.73..26.23 rows=200 width=96)
Group Key: h, v
-> Seq Scan on ctv_data (cost=0.00..16.10 rows=610 width=68)
(5 rows)

regression=# set work_mem = '1MB';
SET
regression=# explain SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
GROUP BY v, h ORDER BY h,v;
QUERY PLAN
------------------------------------------------------------------------
GroupAggregate (cost=44.32..55.97 rows=200 width=96)
Group Key: h, v
-> Sort (cost=44.32..45.85 rows=610 width=68)
Sort Key: h, v
-> Seq Scan on ctv_data (cost=0.00..16.10 rows=610 width=68)
(5 rows)

Now that you mention it, this does seem a bit odd, although I remember
that there's a pretty substantial fudge factor in there when we have
no statistics (which we don't in this example). If I ANALYZE ctv_data
then it sticks to the hashagg plan all the way down to 64kB work_mem.

regards, tom lane