Thread: 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 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: > 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 > >