Re: BUG #15232: Query execution changes based on using 'explain analyze' or not

On Fri, Jun 8, 2018 at 1:24 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Fri, Jun 8, 2018 at 5:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Jud Gardner <jgardner(at)comprehend(dot)com> writes:
>>> When submitted through psql I don't see the issue.
>>> I also don't see it when submitting via jdbc (42.2.2) directly.
>>
>>> I'm seeing this via jdbc through dbVisualizer, haven't seen this kind of
>>> behavior before.
>>
>> Interesting. It could be that dbVisualizer is setting some parameter
>> or transaction mode that prevents parallelism. SERIALIZABLE mode
>> does so, for example, because the serializable-transaction logic
>> hasn't been taught about parallelism yet.
>
> It plans a parallel query, but it's sending an Execute message with
> max_rows = 1000. That causes PortalRun() to be called with run_once =
> false [...]

The quick fix for dbVisualizer is to type -1 into the "Max Rows" box
in the top right hand corner of the SQL query window.

The execute_once flag came in with commit 691b8d59281b, which added
this logic for 'E' messages:

!execute_is_fetch && max_rows == FETCH_ALL

In JDBC there are two separate concepts, statement.setFetchSize() and
statement.setMaxRows(). The first is for fetching in batches
(implying that you might need to execute more than once) and the
second is for imposing a total row limit (possibly executing only once
and discarding the rest, the case here AFAICT). It doesn't look like
there is any way to tell the difference from our end, because they
both finish up as max_rows in an 'E' message (in our JDBC driver
sendOneQuery() and fetch() both call sendExecute()). So... I don't
see how we could improve this situation without changing the wire
protocol so that the client could state and intention to execute only
once (independently of max_rows). I hadn't looked into that code
before today; maybe someone with more knowledge of the protocol has an
idea.

--
Thomas Munro
http://www.enterprisedb.com