Skip site navigation (1) Skip section navigation (2)

Re: Performance of the Materialize operator in a query plan

From: Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)de>
To: PFC <lists(at)peufeu(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance of the Materialize operator in a query plan
Date: 2008-04-27 19:02:19
Message-ID: 875CC37D-A710-4ECB-8033-113E474BABBD@informatik.hu-berlin.de (view raw or flat)
Thread:
Lists: pgsql-performance
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
>

In response to

pgsql-performance by date

Next:From: Vlad ArkhipovDate: 2008-04-28 01:51:38
Subject: Re: Optimizer's issue
Previous:From: Josh BerkusDate: 2008-04-27 17:54:00
Subject: Re: [pgsql-advocacy] Benchmarks WAS: Sun Talks about MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group