On 8/13/19 11:04 AM, rihad wrote:
> On 08/13/2019 08:44 PM, rihad wrote:
>> On 08/13/2019 08:22 PM, Luca Ferrari wrote:
>>> On Tue, Aug 13, 2019 at 5:59 PM rihad <rihad(at)mail(dot)ru> wrote:
>>>> [dbname] LOG: temporary file: path
>>>> "base/pgsql_tmp/pgsql_tmp93683.257381", size 594
>>>>
>>> The setting 'work_mem' is within context 'user', that means it will
>>> affect running sessione unless the session itself has already issued a
>>> SET work_mem to xxx.
>>> So this could be a reason why you don't seem to see any change.
>>>
>>> Also keep in mind that work_mem work on a connection basis, so you are
>>> going to possibly see 521MB x num_connections if all your clients are
>>> doig the same kind of sort concurrently, which probably causes
>>> PostgreSQL to go to disk due to memory unavailable.
>>>
>>> Hope this helps.
>>> Luca
>>> .
>>>
>> Thanks. The box has 15GB mem free (as in FreeBSD )))
>>
>> And it hasn't moved a notch after the increase.
>>
>> No code does SET work_mem=... AFAIK.
>>
>> My apologies to Mr. Peter but I still think that older processes,
>> some of them started a couple of weeks ago, use the older setting.
> Sorry, I just decreased work_mem back to 256MB, reloaded, and
> instantly started seeing 82mb temp file creation, not 165mb as was
> usual with work_mem=512MB.
>
> So it indeed was applied immediately.
> Really weird figures )
>
>
The files are written because work-mem was insufficient to complete the
task at hand, so some data was flushed to disk, more data read into
memory, processed and eventually all merged into final result. Larger
work-mem means more to flush, but less often.