Re: shouldn't postgres know the numer of rows in a (sorted)

Thies C. Arntzen wrote:
>
> Am 16.11.2005 um 14:07 schrieb Richard Huxton:
>
>> You don't say what applicaton language you are using, but most offer
>> a pg_num_rows() interface which tells you how many results are in the
>> recordset you have fetched.
>
>
> my query uses LIMIT and OFFSET - so pg_num_rows will return what i
> specify in LIMIT (or less). that's not the count i was asking for.

Ah - apologies, I didn't read your post closely enough.

I think the answer then is "no". In some cases PG can short-circuit the
query and stop once 50 are fetched, which means it doesn't always know.

With your query I'm not sure whether it can or not. Your timings however
suggest that this is what is happening, otherwise both queries would
take approximately the same amount of time.

One thing I have noticed though, is that the sort-order of your query
might not be well defined.

select asset.asset_id, asset.found_time from asset.asset WHERE
found_time > 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS
NULL order by found_time desc LIMIT 50 OFFSET 0

Unless found_time is unique then you might get different results on two
queries (since asset_id ordering is undefined).

--
Richard Huxton
Archonet Ltd