Re: Techniques to Avoid Temp Files

Duane Murphy wrote:
> We are trying to improve performance by avoiding the temp file creation.
>
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8068.125071", size 58988604
> STATEMENT: SELECT iiid.installed_item__id, item_detail.id, item_detail.model_id, item_detail.type
> FROM installed_item__item_detail AS iiid
> INNER JOIN item_detail ON iiid.item_detail__id = item_detail.id
> INNER JOIN item ON (item.installed_item__id = iiid.installed_item__id )
> INNER JOIN model ON (item.id = model.item__id AND model.id = $1)

> What are the causes of temp file creation?

Operations like hash and sort that need more space than work_mem promises.

> What additional information can I gather in order have a better understanding of how to improve this
> query?

It woul be really useful to see the result of "EXPLAIN (ANALYZE, BUFFERS) SELECT ..."
for your query.

But essentially the answer to avoid temporary files is always "increase work_mem".

Yours,
Laurenz Albe