Date: Thu, 24 Apr 2008 19:05:14 +0200
To: "Viktor Rosenfeld" <rosenfel@informatik.hu-berlin.de>,
	"Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Performance of the Materialize operator in a query plan
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de>
	<11083.1208789089@sss.pgh.pa.us>
	<316F5B37-3D07-417B-B5C1-9DB973258B18@informatik.hu-berlin.de>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t94ke0tfcigqcu@apollo13.peufeu.com>
In-Reply-To: <316F5B37-3D07-417B-B5C1-9DB973258B18@informatik.hu-berlin.de>
User-Agent: Opera Mail/9.24 (Linux)

> 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 EXPLAI=
N  =

> 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 wil=
l  =

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=3D> EXPLAIN ANALYZE SELECT count(*) FROM test;
                                                    QUERY PLAN
------------------------------------------------------------------------=
--------                                                                =
                   =

--------------------------------
  Aggregate  (cost=3D1692.99..1693.00 rows=3D1 width=3D0) (actual  =

time=3D66.314..66.314  =

r                                                                       =
            =

ows=3D1 loops=3D1)
    ->  Seq Scan on test  (cost=3D0.00..1442.99 rows=3D99999 width=3D0) =
(actual  =

time=3D0.                                                               =
                    =

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=
  =

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.