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!
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.
i would be interested in getting this uncorrected count "after sort"
but "before first row" in query (a). so in a fresh DB with no
updates/deletes this would be the correct count, and i could avoid the
very expensive (b).
i'd like to hack that feature into my local portgres, i'm not asking
for inclusion in the official postgres, but could someone direct me if
my idea is feasable and where to look in the code (8.1)?
regards,
thies
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
