Re: shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?

On Wed, Nov 16, 2005 at 01:23:08PM +0100, Thies C Arntzen wrote:
> hi,
>
> i have some system where i show pages results on a web-page - the query
> that returns the paged result looks like this:
>
> (table has a few hundred thousand rows, result-set is ~30000)
>
> a) 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
> this query returns data in 0.064secs.
>
> if i now want to display the pure number of documents that this query
> would generate without the limit clase i would do:
>
> b) select count(asset.asset_id) from asset.asset WHERE found_time >
> 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL
> this query takes > 6 seconds!

Umm, the first query doesn't calculate all the output nor does it even
have an estimate of it. Why do you think it does?

> i understand that postgres has to read every row from the heap to make
> sure that they are all still valid and count. but from my understanding
> query (a) would have something like an uncorrected count (somewhere
> internally) for the whole query as it has to performed an "order by" on
> the result-set before returning the first row.

Not if you have an index on "found_time". In that case it can return
the top 50 without even looking at most of the table. That's what
indexes are for. The only estimate it has is the one in EXPLAIN, and it
can find that without running the query at all.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.