Slow query help
| От | Almir de Oliveira Duarte Junior |
|---|---|
| Тема | Slow query help |
| Дата | |
| Msg-id | 568DE6D1.4020807@adj.com.br обсуждение исходный текст |
| Ответы |
Re: Slow query help
|
| Список | pgsql-performance |
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
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
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
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
В списке pgsql-performance по дате отправления: