BUG #2475: Row limit problem

The following bug has been logged online:

Bug reference: 2475
Logged by: Sebastiaan van Erk
Email address: sebster-postgresql(at)sebster(dot)com
PostgreSQL version: 8.1.4
Operating system: Windows XP
Description: Row limit problem
Details:

When using the "raw" protocol (such as JDBC uses) it seems that the row
limit is ignored.

The following code in the JDBC driver:

// Total size = 4 (size field) + 1 + N (source portal) + 4 (max
rows)
pgStream.SendChar('E'); // Execute
pgStream.SendInteger4(4 + 1 + encodedSize + 4); // message size
if (encodedPortalName != null)
pgStream.Send(encodedPortalName); // portal name
pgStream.SendChar(0); // portal name terminator
pgStream.SendInteger4(limit); // row limit

in v3/QueryExecuterImpl.java (latest non-dev version) DOES send the limit to
the database (and debugging showed it was the correct limit), however the
database seems to ignore it. We tried the following query namely (with the
above limit):

Query "select action_id from actions order by action_id" elapsed time
(seconds) - Total: 1.937, SQL query time: 1.562, Building output time: 0.375

However when we actually put a limit in the query itself we get the
following:

Query "select action_id from actions order by action_id limit 100" elapsed
time (seconds) - Total: 0.156, SQL query time: 0.047, Building output time:
0.109

This shows the ENORMOUS different time it takes for the two queries even
though they both have the limit set.

"Sebastiaan van Erk" <sebster-postgresql(at)sebster(dot)com> writes:
> This shows the ENORMOUS different time it takes for the two queries even
> though they both have the limit set.

So? They're not the same query. (In particular, 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.)

regards, tom lane