Re: Explain analyze time overhead

vincent elschot <vinny@xxxxxxxxx> · Thu, 05 Dec 2013 15:21:20 +0100



    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 of EXPLAIN ANALYZE adds
      profiling overhead to query execution. As a result, running EXPLAIN ANALYZE on 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."