On Thu, Jun 18, 2015 at 12:38 PM, Duane Murphy <duane.murphy@xxxxxxxxx> wrote:
We are trying to improve performance by avoiding the temp file creation.LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8068.125071", size 58988604STATEMENT: SELECT iiid.installed_item__id, item_detail.id, item_detail.model_id, item_detail.typeFROM installed_item__item_detail AS iiidINNER JOIN item_detail ON iiid.item_detail__id = item_detail.idINNER JOIN item ON (item.installed_item__id = iiid.installed_item__id )Our hypothesis is that the temp file creation is caused by the high row count of theinstalled_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
