> Do you mean, that the overhead is an artefact of timing the query?
In=20=
=20
> that case, the query should run faster than its evaluation with EXPLAIN=
=20=20
> ANALYZE, correct?
>
> Is there a way to test this assumption regarding the speed of=20=20
> gettimeofday? I'm on a Macbook and have no idea about the
performance=20=
=20
> 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=20=20
result set is one single integer, client timings are not very
different=20=
=20
from server timings. If the client must retrieve lots of rows, this will=
=20=20
be different, hence the fake count(*) above to prevent this. You might=20=
=20
want to explain the count(*) also to be sure the same plan is used...
And yes EXPLAIN ANALYZE has overhead, sometimes significant. Think=20=20
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=3D> EXPLAIN ANALYZE SELECT count(*) FROM test;
QUERY PLAN
---------------------------------------------------------------------------=
-----=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20
--------------------------------
Aggregate (cost=3D1692.99..1693.00 rows=3D1 width=3D0) (actual=20=20
time=3D66.314..66.314=20=20
r=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20
ows=3D1 loops=3D1)
-> Seq Scan on test (cost=3D0.00..1442.99 rows=3D99999 width=3D0)
(ac=
tual=20=20
time=3D0.=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
013..34.888 rows=3D99999 loops=3D1)
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=20=
=20
2x as long as actually getting the row from the table. Which is=20=20
reassuring, in a way, since grabbing rows out of tables isn't such
an=20=20
unusual operation.
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
