explain analyze query execution time

On 11/21/11, Rudyar <rudyar.cortes@gmail.com> wrote:

Hello,

I try to get the execution time of a query workload. I try using explain
analyze but this time is allways higher than
the execution time of a query across a client like pgadmin3

what is the reason about that difference?

Analyze has to do a lot of gettimeofday calls and other bookkeeping,
and that takes time. If the data queried is all in memory, this
analyze overhead can be far more than the query itself.

Jeff

Rudyar wrote:

I try to get the execution time of a query workload. I try using
explain analyze but this time is allways higher than the execution
time of a query across a client like pgadmin3

what is the reason about that difference?

It's the "observer effect" -- there is a cost to the timing,
counting, measuring, and reporting which is done by EXPLAIN ANALYZE,
which distorts what is being measured. It's just about impossible to
avoid entirely.

-Kevin

On 22/11/11 02:58, Kevin Grittner wrote:

Rudyar wrote:

I try to get the execution time of a query workload. I try using
explain analyze but this time is allways higher than the execution
time of a query across a client like pgadmin3

what is the reason about that difference?

It's the "observer effect" -- there is a cost to the timing,
counting, measuring, and reporting which is done by EXPLAIN ANALYZE,
which distorts what is being measured. It's just about impossible to
avoid entirely.

-Kevin

Thanks kevin,

what tool you recommend for measure the query "real" query execution time?

--
Rudyar Cort�s.
Estudiante de Ingenier�a Civil Inform�tica
Universidad T�cnica Federico Santa Mar�a.

Rudyar wrote:

what tool you recommend for measure the query "real" query
execution time?

The -hackers list is for discussion to coordinate development of the
PostgreSQL database product. For user questions like this, please
pick a more appropriate list based on the descriptions here:

http://www.postgresql.org/community/lists/

While any further follow-up should be on another list, I'll briefly
answer here. EXPLAIN ANALYZE is great for seeing how a query is
being executed, but for accurate timing of how long the query runs
without generating all that extra information, you can measure it on
the client side, or turn on logging of statements running long than
some particular time. In psql you can use "\timing on", in Java you
can run System.currentTimeInMillis() before and after running the
query, etc.

-Kevin

On 22/11/11 10:26, Kevin Grittner wrote:

Rudyar wrote:

what tool you recommend for measure the query "real" query
execution time?

The -hackers list is for discussion to coordinate development of the
PostgreSQL database product. For user questions like this, please
pick a more appropriate list based on the descriptions here:

http://www.postgresql.org/community/lists/

While any further follow-up should be on another list, I'll briefly
answer here. EXPLAIN ANALYZE is great for seeing how a query is
being executed, but for accurate timing of how long the query runs
without generating all that extra information, you can measure it on
the client side, or turn on logging of statements running long than
some particular time. In psql you can use "\timing on", in Java you
can run System.currentTimeInMillis() before and after running the
query, etc.

-Kevin

Thanks Kevin ;)