Обсуждение: What does \timing measure?

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?

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

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).

Thanks in advance,
Mathieu

On Thu, Apr 7, 2011 at 3:52 PM, Mathieu Dubois <mathieu.dubois@limsi.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

Hello,

On 04/08/2011 05:04 AM, Josh Kupershmidt wrote:

On Thu, Apr 7, 2011 at 3:52 PM, Mathieu Dubois <mathieu.dubois@limsi.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.

Thanks! Just for the record I point to the manual page explaining it:
http://www.postgresql.org/docs/8.4/interactive/sql-explain.html
"The ANALYZE option causes the statement to be actually executed, not only planned. The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually returned are added to the display. This is useful for seeing whether the planner's estimates are close to reality."
So EXPLAIN ANALYZE is really the solution to my problem (measuring time on the server).

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.

Yes I'm really interested in the "cold query time".
Is restarting PostgreSQL mandatory? What about just log out (from PostgreSQL), wait some time (let's say 1mn) and then relog?
Also a quick googling shows that to clear OS cache I can run (on the server):

sync;
sysctrl -w vm.drop_caches=3;

Is that right?

Thank you very much for your time (and my \timing :).
Mathieu

Josh

On Fri, Apr 8, 2011 at 2:31 AM, Mathieu Dubois <mathieu.dubois@limsi.fr> wrote:
> Yes I'm really interested in the "cold query time".
> Is restarting PostgreSQL mandatory? What about just log out (from
> PostgreSQL), wait some time (let's say 1mn) and then relog?

Logging out from Postgres isn't going to do anything to clear its
buffercache. AFAIK the only way to wipe it is to shutdown the server
and start it back up.

> Also a quick googling shows that to clear OS cache I can run (on the
> server):
>
> sync;
> sysctrl -w vm.drop_caches=3;
>
> Is that right?

I believe that's right, if you're on Linux with a recent kernel.

Josh

Hello,

Sorry for the delay.

I spent time writing a short script shell to do this (see attachment).
This script has to be run on the server. Be aware that you can very
easily destroy your DB with it.

The query can be either in a file (specified with -i FILENAME) or from
the CLI (between ' ').

Thank you very much.
Mathieu

On 04/08/2011 03:02 PM, Josh Kupershmidt wrote:
> On Fri, Apr 8, 2011 at 2:31 AM, Mathieu Dubois<mathieu.dubois@limsi.fr>  wrote:
>> Yes I'm really interested in the "cold query time".
>> Is restarting PostgreSQL mandatory? What about just log out (from
>> PostgreSQL), wait some time (let's say 1mn) and then relog?
> Logging out from Postgres isn't going to do anything to clear its
> buffercache. AFAIK the only way to wipe it is to shutdown the server
> and start it back up.
>
>> Also a quick googling shows that to clear OS cache I can run (on the
>> server):
>>
>> sync;
>> sysctrl -w vm.drop_caches=3;
>>
>> Is that right?
> I believe that's right, if you're on Linux with a recent kernel.
>
> Josh
>