From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Sebastiaan van Erk <sebster(at)sebster(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Limit vs setMaxRows issue |
Date: | 2006-06-21 14:57:10 |
Message-ID: | B61920F8-F7BB-4638-A53D-F8637EBDB939@fastcrypt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sebastiaan van Erk | 2006-06-21 15:49:00 | Re: Limit vs setMaxRows issue |
Previous Message | Sebastiaan van Erk | 2006-06-21 09:11:51 | Limit vs setMaxRows issue |