I happened to be looking in the PostgreSQL logs (8.4.10, x86_64,
ScientificLinux 6.1) and noticed that an app was doing some sorting
(group by, order by, index creation) that ended up on disk rather than
staying in memory.
So I enabled trace_sort and restarted the app.
What followed confused me.
I know that the app is setting the work_mem and maintenance_work_mem
to 1GB, at the start of the session, with the following calls:
select set_config(work_mem, 1GB, False);
select set_config(maintenance_work_mem, 1GB, False);
By timestamps, I know that these statements take place before the next
log items, generated by PostgreSQL (note: I also log the PID of the
backend and all of these are from the same PID):
LOG: 00000: begin tuple sort: nkeys = 2, workMem = 1048576, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 2, workMem = 1048576, randomAccess = f
^ these make sense
LOG: 00000: begin tuple sort: nkeys = 2, workMem = 131072, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 131072, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 131072, randomAccess = f
....
^^ these do not (but 128MB is the globally-configured work_mem value)
LOG: 00000: begin index sort: unique = t, workMem = 2097152, randomAccess = f
^ this kinda does (2GB is the globally-configured maintenance_work_mem value)
LOG: 00000: begin index sort: unique = f, workMem = 131072, randomAccess = f
LOG: 00000: begin tuple sort: nkeys = 2, workMem = 131072, randomAccess = f
..
The config shows 128MB for work_mem and 2GB for maintenance_work_mem.
Why does PostgreSQL /sometimes/ use the globally-configured values and
sometimes use the values that come from the connection?
Am I wrong in misunderstanding what 'session' variables are? I thought
that session (versus transaction) config items were set for /all/
transactions in a given backend, until changed or until that backend
terminates. Is that not so?
If I reconfigure the app to call out to set_config(item, value, True)
after each 'BEGIN' statement then workMem seems to be correct (at
least more of the time -- the process takes some time to run and I
haven't done an exhaustive check as yet).
--
Jon