Обсуждение: Posgresql Log: lots of parse statements
Hi All,
I need help to understand this please. I was looking to do performance tuning on slow queries so have stated logging queries taking more than 15 secs. In the postgresql log I can see a query which appears only as "parse" while others appear as execute.
2018-01-21 14:01:16 GMT LOG: duration: 62952.558 ms parse <unnamed>: select this_.busi_type as business_type from tablea where this_.busi_id = $1 and this_.busi_date = $2
2018-01-21 14:05:00 GMT LOG: duration: 62952.558 ms parse <unnamed>: select this_.busi_type as business_type from tablea where this_.busi_id = $1 and this_.busi_date = $2
2018-01-21 14:17:12 GMT LOG: duration: 62952.558 ms parse <unnamed>: select this_.busi_type as business_type from tablea where this_.busi_id = $1 and this_.busi_date = $2
2018-01-21 14:34:08 GMT LOG: duration: 62952.558 ms parse <unnamed>: select this_.busi_type as business_type from tablea where this_.busi_id = $1 and this_.busi_date = $2
I don't see this query to appear as execute, so how can find out how much time it's taking to execute? does this parse timing includes execute also?
Best Regards
Vikas
Hi All,I need help to understand this please. I was looking to do performance tuning on slow queries so have stated logging queries taking more than 15 secs. In the postgresql log I can see a query which appears only as "parse" while others appear as execute.[...]I don't see this query to appear as execute, so how can find out how much time it's taking to execute? does this parse timing includes execute also?
The most likely explanation is that executing the already parsed query takes less than 15 seconds and so doesn't appear due to your filter.
David J.
Thanks David,
But why are there so many parse statement occurances for one query? Does postgres parse the statement everytime before execution or parse the query only first time it is loaded in memory and reuse the same parsed plan until it ages out of memory?.
In the log I can see these parse statement occurances about 400 times in a day and everytime taking longer than 15 secs.
Regards
Vikas
On Mar 1, 2018 15:30, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
Hi All,I need help to understand this please. I was looking to do performance tuning on slow queries so have stated logging queries taking more than 15 secs. In the postgresql log I can see a query which appears only as "parse" while others appear as execute.[...]I don't see this query to appear as execute, so how can find out how much time it's taking to execute? does this parse timing includes execute also?The most likely explanation is that executing the already parsed query takes less than 15 seconds and so doesn't appear due to your filter.David J.
Vikas Sharma <shavikas@gmail.com> writes:
> But why are there so many parse statement occurances for one query?
A "parse" log entry is recorded when the client sends a Parse protocol
message. So the answer to that question needs to be sought in your
client application's logic.
> In the log I can see these parse statement occurances about 400 times in a
> day and everytime taking longer than 15 secs.
That seems like a mighty long time for parse analysis. Maybe you're
having difficulties with something taking exclusive locks, thereby
blocking other queries?
regards, tom lane
On Thursday, March 1, 2018, Vikas Sharma <shavikas@gmail.com> wrote:
Thanks David,But why are there so many parse statement occurances for one query? Does postgres parse the statement everytime before execution or parse the query only first time it is loaded in memory and reuse the same parsed plan until it ages out of memory?.In the log I can see these parse statement occurances about 400 times in a day and everytime taking longer than 15 secs.
Please don't top-post replies.
Normally "parse unnamed" happens when client interfaces execute prepared statements using parse-bind-execute protocol. If you want to reduce the number you will need to change your application. As for the time it takes its impossible to say without knowing more about the environment and query.
David J.