pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Query Help
11+ messages / 7 participants
[nested] [flat]
* Slow Query Help
@ 2013-02-05 03:45 Will Platnick <wplatnick@gmail.com>
2013-02-05 13:58 ` Re: Slow Query Help Heikki Linnakangas <hlinnakangas@vmware.com>
2013-02-06 08:51 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Will Platnick @ 2013-02-05 03:45 UTC (permalink / raw)
To: pgsql-performance
We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has raised significantly, especially during our peak time where lots of users are logging in. According to New Relic, this query is now taking up the most amount of time during peak activity and my pg_stat_activity and slow log sampling agrees. We have 3 DB servers referenced here, production running 9.2.2, semi-idle (idle except for replication when I ran the test) running 9.2.2, and 9.1.3 completely idle with an old dump restored.
HW: All servers have the same amount of RAM and CPU and all our indexes fit in RAM. HD's differ, but they're all SSD and since the explain analyze's are all indexes, I'm going to assume it doesn't mean that much in the context.
Configuration across all three servers is the same.
Table was auto vacuumed today.
Idle DB running 9.1.3
http://explain.depesz.com/s/7DZ
Semi-Idle DB running 9.2.2 (transactions ran previously and replication happening, but no active traffic when query was ran)
http://explain.depesz.com/s/fEE
Production DB running 9.2.2 (Incredibly Active)
http://explain.depesz.com/s/qVW
Table "public.users"
Column | Type | Modifiers
-------------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
created_dt | timestamp with time zone | default now()
last_login_dt | timestamp with time zone | default now()
email | character varying | not null
verified | boolean | default false
first_name | character varying |
last_name | character varying |
username | character varying(50) | not null
location | character varying |
im_type | character varying |
im_username | character varying |
website | character varying |
phone_mobile | character varying(30) |
postal_code | character varying(10) |
language_tag | character varying(7) | default 'en'::character varying
settings | text | default ''::text
country | character(2) | default ''::bpchar
timezone | character varying(50) | not null default 'UTC'::character varying
verify_hash | character varying |
bio | character varying(160) |
twitter | character varying |
facebook | character varying |
search_updated_dt | timestamp with time zone | not null default now()
source | character varying |
auto_verified | boolean |
password | character(60) |
google | character varying |
gender | smallint | not null default 0
birthdate | date |
weibo | character varying |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"u_auth_email_index" UNIQUE, btree (lower(email::text))
"u_auth_uname_index" UNIQUE, btree (lower(username::text))
"u_auth_verify_hash_idx" UNIQUE, btree (verify_hash)
"users_search_updated_dt_idx" btree (search_updated_dt DESC)
--
Will Platnick
Sent with Sparrow (http://www.sparrowmailapp.com/?sig)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
@ 2013-02-05 13:58 ` Heikki Linnakangas <hlinnakangas@vmware.com>
1 sibling, 0 replies; 11+ messages in thread
From: Heikki Linnakangas @ 2013-02-05 13:58 UTC (permalink / raw)
To: Will Platnick <wplatnick@gmail.com>; +Cc: pgsql-performance
On 05.02.2013 05:45, Will Platnick wrote:
> We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has raised significantly, especially during our peak time where lots of users are logging in. According to New Relic, this query is now taking up the most amount of time during peak activity and my pg_stat_activity and slow log sampling agrees. We have 3 DB servers referenced here, production running 9.2.2, semi-idle (idle except for replication when I ran the test) running 9.2.2, and 9.1.3 completely idle with an old dump restored.
The only thing that stands out is that it always checks both indexes for
matches. Since you only want a single row as a result, it seems like it
would be better to first check one index, and only check the other one
if there's no match. Rewriting the query with UNION should do that:
SELECT id, username, password, email, verified, timezone FROM users
WHERE lower(username) = 'randomuser'
UNION ALL
SELECT id, username, password, email, verified, timezone FROM users
WHERE lower(email) = 'randomuser'
LIMIT 1;
Also, if you can assume that email addresses always contain the
@-character, you could take advantage of that and only do the
lower(email) = 'randomuser' search if there is one.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
@ 2013-02-06 08:51 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-06 13:17 ` Re: Slow Query Help Will Platnick <wplatnick@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Pavan Deolasee @ 2013-02-06 08:51 UTC (permalink / raw)
To: Will Platnick <wplatnick@gmail.com>; +Cc: pgsql-performance
On Tue, Feb 5, 2013 at 9:15 AM, Will Platnick <wplatnick@gmail.com> wrote:
> We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries
> has raised significantly, especially during our peak time where lots of
> users are logging in. According to New Relic, this query is now taking up
> the most amount of time during peak activity and my pg_stat_activity and
> slow log sampling agrees. We have 3 DB servers referenced here, production
> running 9.2.2, semi-idle (idle except for replication when I ran the test)
> running 9.2.2, and 9.1.3 completely idle with an old dump restored.
>
The only thing that stands out is: on your production server I see
"Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows
actual time as 0.179 ms. Not sure where that additional time is being
spent though. It could be ExecutorStart/End, but have no idea why they
should take so long.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 08:51 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
@ 2013-02-06 13:17 ` Will Platnick <wplatnick@gmail.com>
2013-02-06 16:22 ` Re: Slow Query Help Kevin Grittner <kgrittn@ymail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Will Platnick @ 2013-02-06 13:17 UTC (permalink / raw)
To: pgsql-performance
Good eye, I totally missed that!
Any ideas on how to troubleshoot this delay?
On Wednesday, February 6, 2013 at 3:51 AM, Pavan Deolasee wrote:
> On Tue, Feb 5, 2013 at 9:15 AM, Will Platnick <wplatnick@gmail.com (mailto:wplatnick@gmail.com)> wrote:
> > We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries
> > has raised significantly, especially during our peak time where lots of
> > users are logging in. According to New Relic, this query is now taking up
> > the most amount of time during peak activity and my pg_stat_activity and
> > slow log sampling agrees. We have 3 DB servers referenced here, production
> > running 9.2.2, semi-idle (idle except for replication when I ran the test)
> > running 9.2.2, and 9.1.3 completely idle with an old dump restored.
> >
>
>
> The only thing that stands out is: on your production server I see
> "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows
> actual time as 0.179 ms. Not sure where that additional time is being
> spent though. It could be ExecutorStart/End, but have no idea why they
> should take so long.
>
> Thanks,
> Pavan
> --
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 08:51 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-06 13:17 ` Re: Slow Query Help Will Platnick <wplatnick@gmail.com>
@ 2013-02-06 16:22 ` Kevin Grittner <kgrittn@ymail.com>
2013-02-06 16:24 ` Re: Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 16:27 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Kevin Grittner @ 2013-02-06 16:22 UTC (permalink / raw)
To: Will Platnick <wplatnick@gmail.com>; pgsql-performance
Will Platnick <wplatnick@gmail.com> wrote:
> Will Platnick <wplatnick@gmail.com> wrote:
>> The only thing that stands out is: on your production server I see
>> "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows
>> actual time as 0.179 ms. Not sure where that additional time is being
>> spent though. It could be ExecutorStart/End, but have no idea why they
>> should take so long.
> Any ideas on how to troubleshoot this delay?
Is the client which is running the query on the same machine as the
server? If not, what's the ping time between them?
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 08:51 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-06 13:17 ` Re: Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 16:22 ` Re: Slow Query Help Kevin Grittner <kgrittn@ymail.com>
@ 2013-02-06 16:24 ` Will Platnick <wplatnick@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Will Platnick @ 2013-02-06 16:24 UTC (permalink / raw)
To: pgsql-performance
Clients are technically our pgbouncer which is on the same machine. The explain analyze was local through psql direct to postgresql.
On Wednesday, February 6, 2013 at 11:22 AM, Kevin Grittner wrote:
> Will Platnick <wplatnick@gmail.com (mailto:wplatnick@gmail.com)> wrote:
> > Will Platnick <wplatnick@gmail.com (mailto:wplatnick@gmail.com)> wrote:
>
>
> > > The only thing that stands out is: on your production server I see
> > > "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows
> > > actual time as 0.179 ms. Not sure where that additional time is being
> > > spent though. It could be ExecutorStart/End, but have no idea why they
> > > should take so long.
> > >
> >
>
>
> > Any ideas on how to troubleshoot this delay?
>
> Is the client which is running the query on the same machine as the
> server? If not, what's the ping time between them?
>
> -Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow Query Help
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 08:51 ` Re: Slow Query Help Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-06 13:17 ` Re: Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-06 16:22 ` Re: Slow Query Help Kevin Grittner <kgrittn@ymail.com>
@ 2013-02-06 16:27 ` Pavan Deolasee <pavan.deolasee@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Pavan Deolasee @ 2013-02-06 16:27 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: Will Platnick <wplatnick@gmail.com>; pgsql-performance
On Wed, Feb 6, 2013 at 9:52 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Will Platnick <wplatnick@gmail.com> wrote:
>> Will Platnick <wplatnick@gmail.com> wrote:
>
>>> The only thing that stands out is: on your production server I see
>>> "Total runtime: 7.515 ms", but the top node in EXPLAIN ANAYZE shows
>>> actual time as 0.179 ms. Not sure where that additional time is being
>>> spent though. It could be ExecutorStart/End, but have no idea why they
>>> should take so long.
>
>> Any ideas on how to troubleshoot this delay?
>
> Is the client which is running the query on the same machine as the
> server? If not, what's the ping time between them?
>
I don't think the network latency can cause that. The "Total runtime"
is calculated on the server side itself - see ExplainOnePlan().
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Slow query help
@ 2016-01-07 04:17 Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
2016-01-07 18:55 ` Re: Slow query help Rafael Bernard Rodrigues Araujo <rafael.araujo@endel.com.br>
2016-01-07 19:15 ` Re: Slow query help Marc Mamin <M.Mamin@intershop.de>
0 siblings, 2 replies; 11+ messages in thread
From: Almir de Oliveira Duarte Junior @ 2016-01-07 04:17 UTC (permalink / raw)
To: 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
*
* 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow query help
2016-01-07 04:17 Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
@ 2016-01-07 18:55 ` Rafael Bernard Rodrigues Araujo <rafael.araujo@endel.com.br>
1 sibling, 0 replies; 11+ messages in thread
From: Rafael Bernard Rodrigues Araujo @ 2016-01-07 18:55 UTC (permalink / raw)
To: pgsql-performance
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@adj.com.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@adj.com.br>almirjr@adj.com.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@adj.com.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
>
Attachments:
organization_business_small_pt.png (image/png, 10.9K) download
view image
organization_logo_small.png (image/png, 5.2K) download
view image
* Re: Slow query help
2016-01-07 04:17 Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
@ 2016-01-07 19:15 ` Marc Mamin <M.Mamin@intershop.de>
2016-01-07 19:30 ` Re: Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
1 sibling, 1 reply; 11+ messages in thread
From: Marc Mamin @ 2016-01-07 19:15 UTC (permalink / raw)
To: Almir de Oliveira Duarte Junior <almirjr@adj.com.br>; pgsql-performance
>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/
Hello,
It seems that you don't pay much attention to column alignment.
e.g. http://adj.com.br/erp/data_schema/tables/ERP_PUBLIC_sys_person.html
This probably won't make any significant difference in your case,
but this is something to be aware of when dealing with large tables.
here is a good starting link for this topic:
http://stackoverflow.com/questions/12604744/does-the-order-of-columns-in-a-postgres-table-impact-per...
regards,
Marc Mamin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Slow query help
2016-01-07 04:17 Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
2016-01-07 19:15 ` Re: Slow query help Marc Mamin <M.Mamin@intershop.de>
@ 2016-01-07 19:30 ` Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
0 siblings, 0 replies; 11+ messages in thread
From: Almir de Oliveira Duarte Junior @ 2016-01-07 19:30 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; pgsql-performance
Hi Marc,
Thank you very much!
Almir
====
On 01/07/2016 05:15 PM, Marc Mamin wrote:
>
>
> >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/
>
> Hello,
>
> It seems that you don't pay much attention to column alignment.
> e.g. http://adj.com.br/erp/data_schema/tables/ERP_PUBLIC_sys_person.html
>
> This probably won't make any significant difference in your case,
> but this is something to be aware of when dealing with large tables.
> here is a good starting link for this topic:
> http://stackoverflow.com/questions/12604744/does-the-order-of-columns-in-a-postgres-table-impact-per...
>
> regards,
>
> Marc Mamin
--
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@adj.com.br
Rua São José, 90 - sala 613 - Centro
Rio de Janeiro - RJ - CEP: 20.010-901
------------------------------------------------------------------------
FPASuite <http://fpasuite.com.br;
FPASuite <http://fpasuite.com.br;
Copyright © 2013 FPASuite. Todos os direitos reservados
Attachments:
organization_logo_small.png (image/png, 5.2K) download
view image
organization_business_small_pt.png (image/png, 10.9K) download
view image
end of thread, other threads:[~2016-01-07 19:30 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-05 03:45 Slow Query Help Will Platnick <wplatnick@gmail.com>
2013-02-05 13:58 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-02-06 08:51 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-06 13:17 ` Will Platnick <wplatnick@gmail.com>
2013-02-06 16:22 ` Kevin Grittner <kgrittn@ymail.com>
2013-02-06 16:24 ` Will Platnick <wplatnick@gmail.com>
2013-02-06 16:27 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2016-01-07 04:17 Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
2016-01-07 18:55 ` Re: Slow query help Rafael Bernard Rodrigues Araujo <rafael.araujo@endel.com.br>
2016-01-07 19:15 ` Re: Slow query help Marc Mamin <M.Mamin@intershop.de>
2016-01-07 19:30 ` Re: Slow query help Almir de Oliveira Duarte Junior <almirjr@adj.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox