Обсуждение: explain analyze query execution time
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?
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 ;)