Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aH22o-00049j-DC for pgsql-performance@arkaria.postgresql.org; Thu, 07 Jan 2016 04:19:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aH22n-0005NT-Vn for pgsql-performance@arkaria.postgresql.org; Thu, 07 Jan 2016 04:19:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aH212-0003Or-J7 for pgsql-performance@postgresql.org; Thu, 07 Jan 2016 04:17:37 +0000 Received: from centos01.fpasuite.com.br ([201.17.24.150]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aH20v-0005Jo-C5 for pgsql-performance@postgresql.org; Thu, 07 Jan 2016 04:17:32 +0000 Received: from centos01.insoliti.com.br (aguia.fpasuite.com.br [192.168.1.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: almirjr.adjcom) by centos01.fpasuite.com.br (Postfix) with ESMTPSA id 5855B8077C35 for ; Thu, 7 Jan 2016 04:17:22 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=adj.com.br; s=default; t=1452140242; bh=vPokwB5GhW0pK3VkaaN1LUNTehgY9yFA3jiW7WtDfVc=; h=To:From:Subject:Date; b=mLKz9PAJhcWFrMQ65F0vjbp4oG6jC+bUBfhNne0+cFw6ze7W7IxTgHWRMZZAHW34+ Z8catEQODRv9KCBIyuKDR3EywL8SXRzMIfqu7NGnZW+cINMMFut30/1Zwg7rwXbsnD BjJyn5Ywy1cGJ0t4v4enoB6mS/w/mm+HfX4YXn8c= To: pgsql-performance@postgresql.org From: Almir de Oliveira Duarte Junior Subject: Slow query help Message-ID: <568DE6D1.4020807@adj.com.br> Date: Thu, 7 Jan 2016 02:17:21 -0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------050708090706010708010104" X-Pg-Spam-Score: -2.0 (--) 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 This is a multi-part message in MIME format. --------------050708090706010708010104 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Hi, I ask your help to solve a slow query which is taking more than 14 seconds to be executed. Maybe I am asking too much both from you and specially from postgresql, as it is really huge, envolving 16 tables. Explain: http://explain.depesz.com/s/XII9 Schema: http://adj.com.br/erp/data_schema/ Version: PostgreSQL 9.2.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit OS: Centos 7.1 *Linux centos01.insoliti.com.br 3.10.0-327.3.1.el7.x86_64 #1 SMP Wed Dec 9 14:09:15 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux * * contains large objects: no * has a large proportion of NULLs in several columns: maybe * receives a large number of UPDATEs or DELETEs regularly: no * is growing rapidly: no * has many indexes on it: maybe (please see schema) * uses triggers that may be executing database functions, or is calling functions directly: in some cases * *History:*the system is still being developed. * *Hardware*: this is the development environment, a Dell T110-II server, with 8GB of ram and cpu as follows processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz stepping : 9 microcode : 0x1b cpu MHz : 1663.101 cache size : 3072 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt bogomips : 6185.92 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz stepping : 9 microcode : 0x1b cpu MHz : 1647.722 cache size : 3072 KB physical id : 0 siblings : 2 core id : 1 cpu cores : 2 apicid : 2 initial apicid : 2 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt bogomips : 6185.92 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: Configuration: name | current_setting | source ---------------------------------+-----------------------------------+---------------------- application_name | psql | client authentication_timeout | 1min | configuration file autovacuum | on | configuration file autovacuum_analyze_scale_factor | 0.05 | configuration file autovacuum_analyze_threshold | 10 | configuration file autovacuum_freeze_max_age | 200000000 | configuration file autovacuum_max_workers | 6 | configuration file autovacuum_naptime | 15s | configuration file autovacuum_vacuum_cost_delay | 10ms | configuration file autovacuum_vacuum_cost_limit | 1000 | configuration file autovacuum_vacuum_scale_factor | 0.1 | configuration file autovacuum_vacuum_threshold | 25 | configuration file bytea_output | hex | configuration file checkpoint_completion_target | 0.9 | configuration file checkpoint_segments | 32 | configuration file checkpoint_timeout | 10min | configuration file client_encoding | UTF8 | client client_min_messages | log | configuration file cpu_index_tuple_cost | 0.005 | configuration file cpu_operator_cost | 0.0025 | configuration file cpu_tuple_cost | 0.01 | configuration file DateStyle | SQL, DMY | configuration file default_text_search_config | pg_catalog.english | configuration file effective_cache_size | 5632MB | configuration file enable_bitmapscan | on | configuration file enable_hashagg | on | configuration file enable_hashjoin | on | configuration file enable_indexonlyscan | on | configuration file enable_indexscan | on | configuration file enable_material | on | configuration file enable_mergejoin | on | configuration file enable_nestloop | on | configuration file enable_seqscan | on | configuration file enable_sort | on | configuration file enable_tidscan | on | configuration file lc_messages | pt_BR.UTF-8 | configuration file lc_monetary | pt_BR.UTF-8 | configuration file lc_numeric | pt_BR.UTF-8 | configuration file lc_time | pt_BR.UTF-8 | configuration file listen_addresses | 127.0.0.1, 192.168.1.199 | configuration file log_autovacuum_min_duration | 0 | configuration file log_connections | on | configuration file log_destination | stderr | configuration file log_directory | pg_log | configuration file log_disconnections | on | configuration file log_duration | on | configuration file log_filename | postgresql-%a.log | configuration file log_line_prefix | %t - (%h - %u) --> | configuration file log_min_duration_statement | -1 | configuration file log_min_error_statement | info | configuration file log_min_messages | info | configuration file log_rotation_age | 1d | configuration file log_rotation_size | 0 | configuration file log_statement | all | configuration file log_timezone | Brazil/East | configuration file log_truncate_on_rotation | on | configuration file logging_collector | on | configuration file maintenance_work_mem | 1GB | configuration file max_connections | 100 | configuration file max_stack_depth | 2MB | environment variable password_encryption | on | configuration file port | 5434 | command line random_page_cost | 2 | configuration file seq_page_cost | 1 | configuration file shared_buffers | 2GB | configuration file shared_preload_libraries | plugin_debugger | configuration file ssl | on | configuration file ssl_ca_file | /home/postgres/ssl/ca-bundle.crt | configuration file ssl_cert_file | /home/postgres/ssl/localhost.crt | configuration file ssl_ciphers | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH | configuration file ssl_key_file | /home/postgres/ssl/localhost.key | configuration file ssl_renegotiation_limit | 512MB | configuration file synchronous_commit | off | configuration file syslog_facility | local0 | configuration file syslog_ident | postgres | configuration file TimeZone | Brazil/East | configuration file wal_buffers | 16MB | configuration file work_mem | 50MB | configuration file Thank you very much. Att., Almir de Oliveira Duarte Junior --------------050708090706010708010104 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Hi,

I ask your help to solve a slow query which is taking more than 14 seconds to be executed.
Maybe I am asking too much both from you and specially from postgresql, as it is really huge, envolving 16 tables.

Explain:
http://explain.depesz.com/s/XII9

Schema:
http://adj.com.br/erp/data_schema/

Version:
PostgreSQL 9.2.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

OS: Centos 7.1
Linux centos01.insoliti.com.br 3.10.0-327.3.1.el7.x86_64 #1 SMP Wed Dec 9 14:09:15 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

  • contains large objects: no
  • has a large proportion of NULLs in several columns: maybe
  • receives a large number of UPDATEs or DELETEs regularly: no
  • is growing rapidly: no
  • has many indexes on it: maybe (please see schema)
  • uses triggers that may be executing database functions, or is calling functions directly: in some cases

  • History: the system is still being developed.
  • Hardware: this is the development environment, a Dell T110-II server, with 8GB of ram and cpu as follows
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 58
model name      : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz
stepping        : 9
microcode       : 0x1b
cpu MHz         : 1663.101
cache size      : 3072 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt
bogomips        : 6185.92
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 58
model name      : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz
stepping        : 9
microcode       : 0x1b
cpu MHz         : 1647.722
cache size      : 3072 KB
physical id     : 0
siblings        : 2
core id         : 1
cpu cores       : 2
apicid          : 2
initial apicid  : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt
bogomips        : 6185.92
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

Configuration:
              name               |          current_setting          |        source       
---------------------------------+-----------------------------------+----------------------
 application_name                | psql                              | client
 authentication_timeout          | 1min                              | configuration file
 autovacuum                      | on                                | configuration file
 autovacuum_analyze_scale_factor | 0.05                              | configuration file
 autovacuum_analyze_threshold    | 10                                | configuration file
 autovacuum_freeze_max_age       | 200000000                         | configuration file
 autovacuum_max_workers          | 6                                 | configuration file
 autovacuum_naptime              | 15s                               | configuration file
 autovacuum_vacuum_cost_delay    | 10ms                              | configuration file
 autovacuum_vacuum_cost_limit    | 1000                              | configuration file
 autovacuum_vacuum_scale_factor  | 0.1                               | configuration file
 autovacuum_vacuum_threshold     | 25                                | configuration file
 bytea_output                    | hex                               | configuration file
 checkpoint_completion_target    | 0.9                               | configuration file
 checkpoint_segments             | 32                                | configuration file
 checkpoint_timeout              | 10min                             | configuration file
 client_encoding                 | UTF8                              | client
 client_min_messages             | log                               | configuration file
 cpu_index_tuple_cost            | 0.005                             | configuration file
 cpu_operator_cost               | 0.0025                            | configuration file
 cpu_tuple_cost                  | 0.01                              | configuration file
 DateStyle                       | SQL, DMY                          | configuration file
 default_text_search_config      | pg_catalog.english                | configuration file
 effective_cache_size            | 5632MB                            | configuration file
 enable_bitmapscan               | on                                | configuration file
 enable_hashagg                  | on                                | configuration file
 enable_hashjoin                 | on                                | configuration file
 enable_indexonlyscan            | on                                | configuration file
 enable_indexscan                | on                                | configuration file
 enable_material                 | on                                | configuration file
 enable_mergejoin                | on                                | configuration file
 enable_nestloop                 | on                                | configuration file
 enable_seqscan                  | on                                | configuration file
 enable_sort                     | on                                | configuration file
 enable_tidscan                  | on                                | configuration file
 lc_messages                     | pt_BR.UTF-8                       | configuration file
 lc_monetary                     | pt_BR.UTF-8                       | configuration file
 lc_numeric                      | pt_BR.UTF-8                       | configuration file
 lc_time                         | pt_BR.UTF-8                       | configuration file
 listen_addresses                | 127.0.0.1, 192.168.1.199          | configuration file
 log_autovacuum_min_duration     | 0                                 | configuration file
 log_connections                 | on                                | configuration file
 log_destination                 | stderr                            | configuration file
 log_directory                   | pg_log                            | configuration file
 log_disconnections              | on                                | configuration file
 log_duration                    | on                                | configuration file
 log_filename                    | postgresql-%a.log                 | configuration file
 log_line_prefix                 | %t - (%h - %u) -->                | configuration file
 log_min_duration_statement      | -1                                | configuration file
 log_min_error_statement         | info                              | configuration file
 log_min_messages                | info                              | configuration file
 log_rotation_age                | 1d                                | configuration file
 log_rotation_size               | 0                                 | configuration file
 log_statement                   | all                               | configuration file
 log_timezone                    | Brazil/East                       | configuration file
 log_truncate_on_rotation        | on                                | configuration file
 logging_collector               | on                                | configuration file
 maintenance_work_mem            | 1GB                               | configuration file
 max_connections                 | 100                               | configuration file
 max_stack_depth                 | 2MB                               | environment variable
 password_encryption             | on                                | configuration file
 port                            | 5434                              | command line
 random_page_cost                | 2                                 | configuration file
 seq_page_cost                   | 1                                 | configuration file
 shared_buffers                  | 2GB                               | configuration file
 shared_preload_libraries        | plugin_debugger                   | configuration file
 ssl                             | on                                | configuration file
 ssl_ca_file                     | /home/postgres/ssl/ca-bundle.crt  | configuration file
 ssl_cert_file                   | /home/postgres/ssl/localhost.crt  | configuration file
 ssl_ciphers                     | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH | configuration file
 ssl_key_file                    | /home/postgres/ssl/localhost.key  | configuration file
 ssl_renegotiation_limit         | 512MB                             | configuration file
 synchronous_commit              | off                               | configuration file
 syslog_facility                 | local0                            | configuration file
 syslog_ident                    | postgres                          | configuration file
 TimeZone                        | Brazil/East                       | configuration file
 wal_buffers                     | 16MB                              | configuration file
 work_mem                        | 50MB                              | configuration file


Thank you very much.

Att.,
Almir de Oliveira Duarte Junior

--------------050708090706010708010104--