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