pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of SELECT directly and from application
5+ messages / 4 participants
[nested] [flat]
* Performance of SELECT directly and from application
@ 2020-06-24 05:00 Jessica Holle <jessica.holle@vit.de>
0 siblings, 2 replies; 5+ messages in thread
From: Jessica Holle @ 2020-06-24 05:00 UTC (permalink / raw)
To: pgsql-admin
Hi,
we use PostgreSQL 12.3.1 and have a little bit trouble with performance.
When the statement comes from the application (wildfly) duration is
about 24982.819 ms. When I do the statement directly with psql duration
is 967.773 ms.
Duration from Wildfly is known becaue we use in postgresql.conf
"log_min_duration_statement = 5000". There I see, that from application
are 3 variables used.
Is there any idea why this can happen or a hint to solve this issue?
Kind regards,
Jessica
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance of SELECT directly and from application
@ 2020-06-24 06:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Jessica Holle <jessica.holle@vit.de>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2020-06-24 06:14 UTC (permalink / raw)
To: Jessica Holle <jessica.holle@vit.de>; pgsql-admin
On Wed, 2020-06-24 at 07:00 +0200, Jessica Holle wrote:
> we use PostgreSQL 12.3.1 and have a little bit trouble with performance.
> When the statement comes from the application (wildfly) duration is
> about 24982.819 ms. When I do the statement directly with psql duration
> is 967.773 ms.
> Duration from Wildfly is known becaue we use in postgresql.conf
> "log_min_duration_statement = 5000". There I see, that from application
> are 3 variables used.
> Is there any idea why this can happen or a hint to solve this issue?
Try using "auto_explain" to get EXPLAIN (ANALYZE, BUFFERS) output for the slow
execution. The comparison of these two execution plans would be instructive.
Does the application set any parameters? Does it use a cursor?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance of SELECT directly and from application
@ 2020-06-24 06:38 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: Jessica Holle <jessica.holle@vit.de>
1 sibling, 0 replies; 5+ messages in thread
From: dbatoCloud Solution @ 2020-06-24 06:38 UTC (permalink / raw)
To: Jessica Holle <jessica.holle@vit.de>; +Cc: pgsql-admin
please share the output of explain also let me know if pgbench is
enabled configuration in postgresql.conf file
Thanks
Ashok
On Wed, Jun 24, 2020, 09:00 Jessica Holle <jessica.holle@vit.de> wrote:
> Hi,
>
> we use PostgreSQL 12.3.1 and have a little bit trouble with performance.
> When the statement comes from the application (wildfly) duration is
> about 24982.819 ms. When I do the statement directly with psql duration
> is 967.773 ms.
> Duration from Wildfly is known becaue we use in postgresql.conf
> "log_min_duration_statement = 5000". There I see, that from application
> are 3 variables used.
> Is there any idea why this can happen or a hint to solve this issue?
>
> Kind regards,
> Jessica
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance of SELECT directly and from application
@ 2020-06-25 11:13 Jessica Holle <jessica.holle@vit.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Jessica Holle @ 2020-06-25 11:13 UTC (permalink / raw)
To: pgsql-admin
Thanks for this advice.
We found out, that application use the following paramters:
set max_parallel_workers_per_gather to 4;
set min_parallel_table_scan_size = '1MB';
set parallel_setup_cost = 10;
set parallel_tuple_cost = 0.001;
Now they are using standardparamteers and they have restart Wildfly with
deleting tmp and -surprize- as fast as before.
I'm not allowed to post the plan until it is anonymized.
Until now thanks for helping.
Kind regards, Jessica
Am 24.06.20 um 08:14 schrieb Laurenz Albe:
> On Wed, 2020-06-24 at 07:00 +0200, Jessica Holle wrote:
>> we use PostgreSQL 12.3.1 and have a little bit trouble with performance.
>> When the statement comes from the application (wildfly) duration is
>> about 24982.819 ms. When I do the statement directly with psql duration
>> is 967.773 ms.
>> Duration from Wildfly is known becaue we use in postgresql.conf
>> "log_min_duration_statement = 5000". There I see, that from application
>> are 3 variables used.
>> Is there any idea why this can happen or a hint to solve this issue?
> Try using "auto_explain" to get EXPLAIN (ANALYZE, BUFFERS) output for the slow
> execution. The comparison of these two execution plans would be instructive.
>
> Does the application set any parameters? Does it use a cursor?
>
> Yours,
> Laurenz Albe
--
***************************************************************
Jessica Holle
rzv - Rechenzentrum Verden GmbH
Heinrich-Schröder-Weg 1, 27283 Verden (Germany)
Tel. +49 (0)4231-955 377; Fax. +49 (0)4231- 955 333
www.vit.de
Amtsgericht Walsrode HRB Nr. 120138
Geschäftsführer: Dr. Reinhard Reents
Vorsitzender des Aufsichtsrates: Anton Fortwengel
***************************************************************
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance of SELECT directly and from application
@ 2020-06-25 13:58 Thomas Kellerer <shammat@gmx.net>
parent: Jessica Holle <jessica.holle@vit.de>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2020-06-25 13:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Jessica Holle schrieb am 25.06.2020 um 13:13:
> I'm not allowed to post the plan until it is anonymized.
You can use https://explain.depesz.com/ to share your plan which has an option to automatically obfuscate the table names
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-06-25 13:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-24 05:00 Performance of SELECT directly and from application Jessica Holle <jessica.holle@vit.de>
2020-06-24 06:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-06-25 11:13 ` Jessica Holle <jessica.holle@vit.de>
2020-06-25 13:58 ` Thomas Kellerer <shammat@gmx.net>
2020-06-24 06:38 ` dbatoCloud Solution <dbatocloud17@gmail.com>
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