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

13 maj 2005 kl. 14.45 skrev Richard Huxton:

> 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.

Figured so my self and changed the code from pg_fetch_all to
pg_fetch_assoc, and then I just fetched the 10 or so I needed…

Speed up with around 60% :D

Now the big question is how to understand the EXPLAIN numbers…

Ciao!

/.v