Thread: Query size?

Query size?

From
Paul Tomblin
Date:
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.

Re: Query size?

From
imad
Date:
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
>

Re: Query size?

From
Paul Tomblin
Date:
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

Re: Query size?

From
imad
Date:
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
>

Re: Query size?

From
Tom Lane
Date:
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