Re: LIMIT question

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> writes:
> <Peter(dot)M(dot)Rothermel(at)boeing(dot)com> wrote:
>> Is there a better way to accomplish this goal?

> Here is a nice discussion on the alternatives open to you:
> http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/

That doesn't really address the question of how to estimate the number
of rows in a *query* (as opposed to a table).

The usual advice is to do an EXPLAIN and extract the first line's
rowcount estimate.  Of course you have to realize that this is often
far from reality --- but in the context the OP gave, maybe a ballpark
estimate is good enough.

If you really need an exact count, and are willing to pay for it,
the standard way is

	begin;
	declare c cursor for <<query>> ;
	move forward all in c;	-- note the returned rowcount
	move backward all in c; -- this, at least, is cheap
	fetch 1000 from c;
	commit;

The only thing this saves over just doing the full query is that you
don't have to transmit all the data to the client.  Still, that can be
an important savings.

			regards, tom lane