Re: PATCH: decreasing memory needlessly consumed by array_agg

Hi,

attached is v9 of the patch, modified along the lines of Tom's comments:

1) uses alen=64 for cases with private context, 8 otherwise

2) reverts removal of element_type from initArrayResultArr()

When element_type=InvalidOid is passed to initArrayResultArr, it
performs lookup using get_element_type(), otherwise reuses the value
it receives from the caller.

3) moves the assert into the 'if (release)' branch

4) includes the comments proposed by Ali Akbar in his reviews

Warnings at makeArrayResult/makeMdArrayResult about freeing memory
with private subcontexts.

Regarding the performance impact of decreasing the size of the
preallocated array from 64 to just 8 elements, I tried this.

CREATE TABLE test AS
SELECT mod(i,100000) a, i FROM generate_series(1,64*100000) s(i);

SELECT a, array_agg(i) AS x FRM test GROUP BY 1;

or actually (to minimize transfer overhead):

SELECT COUNT(x) FROM (
SELECT a, array_agg(i) AS x FRM test GROUP BY 1
) foo;

with work_mem=2GB (so that it really uses HashAggregate). The dataset is
constructed to have exactly 64 items per group, thus exploiting the
difference between alen=8 and alen=64.

With alen=8 I get these timings:

Time: 1892,681 ms
Time: 1879,046 ms
Time: 1892,626 ms
Time: 1892,155 ms
Time: 1880,282 ms
Time: 1868,344 ms
Time: 1873,294 ms

and with alen=64:

Time: 1888,244 ms
Time: 1882,991 ms
Time: 1885,157 ms
Time: 1868,935 ms
Time: 1878,053 ms
Time: 1894,871 ms
Time: 1871,571 ms

That's 1880 vs 1882 on average, so pretty much no difference. Would be
nice if someone else could try this on their machine(s).

regards

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services