Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z5feX-0003gE-8z for pgsql-performance@arkaria.postgresql.org; Thu, 18 Jun 2015 19:39:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Z5feW-0003lQ-K9 for pgsql-performance@arkaria.postgresql.org; Thu, 18 Jun 2015 19:39:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Z5feV-0003kY-Ck for pgsql-performance@postgresql.org; Thu, 18 Jun 2015 19:39:07 +0000 Received: from mail-pd0-x229.google.com ([2607:f8b0:400e:c02::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1Z5feP-0002s2-QJ for pgsql-performance@postgresql.org; Thu, 18 Jun 2015 19:39:06 +0000 Received: by pdjm12 with SMTP id m12so73066799pdj.3 for ; Thu, 18 Jun 2015 12:38:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:subject:message-id:date:to:mime-version; bh=lQpsIHicCk8SNISbH8D1oIyxcOszceQ4TY4ThSRiyLw=; b=zEtH0TLXw+vakOiqKMTsoFttpo8bP0FI2qfuRXLDS1GMZ3hzKxXxV+scaN9IDaXF/b AWidmkLAeh1hxdh08k40Nsg7hasxgek/pEFoNr7km2WCrifH17fc96TrtklXqlPZ5AXN aoLGjYS0kGCxEP6Vq3zMA7xUBQKU4iuOac/ImAkJKN5U8F64YIThEwxwg48OHpsFm1qU Bgm6E2fwL61esIhtl5J8q5fxo/CoY295Bv3UIRikIndNeTHFQdZppp9bdXp5mX6RGJY+ k6Z8SPh+pKrJMNGjNBpQxHBXS8p8rftAZNcxb3cemcnDJXMCZ+05VF3rMx5NMsRcv7wm u1eg== X-Received: by 10.70.29.164 with SMTP id l4mr24074462pdh.32.1434656338892; Thu, 18 Jun 2015 12:38:58 -0700 (PDT) Received: from [192.168.1.98] (70-36-184-220.dsl.static.sonic.net. [70.36.184.220]) by mx.google.com with ESMTPSA id m2sm8899111pdp.4.2015.06.18.12.38.57 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 18 Jun 2015 12:38:58 -0700 (PDT) From: Duane Murphy Content-Type: multipart/alternative; boundary="Apple-Mail=_CADA7F26-7BEC-4AE5-B217-57196C49A4A6" Subject: Techniques to Avoid Temp Files Message-Id: <38E9456A-7841-4F13-B72B-FD3137591972@gmail.com> Date: Thu, 18 Jun 2015 12:38:55 -0700 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_CADA7F26-7BEC-4AE5-B217-57196C49A4A6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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 =3D item_detail.id INNER JOIN item ON (item.installed_item__id =3D iiid.installed_item__id = ) INNER JOIN model ON (item.id =3D model.item__id AND model.id =3D $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 are the causes of temp file creation? In general, temp files are = created when the sort merge data will not fit in work_mem. What can I do = to reduce the amount of data that is being merged? Is the simple fact = that the tables have millions of rows going to cause a merge sort? I noticed that this query selects from installed_item__item_detail = instead of from item_detail which seems like it would also work. Would = this change make a positive difference? installed_item__item_detail is a simple join table. The = installed_item__id side cannot be reduced. Would reducing the number of = item_detail rows using additional joins benefit? What additional information can I gather in order have a better = understanding of how to improve this query? (Unfortunately we do not have (easy) access to this particular database = in order to experiment.) ...Duane Background information: =3D> select version(); version = --------------------------------------------------------------------------= ------------------------------------ PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) = 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit $uname -a Linux host.name.com 2.6.32-358.6.2.el6.x86_64 #1 SMP Thu May 16 20:59:36 = UTC 2013 x86_64 x86_64 x86_64 GNU/Linux =3D> select name, current_setting(name), source from pg_settings where = source not in ('default', 'override'); name | current_setting | source = ------------------------------+--------------------+----------------------= application_name | psql | client checkpoint_completion_target | 0.9 | configuration file checkpoint_segments | 128 | configuration file client_encoding | UTF8 | client DateStyle | ISO, MDY | configuration file default_statistics_target | 100 | configuration file default_text_search_config | pg_catalog.english | configuration file effective_cache_size | 512MB | configuration file lc_messages | en_US.UTF-8 | configuration file lc_monetary | en_US.UTF-8 | configuration file lc_numeric | en_US.UTF-8 | configuration file lc_time | en_US.UTF-8 | configuration file log_autovacuum_min_duration | 1s | configuration file log_destination | stderr,syslog | configuration file log_line_prefix | [%m]: | configuration file log_min_duration_statement | 5min | configuration file log_min_error_statement | notice | configuration file log_rotation_age | 1d | configuration file log_rotation_size | 0 | configuration file log_temp_files | 1MB | configuration file log_timezone | US/Pacific | environment = variable log_truncate_on_rotation | on | configuration file logging_collector | on | configuration file maintenance_work_mem | 384MB | configuration file max_connections | 100 | configuration file max_stack_depth | 2MB | environment = variable port | 5432 | command line shared_buffers | 256MB | configuration file syslog_facility | local0 | configuration file TimeZone | US/Pacific | environment = variable wal_buffers | 1MB | configuration file work_mem | 128MB | configuration file (32 rows) --Apple-Mail=_CADA7F26-7BEC-4AE5-B217-57196C49A4A6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
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 =3D = item_detail.id
INNER JOIN = item ON (item.installed_item__id =3D iiid.installed_item__id = )
INNER JOIN model ON = (item.id =3D model.item__id AND model.id =3D = $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 are the causes of temp file = creation? In general, temp files are created when the sort merge data = will not fit in work_mem. What can I do to reduce the amount of data = that is being merged? Is the simple fact that the tables have millions = of rows going to cause a merge sort?

I noticed = that this query selects from installed_item__item_detail instead of from = item_detail which seems like it would also work. Would this change make = a positive = difference?

installed_item__item_detail is a = simple join table. The installed_item__id side cannot be reduced. =  Would reducing the number of item_detail rows using additional = joins benefit?

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

(Unfortunately we do not have (easy) = access to this particular database in order to = experiment.)

 ...Duane

Background information:

=3D> select version();
                  =                     =             =  version
---------------------------------------------------------------------= -----------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, = compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), = 64-bit

$uname = -a
Linux host.name.com = 2.6.32-358.6.2.el6.x86_64 #1 SMP Thu May 16 20:59:36 UTC 2013 x86_64 = x86_64 x86_64 GNU/Linux

=3D> select = name, current_setting(name), source from pg_settings where source not in = ('default', 'override');
             name     =         |  current_setting   |   =      source
------------------------------+--------------------+-----------------= -----
 application_name =             | psql       =         | client
 checkpoint_completion_target | 0.9   =              | configuration = file
 checkpoint_segments          | 128 =                | configuration = file
 client_encoding =              | UTF8     =           | client
 DateStyle           =          | ISO, MDY         =   | configuration file
 default_statistics_target    | 100     =            | configuration = file
 default_text_search_config   | pg_catalog.english | = configuration file
 effective_cache_size         | 512MB =              | configuration = file
 lc_messages =                  | = en_US.UTF-8        | configuration = file
 lc_monetary =                  | = en_US.UTF-8        | configuration = file
 lc_numeric   =                 | en_US.UTF-8 =        | configuration file
 lc_time           =            | en_US.UTF-8     =    | configuration file
 log_autovacuum_min_duration  | 1s       =           | configuration = file
 log_destination =              | stderr,syslog   =    | configuration file
 log_line_prefix             =  | [%m]:              | = configuration file
 log_min_duration_statement   | 5min       =         | configuration file
 log_min_error_statement      | = notice             | configuration = file
 log_rotation_age =             | 1d       =           | configuration = file
 log_rotation_size =            | 0         =          | configuration = file
 log_temp_files =               | 1MB     =            | configuration = file
 log_timezone =                 | US/Pacific =         | environment = variable
 log_truncate_on_rotation     | on     =             | configuration = file
 logging_collector =            | on       =           | configuration = file
 maintenance_work_mem         | 384MB =              | configuration = file
 max_connections =              | 100     =            | configuration = file
 max_stack_depth =              | 2MB     =            | environment = variable
 port   =                     =   | 5432               | command = line
 shared_buffers =               | 256MB     =          | configuration = file
 syslog_facility =              | local0     =         | configuration file
 TimeZone           =           | US/Pacific       =   | environment variable
 wal_buffers               =    | 1MB               =  | configuration file
 work_mem               =       | 128MB             =  | configuration file
(32 rows)

= --Apple-Mail=_CADA7F26-7BEC-4AE5-B217-57196C49A4A6--