Performance of SELECT directly and from application

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance of SELECT directly and from application

Jessica Holle
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


Reply | Threaded
Open this post in threaded view
|

Re: Performance of SELECT directly and from application

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
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Performance of SELECT directly and from application

dbatoCloud Solution
In reply to this post by Jessica Holle
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 <[hidden email]> 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
>
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Performance of SELECT directly and from application

Jessica Holle
In reply to this post by Laurenz Albe
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
***************************************************************



Reply | Threaded
Open this post in threaded view
|

Re: Performance of SELECT directly and from application

Thomas Kellerer-4
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