Обсуждение: Query timing stable?
Hi, I turned on "\timing" in the command line. It seems that the timing is not stable. For the same query, the query's executing time is longer for the first time I run it than for the second and third time run it. I think it might be "cached" automatically. Is there a way to turn the "automatic caching" off so that I can get the same "timing" when I run the same query? Or is there some other way to get stable query time whenever the query is run? Thanks, Cheng
On Wed, Mar 14, 2012 at 02:24:42PM -0400, Cheng wrote: > Hi, > > I turned on "\timing" in the command line. It seems that the timing > is not stable. For the same query, the query's executing time is > longer for the first time I run it than for the second and third > time run it. I think it might be "cached" automatically. > > Is there a way to turn the "automatic caching" off so that I can get > the same "timing" when I run the same query? Or is there some other > way to get stable query time whenever the query is run? > > Thanks, > Cheng > The OS and PostgreSQL will both cache data that has been read from the disk, in case it is needed again. The amount cached is dependent on the amount of RAM storage in your machine. This caching causes a repeated query to run in less time than the initial query. The caching is automatic, but can only cache what has already been read. You will need to run a query several times to get a stable timing and typically discard the time for at least the first run. Regards, Ken
On 03/14/2012 11:24 AM, Cheng wrote: > Hi, > > I turned on "\timing" in the command line. It seems that the timing is > not stable. For the same query, the query's executing time is longer > for the first time I run it than for the second and third time run it. > I think it might be "cached" automatically. > > Is there a way to turn the "automatic caching" off so that I can get > the same "timing" when I run the same query? Or is there some other > way to get stable query time whenever the query is run? > > Thanks, > Cheng > Depends on your OS. Things almost always run faster on subsequent access - usually due to OS caching of disk reads but data can be cached in PostgreSQL and your storage subsystems as well. Ensuring that *everything* is flushed almost requires restarting your system. Depending on your OS, you may be able to flush the disk cache and restart PostgreSQL and get "close enough". Depending on your query, you may be altering the underlying tables so getting exact repeatability is unlikely. Cheers, Steve