--Apple-Mail-4--912494086
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit
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@[EMAIL PROTECTED]
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--Apple-Mail-4--912494086
Content-Type: text/html;
charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
<html><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: space; =
-webkit-line-break: after-white-space; ">Hi,<div><br></div><div>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.</div><div><br></div><div>Thanks,</div><div>Viktor</div><div><br></div>=
<div><div>Am 24.04.2008 um 19:05 schrieb PFC:<br><blockquote =
type=3D"cite"><blockquote type=3D"cite">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?<br></blockquote><blockquote =
type=3D"cite"><br></blockquote><blockquote type=3D"cite">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.<br></blockquote><br>Run EXPLAIN ANALYZE query<br>Type =
\timing<br>Run SELECT count(*) FROM (query) AS foo<br><br>\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...<br><br>And yes EXPLAIN ANALYZE has overhead, sometimes =
significant. Think Heisenberg... You will measure it easily with this =
dumb method ;)<br><br><br>Here a very dumb query :<br><br>SELECT =
count(*) FROM test;<br> count<br>-------<br> 99999<br>(1 =
ligne)<br><br>Temps : 26,924 ms<br><br><br>test=3D> EXPLAIN ANALYZE =
SELECT count(*) FROM test;<br> =
&n=
bsp; &nbs=
p; =
&n=
bsp;QUERY =
PLAN<br>------------------------------------------------------------------=
-------------- =
&n=
bsp; &nbs=
p; =
&n=
bsp; &nbs=
p; =
--------------------------------=
<br> Aggregate (cost=3D1692.99..1693.00 rows=3D1 width=3D0) =
(actual time=3D66.314..66.314 r =
&n=
bsp; &nbs=
p; =
&n=
bsp; &nbs=
p; =
ows=3D1 loops=3D1)<br> =
-> Seq Scan on test (cost=3D0.00..1442.99 =
rows=3D99999 width=3D0) (actual time=3D0. =
&n=
bsp; &nbs=
p; =
&n=
bsp; &nbs=
p; =
013..34.888 rows=3D99999 =
loops=3D1)<br> Total runtime: 66.356 ms<br>(3 lignes)<br><br>Temps : =
66,789 ms<br><br>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.<br><br><br>-- <br>Sent via =
pgsql-performance mailing list (<a =
href=3D"mailto:pgsql-performance@[EMAIL PROTECTED]
">pgsql-performance@[EMAIL PROTECTED]
>)<br>To make changes to your subscription:<br><a =
href=3D"http://www.postgresql.org/mailpref/pgsql-performance">http://www.p=
ostgresql.org/mailpref/pgsql-performance</a><br><br></blockquote></div><br=
></div></body></html>=
--Apple-Mail-4--912494086--
|
