Re: What does \timing measure?

On Thu, Apr 7, 2011 at 3:52 PM, Mathieu Dubois <mathieu(dot)dubois(at)limsi(dot)fr> wrote:
> Hello,
>
> I want to compare the time needed to execute 2 queries (which give the same
> results).
> Of course this depends on the server load, the network, etc.
>
> The basic idea is to use "\timing on" under psql but what is the returned
> time? The time to execute the query on the server or the time to fetch the
> results?

The \timing command will show you the total time it took to execute
your query, and fetch the results back to the client.

> In the first case the measurement would be more useful (no effect of the
> network and the client side).

Try EXPLAIN ANALYZE instead, which will show you (among other things)
the total time to execute your query on the server, not including the
overhead of fetching results.

> I have also a methodological question.
> The idea is to measure several times the same query (let's say 50 times) to
> get basic statistics.
> If I repeat the same query 50 times will the server use some caching
> mechanism to accelerate the query? In my case, this is not wanted (because I
> want to measure the normal query time).

Both PostgreSQL and your operating system will cache recently-accessed
blocks of data, so you will often see subsequent executions of the
same query be quicker than the first. If you're really only interested
in the performance of your query without the help of caching, you can
restart PostgreSQL plus clear out your OS's filesystem cache between
each run.

Josh