Re: Performance of the Materialize operator in a query plan

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(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>