Re: Limit vs setMaxRows issue

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

> Marc Herbert wrote:
>> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>> 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...
>
> I don't know what you mean by "lazy prepared" statements. We give the
> statement to the server for parsing and planning at the point when we
> know both the query and the parameter types -- which, because of the
> JDBC API design, means just before execution.

OK thanks, now I think I got it: it seems like the JDBC API does not
assume you need parameter types to plan the query.

Connection.preparedStatement()

* If the driver supports precompilation,
* the method <code>prepareStatement</code> will send
* the statement to the database for precompilation.

No word about parameter types. If you do not need the datatypes, then
you can send/plan the query way earlier (not "lazy"), at
PreparedStatement object construction time.

I should have noticed this issue from here
<http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html>
Looks like we have an API mismatch here.

Is this parameter types issue specific to PostgreSQL, or is just the
JDBC API badly designed on this point?

Another approach could be for the server to infer the types from the
query string?

If not 100% reliably, then in an optimistic way?

> We retain the parse/plan results on the server side when it looks
> like the statement will be reused (using a simple "how many times
> has this statement already been reused?" metric), otherwise we
> reparse/replan on each execution.

Could this be compatible with any setMaxRows() optimization? I guess
yes, provided the maxRows parameter is considered in preparedstatement
matching, just like datatypes seem to be... Maybe this is already the
case for non-JDBC PREPARE + LIMIT?

Thanks again for all these answers.