> And it can't be adjusted on the fly, per query.

Sure it can:

   SET LOCAL work_mem = '256MB';
   SELECT * FROM …