On Tuesday, May 13, 2014, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
A recent question from Tim Kane prompted me to measure the overhead
costs of EXPLAIN ANALYZE, which I'd not checked in awhile. Things
are far worse than I thought. On my current server (by no means
lavish hardware: Xeon E5-2609 @2.40GHz) a simple seqscan can run
at something like 110 nsec per row:
regression=# create table foo as select x as f1 from generate_series(1,1000000) x;
SELECT 1000000
regression=# vacuum foo;
VACUUM
regression=# explain analyze select * from foo;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.053..111.720 rows=1000000 loops=1)
Planning time: 0.222 ms
Execution time: 166.682 ms
(3 rows)
(and, btw, this is a debug build --- without assert and memory
context checks it'd be faster.)
The problem with this number is that a simple test program shows that
gettimeofday() requires about 40 nsec on this hardware. That means
that two-thirds of the above timing measurement is overhead.
I'm all for finding something better if we can, but in the mean time this is certainly not unexpected, and isn't it exactly what "explain (analyze,timing off)" was invented for?
Cheers,
Jeff
