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
On Thu, Mar 1, 2018 at 8:23 AM, Vikas Sharma <shavikas@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.
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:
Show quoted text
On Thu, Mar 1, 2018 at 8:23 AM, Vikas Sharma <shavikas@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.