Thread: Query size?
I'm working with another developer to produce an iPhone app - he's writing the app and I'm writing the server that provides the data to it. One of the queries he's doing might produce hundreds or thousands of results, and he doesn't want me sending that much data. So I'm wondering if there is a simple way to determine if a query is going to produce a result of more than N rows? Is the only way to do a "select count(*)" query first, or there something in the ResultSet that tells me how many results are queued up? -- Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ God is real, unless declared as an integer.
Why dont you adopt a pro-active aproach here and append a LIMIT to every query you send to the server? You can do an OFFSET LIMIT combination of all queries until you get results less than LIMIT. --Imad On Sun, Jun 22, 2008 at 9:38 PM, Paul Tomblin <ptomblin@gmail.com> wrote: > I'm working with another developer to produce an iPhone app - he's writing > the app and I'm writing the server that provides the data to it. One of the > queries he's doing might produce hundreds or thousands of results, and he > doesn't want me sending that much data. > > So I'm wondering if there is a simple way to determine if a query is going > to produce a result of more than N rows? Is the only way to do a "select > count(*)" query first, or there something in the ResultSet that tells me how > many results are queued up? > > -- > Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ > God is real, unless declared as an integer. > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
imad wrote: > Why dont you adopt a pro-active aproach here and append a LIMIT to > every query you send to the server? > You can do an OFFSET LIMIT combination of all queries until you get > results less than LIMIT. What I'm asking though is if there is some way to know before I start returning results, short of doing a "COUNT(*)" first. -- Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ We're the technical experts. We were hired so that management could ignore our recommendations and tell us how to do our jobs. -- Mike Andrews
Check this documentation out. It does what you want, but in an efficient way. http://doc.postgresintl.com/jdbc/ch04.html#jdbc-query-with-cursor --Imad On Sun, Jun 22, 2008 at 10:37 PM, Paul Tomblin <ptomblin@gmail.com> wrote: > imad wrote: >> >> Why dont you adopt a pro-active aproach here and append a LIMIT to >> every query you send to the server? >> You can do an OFFSET LIMIT combination of all queries until you get >> results less than LIMIT. > > What I'm asking though is if there is some way to know before I start > returning results, short of doing a "COUNT(*)" first. > > > -- > Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ > We're the technical experts. We were hired so that management could > ignore our recommendations and tell us how to do our jobs. > -- Mike Andrews >
Paul Tomblin <ptomblin@gmail.com> writes:
> What I'm asking though is if there is some way to know before I start
> returning results, short of doing a "COUNT(*)" first.
No. Postgres generates query results on the fly, so the server doesn't
know the number of rows that will be returned either, until the query
completes.
You can get an estimate by running EXPLAIN, but those estimates are
frequently far off the mark.
If you're really intent on having an accurate count before you fetch
the results, you can set up the query as a scrollable cursor, do MOVE
FORWARD ALL and note the move count, then MOVE BACKWARD ALL and start
fetching. This amounts to forcing the server to materialize the whole
result set before you start to fetch it, which is exceedingly expensive,
especially if the result set is very large.
Usually the best bet is to just limit how much you will fetch, using
LIMIT or a cursor as suggested by imad.
regards, tom lane