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

Thanks for your answers.

I need to understand the semantics of the jdbc driver.

My question is, what is the behaviour of executeQuery() ?
At the beginning I thought it was a blocking call, meaning control comes
back to the application right after the query has been fully executed at
the server side.
But my experiments show that once the server starts producing the first
tuples (assume a big join between two tables) executeQuery returns and I
can start iterating to the Resultset while the long query continues running
at the backend.

Can someone verify this ?
If this is the case, my next question is, what happens if I call .close()
on the ResultSet and the query is still running. Does it get aborted ?

FYI: I use a modified version of PostgreSQL. EXPLAIN ANALYSE and a couple
of other tricks I tried don't always work properly.

Thank you
-dk

On Mon, Apr 21, 2014 at 8:01 AM, Mark Kirkwood <
mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:

> 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(at)jackdb(dot)com
>>> <mailto:sehrope(at)jackdb(dot)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(at)gmail(dot)com <mailto:dkarampin(at)gmail(dot)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
>>>>
>>>
>>>
>>>
>>
>>
>>
>