Thread: LIMIT question

I need to use a LIMIT count in a query but I also need to know how many
rows the query itself would yield without the limit.
I can do this inside a transaction like this

BEGIN
SELECT COUNT(*) from table1 where blah;
select * from table1 where blah LIMIT 1000;
COMMIT

Now I can give some feedback like your search matches 200,000 but was
limited to 1000 items.
Is there a better way to accomplish this goal?

On Tue, Aug 19, 2008 at 1:58 PM, EXT-Rothermel, Peter M
<Peter.M.Rothermel@boeing.com> wrote:
> Now I can give some feedback like your search matches 200,000 but was
> limited to 1000 items.
> 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/


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Richard Broersma" <richard.broersma@gmail.com> writes:
> <Peter.M.Rothermel@boeing.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 rowcountmove backward all in c; --
this,at least, is cheapfetch 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