Quere keep using temporary files

From: rihad <rihad(at)mail(dot)ru>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Quere keep using temporary files
Date: 2019-10-25 08:22:58
Message-ID: 2c38ebb8-977e-926b-4842-5cf31c4059b2@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Sandow 2019-10-25 10:45:40 Error building Postgres for Windows
Previous Message Thomas Kellerer 2019-10-25 06:13:03 Re: Search path