Обсуждение: Quere keep using temporary files
Hi, we frequently run many query involving XML that use a smallish temporary file, despite having increased local work_mem in that transaction to 16GB. FreeBSD's top shows that the memory isn't actually being used - it remains free. Basically many such queries are run within a single transaction: LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp92452.1079", size 166518 STATEMENT: DELETE FROM "foo" WHERE ((col1, col2, col3) in (select col1, col2, col3 from foo_xml_v2('<here goes xml-formatted blob>'))) AND "foo"."col_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) It looks like increasing work_mem doesn't help. Surely 16GB is enough to cover all these small temp files?
On Fri, Oct 25, 2019 at 10:23 AM rihad <rihad@mail.ru> wrote: > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp92452.1079", size 166518 > STATEMENT: DELETE FROM "foo" WHERE ((col1, col2, col3) in (select col1, > col2, col3 from foo_xml_v2('<here goes xml-formatted blob>'))) AND > "foo"."col_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) Isn't work_mem used for hashing and sorting, gin and stuff like that? Luca
rihad <rihad@mail.ru> writes: > Hi, we frequently run many query involving XML that use a smallish > temporary file, despite having increased local work_mem in that > transaction to 16GB. FreeBSD's top shows that the memory isn't actually > being used - it remains free. Basically many such queries are run within > a single transaction: > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp92452.1079", size 166518 > STATEMENT: DELETE FROM "foo" WHERE ((col1, col2, col3) in (select col1, > col2, col3 from foo_xml_v2('<here goes xml-formatted blob>'))) AND > "foo"."col_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) > It looks like increasing work_mem doesn't help. Surely 16GB is enough to > cover all these small temp files? You'd need to provide a lot more detail about what that query is doing for anyone to be able to guess where the temp file usage is coming from. regards, tom lane
On 10/25/2019 05:49 PM, Tom Lane wrote: > rihad <rihad@mail.ru> writes: >> Hi, we frequently run many query involving XML that use a smallish >> temporary file, despite having increased local work_mem in that >> transaction to 16GB. FreeBSD's top shows that the memory isn't actually >> being used - it remains free. Basically many such queries are run within >> a single transaction: >> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp92452.1079", size 166518 >> STATEMENT: DELETE FROM "foo" WHERE ((col1, col2, col3) in (select col1, >> col2, col3 from foo_xml_v2('<here goes xml-formatted blob>'))) AND >> "foo"."col_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) >> It looks like increasing work_mem doesn't help. Surely 16GB is enough to >> cover all these small temp files? > You'd need to provide a lot more detail about what that query is doing > for anyone to be able to guess where the temp file usage is coming from. > > regards, tom lane > . > I just checked and saw that the function "foo_xml_v2" above returns table. Is this enough to trigger temporary file usage regardless of work_mem?
rihad <rihad@mail.ru> writes: > On 10/25/2019 05:49 PM, Tom Lane wrote: >> You'd need to provide a lot more detail about what that query is doing >> for anyone to be able to guess where the temp file usage is coming from. > I just checked and saw that the function "foo_xml_v2" above returns > table. Is this enough to trigger temporary file usage regardless of > work_mem? No --- that would result in creation of a tuplestore to hold the function result, but it shouldn't spill to disk until it exceeds work_mem ... or at least so I'd expect. Can you extract a self-contained case that creates a small temp file? regards, tom lane