Postgres Pro
Downloads
Home   >   mailing lists

Re: Techniques to Avoid Temp Files - Mailing list pgsql-performance

From Jeff Janes
Subject Re: Techniques to Avoid Temp Files
Date
Msg-id CAMkU=1xDEzzRzz8eeptbBtfaxhVcuobs=a+EMVLD2QgkZ0C6qA@mail.gmail.com
Whole thread Raw
In response to Techniques to Avoid Temp Files  (Duane Murphy <duane.murphy@gmail.com>)
List pgsql-performance
Tree view
On Thu, Jun 18, 2015 at 12:38 PM, Duane Murphy <duane.murphy@gmail.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

pgsql-performance by date:

Previous
From: Igor Neyman
Date:
Subject: Re: Slow query (planner insisting on using 'external merge' sort type)
Next
From: Jim Nasby
Date:
Subject: Re: Slow query (planner insisting on using 'external merge' sort type)