Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Thanks for correction. At this point I would be trying to modify plan_cache_mode for the session which uses the bond variable. alter it so that plan_cache_mode=force_custom_plan One hypothesis is that, a bad plan got cached for that SQL pattern. Obviously, when you run it *manually* you are always getting a *custom* plan as it's not a prepared statement. On Sat, 9 Nov 2024, 03:46 ravi k, wrote: > Sorry, it was typo. Bind variable is bigint only. > > Thanks > > On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, wrote: > >> Just spotted a potential problem. The indexed column is a bigint. Are >> you, in your prepared statement passing a string or a big int ? >> I notice your plan is doing an implicit type conversion when you run it >> manually. >> Sometimes the wrong type will make it not use the index. >> >> On Fri, 8 Nov 2024, 03:07 ravi k, wrote: >> >>> Hi , >>> >>> Thanks for the suggestions. >>> >>> Two more observations: >>> >>> 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are >>> accurate in postgres 16) if parameter sniffing happens the possibility of >>> going to sequence scan is more right. >>> >>> 2) no blockings or IO issue during the time. >>> >>> 3) even with limit clause if touch all partitions also it could have >>> been completed in milliseconds as this is just one record. >>> >>> 4) auto_explain in prod we cannot enable as this is expensive and with >>> high TPS we may face latency issues and lower environment this issue cannot >>> be reproduced,( this is happening out of Million one case) >>> >>> This looks puzzle to us, just in case anyone experianced pls share your >>> experience. >>> >>> Regards, >>> Ravi >>> >>> On Thu, 7 Nov, 2024, 3:41 am David Mullineux, wrote: >>> It might be worth eliminating the use of cached plans here. Is your app using prepared statements at all? Point is that if the optimizer sees the same prepared query , 5 times, the it locks the plan that it found at that time. This is a good trade off as it avoids costly planning-time for repetitive queries. But if you are manually querying, the a custom plan will be generated anew. A quick analyze of the table should reset the stats and invalidate any cached plans. This may not be your problem just worth eliminating it from the list of potential causes. On Wed, 6 Nov 2024, 17:14 Ramakrishna m, wrote: > Hi Team, > > One of the queries, which retrieves a single record from a table with > 16 hash partitions, is taking more than 10 seconds to execute. In > contrast, > when we run the same query manually, it completes within milliseconds. > This > issue is causing exhaustion of the application pools. Do we have any bugs > in postgrs16 hash partitions? Please find the attached log, table, and > execution plan. > > size of the each partitions : 300GB > Index Size : 12GB > > Postgres Version : 16.x > Shared Buffers : 75 GB > Effective_cache : 175 GB > Work _mem : 4MB > Max_connections : 3000 > > OS : Ubuntu 22.04 > Ram : 384 GB > CPU : 64 > > Please let us know if you need any further information or if there are > additional details required. > > > Regards, > Ram. >
Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Thanks for the advice! I am planing to set session level! but before that one more observations noticed i.e One more table has same issue, which is having similar like hash partitions. And I scheduled manual analyze for all parent hash tables(thus all stats will update together). After this change I didn't noticed the issue, not sure does this addressed issue or not, just monitoring if this not works will set custom plan in session level. I have seen in SQL server parameter sniffing regularly but in postgres I never experienced. I am still wondering does this sniffing or not as from stats I didn't notice any sequence scan. Best, On Sat, 9 Nov, 2024, 3:40 pm David Mullineux, wrote: > Thanks for correction. At this point I would be trying to modify > plan_cache_mode > for the session which uses the bond variable. alter it so that > plan_cache_mode=force_custom_plan > One hypothesis is that, a bad plan got cached for that SQL pattern. > Obviously, when you run it *manually* you are always getting a *custom* > plan as it's not a prepared statement. > > > > > On Sat, 9 Nov 2024, 03:46 ravi k, wrote: > >> Sorry, it was typo. Bind variable is bigint only. >> >> Thanks >> >> On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, wrote: >> >>> Just spotted a potential problem. The indexed column is a bigint. Are >>> you, in your prepared statement passing a string or a big int ? >>> I notice your plan is doing an implicit type conversion when you run it >>> manually. >>> Sometimes the wrong type will make it not use the index. >>> >>> On Fri, 8 Nov 2024, 03:07 ravi k, wrote: >>> Hi , Thanks for the suggestions. Two more observations: 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are accurate in postgres 16) if parameter sniffing happens the possibility of going to sequence scan is more right. 2) no blockings or IO issue during the time. 3) even with limit clause if touch all partitions also it could have been completed in milliseconds as this is just one record. 4) auto_explain in prod we cannot enable as this is expensive and with high TPS we may face latency issues and lower environment this issue cannot be reproduced,( this is happening out of Million one case) This looks puzzle to us, just in case anyone experianced pls share your experience. Regards, Ravi On Thu, 7 Nov, 2024, 3:41 am David Mullineux, wrote: > It might be worth eliminating the use of cached plans here. Is your > app using prepared statements at all? > Point is that if the optimizer sees the same prepared query , 5 times, > the it locks the plan that it found at that time. This is a good trade > off > as it avoids costly planning-time for repetitive queries. But if you are > manually querying, the a custom plan will be generated anew. > A quick analyze of the table should reset the stats and invalidate any > cached plans. > This may not be your problem just worth eliminating it from the list > of potential causes. > > On Wed, 6 Nov 2024, 17:14 Ramakrishna m, wrote: > >> Hi Team, >> >> One of the queries, which retrieves a single record from a table with >> 16 hash partitions, is taking more than 10 seconds to execute. In >> contrast, >> when we run the same query manually, it completes within milliseconds. >> This >> issue is causing exhaustion of the application pools. Do we have any bugs >> in postgrs16 hash partitions? Please find the attached log, table, and >> execution plan. >> >> size of the each partitions : 300GB >> Index Size : 12GB >> >> Postgres Version : 16.x >> Shared Buffers : 75 GB >> Effective_cache : 175 GB >> Work _mem : 4MB >> Max_connections : 3000 >> >> OS : Ubuntu 22.04 >> Ram : 384 GB >> CPU : 64 >> >> Please let us know if you need any further information or if there >> are additional details required. >> >> >> Regards, >> Ram. >> >
Re: Bash function from psql (v14)
Maybe try using full path...
Yahoo Mail: Search, Organize, Conquer
On Sat, Nov 9, 2024 at 12:41 PM, Murthy Nunna wrote:
Hi,
I am trying to code bash function and call it from psql. But it is failing. How
can I get this to work. Creating a separate script instead of a function works,
but I do not want to do that. I have too many variables to be passed back and
forth. Any ideas?
#!/bin/bash
psql -d postgres -p 5433 <<-PSQLBLOCK
\! run-bash-function -- This doesn’t work
PSQLBLOCK
# Run the function outside PSQLBLOCK. This works!
run-bash-function
exit $?
# Create bash function
run-bash-function ()
{
echo "in bash function"
}
# end of bash script
Run the above script:
./test-bash-function.sh
sh: line 1: run-bash-function: command not found
in bash function
Bash function from psql (v14)
Hi,
I am trying to code bash function and call it from psql. But it is failing. How
can I get this to work. Creating a separate script instead of a function works,
but I do not want to do that. I have too many variables to be passed back and
forth. Any ideas?
#!/bin/bash
psql -d postgres -p 5433 <<-PSQLBLOCK
\! run-bash-function-- This doesn't work
PSQLBLOCK
# Run the function outside PSQLBLOCK. This works!
run-bash-function
exit $?
# Create bash function
run-bash-function ()
{
echo "in bash function"
}
# end of bash script
Run the above script:
./test-bash-function.sh
sh: line 1: run-bash-function: command not found
in bash function
Re: Bash function from psql (v14)
On Sat, 9 Nov 2024 at 23:11, Murthy Nunna wrote: > I am trying to code bash function and call it from psql. But it is failing. > How can I get this to work. Creating a separate script instead of a function > works, but I do not want to do that. I have too many variables to be passed > back and forth. Any ideas? > Run the above script: > > ./test-bash-function.sh > > sh: line 1: run-bash-function: command not found > > in bash function > > /* postgres@ubuntu:/tmp$ echo 'whoami' >> myscript.sh postgres@ubuntu:/tmp$ chmod a+x myscript.sh postgres@ubuntu:/tmp$ psql <
