[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Performance of the Materialize operator in a query plan
From: Viktor Rosenfeld <rosenfel () informatik ! hu-berlin ! de>
Date: 2008-04-27 19:02:19
Message-ID: 875CC37D-A710-4ECB-8033-113E474BABBD () informatik ! hu-berlin ! de
[Download RAW message or body]
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
>
[Attachment #3 (text/html)]
<html><body style="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="cite"><blockquote \
type="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="cite"><br></blockquote><blockquote type="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=> \
EXPLAIN ANALYZE SELECT count(*) FROM test;<br> \
&n \
bsp; &nbs \
p; QUERY \
PLAN<br>-------------------------------------------------------------------------------- \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; --------------------------------<br> \
Aggregate (cost=1692.99..1693.00 rows=1 width=0) (actual time=66.314..66.314 r \
&n \
bsp; &nbs \
p; \
&n \
bsp; ows=1 \
loops=1)<br> -> Seq Scan on test (cost=0.00..1442.99 \
rows=99999 width=0) (actual time=0. \
&n \
bsp; &nbs \
p; \
&n \
bsp; 013..34.888 \
rows=99999 loops=1)<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="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)<br>To \
make changes to your subscription:<br><a \
href="http://www.postgresql.org/mailpref/pgsql-performance">http://www.postgresql.org/ \
mailpref/pgsql-performance</a><br><br></blockquote></div><br></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic