On Fri, Mar 20, 2020 at 07:57:02PM +0800, Pengzhou Tang wrote:
>Hi Tomas,
>
>I rebased the code and resolved the comments you attached, some unresolved
>comments are explained in 0002-fixes.patch, please take a look.
>
>I also make the hash spill working for parallel grouping sets, the plan
>looks like:
>
>gpadmin=# explain select g100, g10, sum(g::numeric), count(*), max(g::text)
>from gstest_p group by cube (g100,g10);
> QUERY PLAN
>-------------------------------------------------------------------------------------------
> Finalize MixedAggregate (cost=1000.00..7639.95 rows=1111 width=80)
> Filtered by: (GROUPINGSETID())
> Group Key: ()
> Hash Key: g100, g10
> Hash Key: g100
> Hash Key: g10
> Planned Partitions: 4
> -> Gather (cost=1000.00..6554.34 rows=7777 width=84)
> Workers Planned: 7
> -> Partial MixedAggregate (cost=0.00..4776.64 rows=1111 width=84)
> Group Key: ()
> Hash Key: g100, g10
> Hash Key: g100
> Hash Key: g10
> Planned Partitions: 4
> -> Parallel Seq Scan on gstest_p (cost=0.00..1367.71
>rows=28571 width=12)
>(16 rows)
>
Hmmm, OK. I think there's some sort of memory leak, though. I've tried
running a simple grouping set query on catalog_sales table from TPC-DS
scale 100GB test. The query is pretty simple:
select count(*) from catalog_sales
group by cube (cs_warehouse_sk, cs_ship_mode_sk, cs_call_center_sk);
with a partial MixedAggregate plan (attached). When executed, it however
allocates more and more memory, and eventually gets killed by an OOM
killer. This is on a machine with 8GB of RAM, work_mem=4MB (and 4
parallel workers).
The memory context stats from a running process before it gets killed by
OOM look like this
TopMemoryContext: 101560 total in 6 blocks; 7336 free (6 chunks); 94224 used
TopTransactionContext: 73816 total in 4 blocks; 11624 free (0 chunks); 62192 used
ExecutorState: 1375731712 total in 174 blocks; 5391392 free (382 chunks); 1370340320 used
HashAgg meta context: 315784 total in 10 blocks; 15400 free (2 chunks); 300384 used
ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
...
That's 1.3GB allocated in ExecutorState - that doesn't seem right.
FWIW there are only very few groups (each attribute has fewer than 30
distinct values, so there's only about ~1000 groups. On master it works
just fine, of course.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services