Re: How to prevent jdbc from sending any results back to the client ?

FWIW: the difference is noticeable, even on modern CPU types (this is an i7 4770):

bench=# EXPLAIN (ANALYZE,TIMING FALSE) SELECT aid,bid FROM pgbench_accounts;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000 width=8) (actual rows=1000000 loops=1)
 Planning time: 0.066 ms
 Total runtime: 80.172 ms
(3 rows)

bench=# EXPLAIN (ANALYZE,TIMING) SELECT aid,bid FROM
pgbench_accounts;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.010..98.167 rows=1000000 loops=1)
 Planning time: 0.063 ms
 Total runtime: 124.818 ms
(3 rows)

On 21/04/14 17:46, Mark Kirkwood wrote:

One possible problem with using EXPLAIN ANALYZE is that the cost of
timing each step can artificially inflate the query time...however you
can avoid this by using the variant:

EXPLAIN (ANALYZE,TIMING FALSE) statement

Which still does the query, but skips timing each step (which I think
probably what you want). It still says how long the entire statement took.

regards

Mark


On 20/04/14 12:22, Dave Cramer wrote:

Dimitris,

You would be better off running queries such as explain analyze which do
not return results, but do time the query. Every postgresql client
library will have to wait for the results. That is essentially the way
the protocol works

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 19 April 2014 15:02, Sehrope Sarkuni <sehrope@jackdb.com
<mailto:sehrope@jackdb.com>> wrote:

    The fetch size only comes into play if your are in a transaction.
    You have to disable auto commit and set the fetch size before
    executing your query. Otherwise the entire query result will be read
    and buffered in memory.

    An alternative is to run the command as an EXPLAIN ANALYZE[1]. The
    server will then execute the entire operation but instead of sending
    back the data it will send the query plan and runtime statistics.

    [1]: http://www.postgresql.org/docs/9.3/static/sql-explain.html

    Regards,
    Sehrope Sarkuni
    Founder & CEO | JackDB, Inc. | http://www.jackdb.com/

    On Apr 19, 2014, at 2:48 PM, Dimitris Karampinas
    <dkarampin@gmail.com <mailto:dkarampin@gmail.com>> wrote:

    Hi,

    I'm working on an academic project and I need to benchmark
PostgreSQL.
    I'm intersted only about the performance of the DBMS itself and
    I'm trying to keep things simple in my measurements.
    Preferably I'd like to ignore the query results at the client side
    but jdbc seems to return results even if I don't call next() on
    the Resultset (is that true ?).
    As a consequence, I can't measure acurately a per query execution
    time since the time I get depends also on the time spent to send
    the answer (or part of it) to the client.
    setFetchSize(1) doesn't seem to help much.
    Can I hack the driver and diminish the overhead explained above ?

    Cheers,
    Dimitris