Skip site navigation (1) Skip section navigation (2)

Re: Limit vs setMaxRows issue

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 (view raw or flat)
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
>


In response to

Responses

pgsql-jdbc by date

Next:From: Sebastiaan van ErkDate: 2006-06-21 15:49:00
Subject: Re: Limit vs setMaxRows issue
Previous:From: Sebastiaan van ErkDate: 2006-06-21 09:11:51
Subject: Limit vs setMaxRows issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group