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=1000 loops=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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
