Re: Slow query help

Hi, Almir.

For instance, number 4:

===
4. 3,888.460 9,649.531 ↓ 70.9 7,382,985 1
→

Hash Left Join (cost=46,368.01..71,725.05 rows=104,205 width=2,356) (actual
time=1,013.778..9,649.531 *rows=7,382,985* loops=1)

Hash Cond: (e7.ser_recall_id = e11.ser_recall_item_ser_recall_id)

===

Take care,

--
Rafael Bernard Rodrigues Araújo
about.me/rafaelbernard

On Thu, Jan 7, 2016 at 12:40 PM, Almir de Oliveira Duarte Junior <
almirjr(at)adj(dot)com(dot)br> wrote:

> Hi Rafael,
>
> Thank you very much.
> It is strange, I don't have any table with more than 50,000 rows...
> Anyway, I will try that...
>
>
>
> On 01/07/2016 12:28 PM, Rafael Bernard Rodrigues Araujo wrote:
>
> Hi, Almir.
>
> I would at first try to decrease the number of rows from some joined
> tables at the join level instead of the where level, specially subqueries.
> I could see that you have some huge tables with more than 1,000,000.
>
> Take care,
>
> --
> Rafael Bernard Rodrigues Araújo
> about.me/rafaelbernard
>
> On Thu, Jan 7, 2016 at 2:17 AM, Almir de Oliveira Duarte Junior <
> <almirjr(at)adj(dot)com(dot)br>almirjr(at)adj(dot)com(dot)br> wrote:
>
>> 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 <http://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
>>
>>
>
>
> --
> Att.,
>
> Almir de Oliveira Duarte Junior, PMP
>
> ADJ Tecnologia da Informação
> Diretor
> Tel: +55 (21) 3079-4128
> Cel: +55 (21) 99362-7627
> Skype: almir.duarte.jr
> Email: almirjr(at)adj(dot)com(dot)br
> Rua São José, 90 - sala 613 - Centro
> Rio de Janeiro - RJ - CEP: 20.010-901
> ------------------------------
> [image: FPASuite] <http://fpasuite.com.br>
> [image: FPASuite] <http://fpasuite.com.br>
> Copyright © 2013 FPASuite. Todos os direitos reservados
>