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