Re: experience sharing: select query returns more records than necessary

Dave Cramer wrote:
>
>
>
> In contrast, if I issue the query on the DB server in the psql
> console, it
> returns records almost immediately. Finally I found that it is
> because the
> postgreSQL JDBC driver is pre-fetching a lot (all?) of the records.
> To fix
> the problem, one can call setFetchSize(50) on the statement.
>
> psql doesnt do anything any differently. It just doesn't have to create
> objects, and you are likely local to the network.

Well, not exactly. The difference is that psql begins outputting as soon
as the first result arrives, while the driver (in non-cursor mode)
gathers the entire resultset before returning anything to the application.

So while the overall query execution time is going to be similar
(excepting object creation, etc), the query *latency* is higher with JDBC.

Though, I would suggest that if you really only care about the first 50
results, then put a LIMIT 50 in your query! The query planner may come
up with a better plan if you do that, too ..

If you want to "stream" the whole resultset, then fetchsize is certainly
the way to do it.

-O