Re: That EXPLAIN ANALYZE patch still needs work

On Wed, 2006-06-07 at 17:28 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Wed, 2006-06-07 at 16:56 -0400, Tom Lane wrote:
> >> Certainly the removal of timing
> >> is not going to convert an intolerable EXPLAIN ANALYZE runtime into an
> >> acceptable one;
>
> > I disagree, as have others.
>
> The overhead seems to be on the order of a couple tens of percent usually.
> I don't see how that makes the difference between an EXPLAIN ANALYZE you
> can run and one you can't.

Well, thats not my experience and doesn't match others posted on
-hackers.

A simple test with pgbench shows the timing overhead of EXPLAIN ANALYZE
to be consistently above 500% (or more than +400%, depending upon how
you style those numbers). This is for in-shared-buffers data, following
cache priming. Test shown below is typical result from 5 tests:

> postgres=# select count(*) from accounts;
> count
> --------
> 100000
> (1 row)
>
> Time: 267.008 ms
> postgres=# explain analyze select count(*) from accounts;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=10945.00..10945.01 rows=1 width=0) (actual time=1497.830..1497.837 rows=1 loops=1)
> -> Seq Scan on accounts (cost=0.00..9998.20 rows=378720 width=0) (actual time=0.078..828.455 rows=100000 loops=1)
> Total runtime: 1497.954 ms
> (3 rows)
>
> Time: 1498.983 ms

Other timings were: 1493 1498 1707 1814 1827

EXPLAIN ANALYZE is designed to be run *when* you have unacceptable run
times and need to find out why. So making the execution time even more
unacceptable makes the utility infeasible at the time you need it most.
The additional run-time occurs on people's production systems, so they
generally aren't happy running long tasks.

BTW I think EA is great - so good in fact I want to run it more often.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com