Re: Techniques to Avoid Temp Files

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)

Our hypothesis is that the temp file creation is caused by the high row count of the

installed_item__item_detail table.

installed_item__item_detail: 72916824 rows (27 GB)

item_detail:                 59212436 rows (40 GB)

The other two tables, item and model, are temporary tables created during this particular process. Unfortunately, I don't have those table sizes.

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