You will want to try decreasing work_mem to a sane number first, without
looking at anything else really.
Check out the official docs:
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM
The gist is that work_mem is not a limit that's effective per
session/connection/query, but per sort- or hash-node, of which there can be
many in complex queries. Which is why 1GB of work_mem can end up consuming
several multiples of that, if you are (un)lucky enough.
--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )
www: https://johannes.truschnigg.info/
phone: +436502133337
xmpp: johannes@truschnigg.info
