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