Re: Performance of the Materialize operator in a query plan

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