From: PFC <lists@peufeu.com>
To: Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of the Materialize operator in a query plan
Date: Thu, 24 Apr 2008 19:05:14 +0200
Message-ID: <op.t94ke0tfcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <316F5B37-3D07-417B-B5C1-9DB973258B18@informatik.hu-berlin.de>
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>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, rosenfel@informatik.hu-berlin.de, tgl@sss.pgh.pa.us
Subject: Re: Performance of the Materialize operator in a query plan
In-Reply-To: <op.t94ke0tfcigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox