Query plan and execution time of a query
Hello,
I am trying to get the execution time of a query by using JDBC (Java).
To do that I make the difference between the time (in milliseconds)
after the query execution and the time before the query execution.
The first time I do that, I get the real execution time, but the query
plan seems to be cached. And the next time I do the same operation,
for a simple query I do not get the real time (in fact my 'simple'
query seems to execute in 0 millisecond).
So I need help: I would like to solve this problem but I do not know
how. I have tried some researches on Internet but I did not find. Is
there a possibility to disable the query plan cache temporarily? It is
also possible I am totally wrong and the problem is not where I see
it... Anyway I need help.
Thank you in advance,
Mickael
Mickael,
Not knowing your query, it sounds like your method is working
correctly. It is quite normal to have the initial query take
longer than subsequent queries. This is a cache effect and is
what databases, in general, strive for performance-wise. I
suspect that the second time you run the query that it does
not take 0ms, just less than 1ms.
Ken
Show quoted text
On Sat, Jul 21, 2007 at 03:26:19PM +0200, Mickael DELOISON wrote:
Hello,
I am trying to get the execution time of a query by using JDBC (Java).
To do that I make the difference between the time (in milliseconds)
after the query execution and the time before the query execution.The first time I do that, I get the real execution time, but the query
plan seems to be cached. And the next time I do the same operation,
for a simple query I do not get the real time (in fact my 'simple'
query seems to execute in 0 millisecond).So I need help: I would like to solve this problem but I do not know
how. I have tried some researches on Internet but I did not find. Is
there a possibility to disable the query plan cache temporarily? It is
also possible I am totally wrong and the problem is not where I see
it... Anyway I need help.Thank you in advance,
Mickael---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
If you want more precise timings then turn on log_duration_statement.
The logs will carry timings down to the microsecond.
Also note that this is not the correct list for such questions - this
list is about development of PostgreSQL, not use.
cheers
andrew
Kenneth Marshall wrote:
Show quoted text
Mickael,
Not knowing your query, it sounds like your method is working
correctly. It is quite normal to have the initial query take
longer than subsequent queries. This is a cache effect and is
what databases, in general, strive for performance-wise. I
suspect that the second time you run the query that it does
not take 0ms, just less than 1ms.Ken
On Sat, Jul 21, 2007 at 03:26:19PM +0200, Mickael DELOISON wrote:
Hello,
I am trying to get the execution time of a query by using JDBC (Java).
To do that I make the difference between the time (in milliseconds)
after the query execution and the time before the query execution.The first time I do that, I get the real execution time, but the query
plan seems to be cached. And the next time I do the same operation,
for a simple query I do not get the real time (in fact my 'simple'
query seems to execute in 0 millisecond).So I need help: I would like to solve this problem but I do not know
how. I have tried some researches on Internet but I did not find. Is
there a possibility to disable the query plan cache temporarily? It is
also possible I am totally wrong and the problem is not where I see
it... Anyway I need help.Thank you in advance,
Mickael---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings