Re: Limit vs setMaxRows issue

Oliver Jowett <oliver(at)opencloud(dot)com> writes:

> Marc Herbert wrote:

>> If planning is done at time of creation of the PreparedStatement
>> object (reminder: the example given above has no parameters), then the
>> setMaxRows() call will come too late whatever is the protocol change.
>> I mean: no protocol change can go back in time and "optimize" by not
>> doing useless work already done.
>> Thanks in advance for pointing out my mistake(s) here.
>
> We do not special-case the no-parameters case, so it's handled just
> like all the other cases: the query is parsed and planned
> immediately before execution.

OK, I should not have put this "reminder" above. I thought I would
simplify the discussion but it did not.

According to:
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html as
well as to any other non-DB specific, similar documentation, the
server is able to plan the query at parse time BEFORE receiving any
actual parameter. Connection.preparedStatement()'s Javadoc calls this
"pre-compilation" (and it's optional).

> the query is parsed and planned immediately before execution.

Hum, interesting. Looks like "lazy prepared" statement, no
pre-compilation? If you delay parsing & planning then of course you
would not need to go back in time to add late
optimizations...

However, what about the following executions of the same and now
already prepared and planned statement? Now you would have to go back
in time to perform any .setMaxRows() optimization, right?

I assume here that it's the query planning phase that would benefit
from any .setMaxRows() optimization, correct me if I'm wrong.

> We also avoid an extra round-trip by doing it at
> that point.

Then you also avoid any latency benefit that _could_ arise thanks to
pre-compilation. At least for the first execution.

> If you're interested in the details of this, the driver source code is
> really your best reference..

Sure, but for such high-level architectural questions you can easily
understand that I prefer to read your enlightning explanations.