Re: How to prevent jdbc from sending any results back to the client ? - Mailing list pgsql-jdbc

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