Hi,
using this strategy to study the overhead of EXPLAIN ANALYZE was very
insightful. Apparently, measuring the performance of the query plan
introduced a overhead of more than 10 seconds in the query I was
looking at.
Thanks,
Viktor
Am 24.04.2008 um 19:05 schrieb PFC:
Do you mean, that the overhead is an artefact of timing the query?
In that case, the query should run faster than its evaluation with
EXPLAIN ANALYZE, correct?
Is there a way to test this assumption regarding the speed of
gettimeofday? I'm on a Macbook and have no idea about the
performance of its implementation.
Run EXPLAIN ANALYZE query
Type \timing
Run SELECT count(*) FROM (query) AS foo
\timing gives timings as seen by the client. If you're local, and
the result set is one single integer, client timings are not very
different from server timings. If the client must retrieve lots of
rows, this will be different, hence the fake count(*) above to
prevent this. You might want to explain the count(*) also to be sure
the same plan is used...
And yes EXPLAIN ANALYZE has overhead, sometimes significant. Think
Heisenberg... You will measure it easily with this dumb method ;)
Here a very dumb query :
SELECT count(*) FROM test;
count
-------
99999
(1 ligne)
Temps : 26,924 ms
test=> EXPLAIN ANALYZE SELECT count(*) FROM test;
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------
Aggregate (cost=1692.99..1693.00 rows=1 width=0) (actual
time=66.314..66.314
r
ows
=1 loops=1)
-> Seq Scan on test (cost=0.00..1442.99 rows=99999 width=0)
(actual
time
=
0
. 013
..34.888 rows=99999 loops=1)
Total runtime: 66.356 ms
(3 lignes)
Temps : 66,789 ms
Apparently measuring the time it takes to get a row from the table
takes 2x as long as actually getting the row from the table. Which
is reassuring, in a way, since grabbing rows out of tables isn't
such an unusual operation.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
