Re: Techniques to Avoid Temp Files

On Thu, Jun 18, 2015 at 12:38 PM, Duane Murphy <duane(dot)murphy(at)gmail(dot)com>
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)
>
> 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.
>

Those temporary tables aren't providing any output to the query, so their
only role must be to restrict the rows returned by the permanent tables.
If they restrict that by a lot, then it could do a nested loop over the
temp tables, doing indexed queries against the permanent tables assuming
you have the right indexes.

Temporary tables do not get analyzed automatically, so you should probably
run ANALYZE on them explicitly before this big query.

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

What indexes do the tables have? What is the output of EXPLAIN, or better
yet EXPLAIN (ANALYZE,BUFFERS), for the query?

Cheers,

Jeff