Re: Show hash / bitmap sizes in EXPLAIN ANALYZE?

On 10/01/2016 01:37 AM, Andres Freund wrote:
> Hi,
>
> At the moment in-memory sort and hash nodes show their memory usage in
> explain:
> │   ->  Sort  (cost=59.83..62.33 rows=1000 width=4) (actual time=0.512..0.632 rows=1000 loops=1)
             │
 
> │         Sort Key: a.a
             │
 
> │         Sort Method: quicksort  Memory: 71kB
             │
 
> │         ->  Function Scan on generate_series a  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.165..0.305
rows=1000loops=1) │
 
> and
> │   ->  Hash  (cost=10.00..10.00 rows=1000 width=4) (actual time=0.581..0.581 rows=1000 loops=1)
             │
 
> │         Buckets: 1024  Batches: 1  Memory Usage: 44kB
             │
 
>
> I think we should show something similar for bitmap scans, and for
> some execGrouping.c users (at least hash aggregates, subplans and
> setop seem good candidates too).
>

+1 to improve this

> For both categories it's useful to see how close within work_mem a
> scan ended up being (to understand how high to set it, and how much
> the data can grow till work_mem is excceded), and for execGrouping.c
> users it's also very interesting to see the actual memory usage
> because the limit is only a very soft one.
>
> Does anybody see a reason not to add that?
>

Well, the obvious problem with execGrouping.c is that we don't have 
information about memory usage - we don't know how large the aggregate 
state is. It's trivial to compute it for aggregates that use 
fixed-length data types, but for aggregates that use varlena/internal 
state that's not going to work.

This is actually the same problem Jeff Davis ran into when trying to 
implement memory-bounded HashAgg ~2 years ago, which also needs this 
information. Back then there was a lot of discussion about whether the 
~1% penalty measured is acceptable price for the accounting, which kinda 
killed the whole patch.

I plan to revisit that hashagg patch, or rather a new patch with the 
same goal - now that we have serial/deserial functions for aggregates, 
we should be able to implement much nicer spill-to-disk method. But 
that'll need the memory accounting, so if you want to look into it, 
you're welcome.

regards

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