Обсуждение: 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