Re: Limit vs setMaxRows issue

Hi,

I'm sorry, but I don't really know what you mean with setMaxRows using a
cursor.

Note that the *same query* (including the order by) is fast when it
contains the LIMIT 100 sql appended to it. So this query *also* does an
order by. The only difference between the queries is that one uses the
setMaxRows() call to limit the number of rows in the result set, and the
other uses SQL.

Greetings,
Sebastiaan

Dave Cramer wrote:
> Sebastiaan,
>
> I believe the setMaxRows will use a cursor, because you have an order
> by on the cursor it will have to be fully materialized
>
> Try it without the order by
>
> Dave
> On 21-Jun-06, at 5:11 AM, Sebastiaan van Erk wrote:
>
>> Hi,
>>
>> When using the ps.setMaxRows() call on PreparedStatement, the jdbc
>> driver sets the row limit via the "raw" postgres protocol. However,
>> in the words of Tom Lane, "the row limit in the protocol only says
>> how many rows to deliver in the first batch. The presumption is that
>> you'll eventually grab the rest, and so the query is planned on that
>> basis."
>>
>> What this means that when we do the following query:
>>
>> select action_id from actions order by action_id
>>
>> with a ps.setMaxRows(100), it takes about 1.8 seconds for the query
>> to complete. However, if we do the following query:
>>
>> select action_id from actions order by action_id limit 100
>>
>> without any ps.setMaxRows() the query only takes 0.156 seconds. This
>> is more than a factor of 10 faster.
>>
>> I'm not 100% sure of what JDBC says about setMaxRows (it's kind of
>> ambiguous in the java doc, as usual), but as far as I can tell, if
>> you call setMaxRows on the prepared statement there is no way in to
>> ever retrieve more than that number of rows. If this is indeed the
>> case, it seems to me that currently there is a mismatch between the
>> JDBC api and the postgresql api, and JDBC should somehow tell
>> postgres that this is a hard limit and it should not plan for a
>> second batch.
>>
>> Therefore, my question is: is this a bug? It is not feasable for me
>> to add LIMIT clauses to all the SQL queries in my code, so if this IS
>> a bug, I hope it can be fixed. If it is NOT a bug, is there an
>> alternative workaround that does not involve changing all of my sql
>> statements?
>>
>> Thanks in advance,
>> Sebastiaan
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster