Thread: Performance of SELECT directly and from application
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
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
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 > > >
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 ***************************************************************
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