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

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

From: "Dave Cramer" <pg(at)fastcrypt(dot)com>
To: "Kent Tong" <kent(at)cpttm(dot)org(dot)mo>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: experience sharing: select query returns more records than necessary
Date: 2009-01-20 15:56:50
Message-ID: 491f66a50901200756w3463c85ak2e99fb8ba08f2ad@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Tue, Jan 20, 2009 at 9:43 AM, Kent Tong <kent(at)cpttm(dot)org(dot)mo> wrote:

>
>
>
> Oliver Jowett wrote:
> >
> > Dave Cramer wrote:
> > 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.
> >
>
> Yeah, I was actually settling on "LIMIT 50" before coming across
> setFetchSize().
> However, the support for LIMIT varies from one DBMS to another. To keep
> the code as portable as possible, I decided to go with setFetchSize().
>
> Kent,
>

Check the difference in the plans. Using limit can invoke an index that
might not otherwise be used. It can make orders of magnitude differences in
these kinds of queries.

Dave

In response to

pgsql-jdbc by date

Next:From: Hartmann, Christian (externer Mitarbeiter)Date: 2009-01-20 16:47:04
Subject: java.sql.Statement generates java.lang.OutOfMemoryError in big tabe
Previous:From: Kent TongDate: 2009-01-20 14:43:32
Subject: Re: experience sharing: select query returns more records than necessary

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