Steve Kehlet <steve.kehlet@xxxxxxxxx> writes: > But for now, the devs are asking me for some way to put a cap on a postgres > query's total memory consumption. I'm familiar with the available settings > on the "Resource Consumption" docs (and you can see my settings in my gist > above, including work_mem turned way down to 1MB for testing), but it seems > like there are things like Materialize that remain uncappable, since > they're not constrained by the shared_buffers and work_mem limits. Materialize nodes should honor the work_mem limit. That's per node of course, but you only have half a dozen of them in this query, so I doubt that's where the problem is. You mentioned array_agg ... how are you using that exactly, and how large a result are you expecting it to create? If you had an array_agg being used in a HashAggregate plan node, I'd figure for sure that was the source of your problem, but the explain output shows it's a GroupAggregate so there should only be one array_agg active at a time. Anyway, what I would try doing is starting the postmaster with a "ulimit -d" value corresponding to the max per-process data segment size you want. Something in the range of 50-100MB would probably be reasonable if your queries aren't too complex. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
