On Monday, December 10, 2018, 3:45:33 PM EST, Tom Lane <t...@sss.pgh.pa.us> wrote: Thomas Carroll <tomfecarr...@yahoo.com> writes: > Postgres version: 10.5. work_mem setting: 4MB, shared_buffers setting: 800 > MB, connections typically around 30-40.
I imagine you checked this already, but ... what is temp_buffers set to? That constrains the maximum memory used for temporary-table buffers in each process, and an unreasonable setting for it could lead to the described behavior. Another thing to keep in mind with long-lived "temporary" tables is that autovacuum can't do anything with them; so it's incumbent on your application to periodically VACUUM and/or ANALYZE them as needed. Otherwise such tables will bloat, which could contribute to excessive use of temporary-table buffers. regards, tom lane On Monday, December 10, 2018, 3:45:33 PM EST, Tom Lane <t...@sss.pgh.pa.us> wrote: > I imagine you checked this already, but ... what is temp_buffers set to? Thanks for your reply! temp_buffers is the default 8MB, and I should have included that in my first email. > Another thing to keep in mind with long-lived "temporary" tables... WRT temp tables and autovacuum: I realize I need to add an important detail here: The table is created by the function using: CREATE TEMPORARY TABLE table_name ON COMMIT DROP AS SELECT... So my perhaps-wrong expectation is that all remnants of the old temporary table are discarded from the previous invocation, so there is no need to do any vacuuming. Thanks again,Tom
