Re: How many rows if limit wasn't present?

Richard Huxton wrote:
> Victor Spång Arthursson wrote:
>
>> Ciao!
>>
>> Is it possible to get the number of rows that would have been
>> returned if the LIMIT-clause weren't present in some way after the
>> query was run?
>>
>> Reason for asking is that I have a really big chunk of SQL, which
>> takes time to execute, and whoose result is paginated using a LIMIT-
>> clause, and to get the actual result (before pagination) I have to
>> run the query one more time… Big slow down…
>
>
> No. Standard procedure here is to select the results to a temporary
> table, or application-level cache etc.

Just to expand a bit on my own reply - PG will stop processing once it
hits the LIMIT. Sometimes it still has to gather all the rows first
(e.g. if you ask for the top 10 selling items this month, it needs to
calculate all the sales before limiting).

Also - you don't need to cache the full result. Sometimes it might make
sense to cache just some keys and associated scores and fetch
descriptive columns later if required.

HTH
--
Richard Huxton
Archonet Ltd