pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: vincent elschot <vinny@xs4all.nl>
To: pgsql-performance@postgresql.org
Subject: Re: Explain analyze time overhead
Date: Thu, 05 Dec 2013 15:21:20 +0100
Message-ID: <52A08BE0.9080504@xs4all.nl> (raw)
In-Reply-To: <1386252540.32882.YahooMailNeo@web122206.mail.ne1.yahoo.com>
References: <1386252540.32882.YahooMailNeo@web122206.mail.ne1.yahoo.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>


On 05-12-13 15:09, salah jubeh wrote:
>
> Hello guys,
>
> When I excute a query,  the exection time is about 1 minute; however, 
> when I execute the query with explain analyze the excution time jumps 
> to 10 minutes.
> I have tried this for several queries, where  I need to optimize; and 
> using explain analyze leads alway to a huge time overhead in factor of 10.
>
> This is a little bit starnge for me; did any one experience somthing 
> like this? Can I trust the generated plans?
>
> Regards

Explain analyze does a lot more work than just explaining the query, it 
excecutes it and takes not of how long things actually took, which 
itself takes time. Apparently on some machines, it can take much longer 
than just executing the query would take.

 From the manual:
"In order to measure the run-time cost of each node in the execution 
plan, the current implementation ofEXPLAIN ANALYZEadds profiling 
overhead to query execution. As a result, runningEXPLAIN ANALYZEon a 
query can sometimes take significantly longer than executing the query 
normally. The amount of overhead depends on the nature of the query, as 
well as the platform being used. The worst case occurs for plan nodes 
that in themselves require very little time per execution, and on 
machines that have relatively slow operating system calls for obtaining 
the time of day."

view thread (8+ messages)  latest in thread

Message-ID: <52A08BE0.9080504@xs4all.nl>
Permalink:  ../52A08BE0.9080504@xs4all.nl/
Also on:    postgresql.org/message-id/52A08BE0.9080504@xs4all.nl

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: vinny@xs4all.nl
  Subject: Re: Explain analyze time overhead
  In-Reply-To: <52A08BE0.9080504@xs4all.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox